Database Backups

Different types of SQL Server backups you can create:¶

Full backups
Differential backups
File backups
Filegroup backups
Partial backups
Copy-Only backups
Mirror backups
Transaction log backups

Full Backup Overview¶

The most common types of SQL Server backups are complete or full backups, also known as database backups. These backups create a complete backup of your database as well as part of the transaction log, so the database can be recovered. This allows for the simplest form of database restoration, since all of the contents are contained in one backup.

Explanation¶

A full backup can be completed either using T-SQL or by using SSMS. The following examples show you how to create a full backup.

Create full database backup to one disk file using T-SQL¶

Syntax 1:
BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks.BAK'
GO

Syntax 2: Without Compression Backup
BACKUP DATABASE [<DatabaseName>] TO  DISK = N'D:\SampleDB\<DatabaseName>.bak'
WITH NOFORMAT, NOINIT,  NAME = N'<DatabaseName>-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Syntax 3: With Compression Backup
BACKUP DATABASE [<DatabaseName>] TO  DISK = N'D:\SampleDB\<DatabaseName>.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'<DatabaseName>-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
GO

Create full database backup to one disk file using SSMS¶

Right click on the database name
Select Tasks > Backup
Select "Full" as the backup type
Select "Disk" as the destination
Click on "Add..." to add a backup file and type "C:\AdventureWorks.BAK" and click "OK"
Click "OK" again to create the backup

DBAImg

Differential Backups Overview¶

Another option to assist with your recovery is to create "Differential" backups. A "Differential" backup is a backup of any extent that has changed since the last "Full" backup was created.

Explanation The way differential backups work is that they will backup all extents that have changed since the last full backup. An extent is made up of eight 8KB pages, so an extent is 64KB of data. Each time any data has been changed a flag is turned on to let SQL Server know that if a "Differential" backup is created it should include the data from this extent. When a "Full" backup is taken these flags are turned off.

So if you do a full backup and then do a differential backup, the differential backup will contain only the extents that have changed. If you wait some time and do another differential backup, this new differential backup will contain all extents that have changed since the last full backup. Each time you create a new differential backup it will contain every extent changed since the last full backup. When you go to restore your database, to get to the most current time you only need to restore the full backup and the most recent differential backup. All of the other differential backups can be ignored.

If your database is in the Simple recovery model, you can still use full and differential backups. This does not allow you to do point in time recovery, but it will allow you to restore your data to a more current point in time then if you only had a full backup.

If your database is in the Full or Bulk-Logged recovery model you can also use differential backups to eliminate the number of transaction logs that will need to be restored. Since the differential will backup all extents since the last full backup, at restore time you can restore your full backup, your most recent differential backup and then any transaction log backups that were created after the most recent differential backup. This cuts down on the number of files that need to be restored.

Create Differential database backup to one disk file using T-SQL¶

Syntax 1:
BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks.DIF' WITH DIFFERENTIAL
GO

Syntax 2: With Compression Backup
BACKUP DATABASE [<DatabaseName>] TO  DISK = N'D:\SampleDB\<DatabaseName>.diff' 
WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = N'<DatabaseName>-Differential Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
GO

Create Differential database backup to one disk file using SSMS¶

Right click on the database name
Select Tasks > Backup
Select "Differential" as the backup type
Select "Disk" as the destination
Click on "Add..." to add a backup file and type "C:\AdventureWorks.diff" and click "OK"
Click "OK" again to create the backup

DBAImg

Transaction Log Backup Overview¶

If your database is set to the "Full" or "Bulk-logged" recovery model then you will be able to issue "Transaction Log" backups. By having transaction log backups along with full backups you have the ability to do a point in time restore, so if someone accidently deletes all data in a database you can recover the database to the point in time right before the delete occurred. The only caveat to this is if your database is set to the "Bulk-logged" recovery model and a bulk operation was issued, you will need to restore the entire transaction log.

Explanation¶

A transaction log backup allows you to backup the active part of the transaction log. So after you issue a "Full" or "Differential" backup the transaction log backup will have any transactions that were created after those other backups completed. After the transaction log backup is issued, the space within the transaction log can be reused for other processes. If a transaction log backup is not taken, the transaction log will continue to grow.

A transaction log backup can be completed either using T-SQL or by using SSMS. The following examples show you how to create a transaction log backup.

Create Transaction Log backup to one disk file using T-SQL¶

Syntax 1:
BACKUP LOG AdventureWorks TO DISK = 'C:\AdventureWorks.TRN'
GO

Syntax 2: With Compression Backup
BACKUP LOG [<DatabaseName>] TO  DISK = N'D:\SampleDB\<DatabaseName>.log' 
WITH NOFORMAT, NOINIT,  NAME = N'<DatabaseName>-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  
STATS = 10
GO

Create Transaction Log backup to one disk file using SSMS¶

Right click on the database name
Select Tasks > Backup
Select "Transaction Log" as the backup type
Select "Disk" as the destination
Click on "Add..." to add a backup file and type "C:\AdventureWorks.TRN" and click "OK"
Click "OK" again to create the backup

DBAImg

Create Backups using Only Commands¶

There are only two commands for backup, the primary is BACKUP DATABASE. This allows you to do a complete backup of your database as well as differential, file, etc. backups depending on the options that you use.

Explanation¶

The BACKUP DATABASE command gives you many options for creating backups. Following are different examples.

Create a full backup to disk¶

The command is BACKUP DATABASE databaseName. The "TO DISK" option specifies that the backup should be written to disk and the location and filename to create the backup is specified.

BACKUP DATABASE AdventureWorks 
TO DISK = 'C:\AdventureWorks.BAK'
GO

Create a differential backup¶

This command adds the "WITH DIFFERENTIAL" option.

BACKUP DATABASE AdventureWorks 
TO DISK = 'C:\AdventureWorks.BAK' 
WITH DIFFERENTIAL 
GO

Create a file level backup¶

This command uses the "WITH FILE" option to specify a file backup. You need to specify the logical filename within the database which can be obtained by using the command sp_helpdb 'databaseName', specifying the name of your database.

BACKUP DATABASE TestBackup FILE = 'TestBackup' 
TO DISK = 'C:\TestBackup_TestBackup.FIL'
GO

Create a filegroup backup¶

This command uses the "WITH FILEGROUP" option to specify a filegroup backup. You need to specify the filegroup name from the database which can be obtained by using the command sp_helpdb 'databaseName', specifying the name of your database.

BACKUP DATABASE TestBackup FILEGROUP = 'ReadOnly' 
TO DISK = 'C:\TestBackup_ReadOnly.FLG'
GO

Create a full backup to multiple disk files¶

This command uses the "DISK" option multiple times to write the backup to three equally sized smaller files instead of one large file.

BACKUP DATABASE AdventureWorks 
TO DISK = 'C:\AdventureWorks_1.BAK',
DISK = 'D:\AdventureWorks_2.BAK',
DISK = 'E:\AdventureWorks_3.BAK'
GO

Create a full backup with a password¶

This command creates a backup with a password that will need to be supplied when restoring the database.

BACKUP DATABASE AdventureWorks 
TO DISK = 'C:\AdventureWorks.BAK'
WITH PASSWORD = 'Q!W@E#R$'
GO

Create a full backup with progress stats¶

This command creates a full backup and also displays the progress of the backup. The default is to show progress after every 10%.

BACKUP DATABASE AdventureWorks 
TO DISK = 'C:\AdventureWorks.BAK'
WITH STATS
GO

Here is another option showing stats after every 1%.

BACKUP DATABASE AdventureWorks 
TO DISK = 'C:\AdventureWorks.BAK'
WITH STATS = 1
GO

Create a backup and give it a description¶

This command uses the description option to give the backup a name. This can later be used with some of the restore commands to see what is contained with the backup. The maximum size is 255 characters.

BACKUP DATABASE AdventureWorks 
TO DISK = 'C:\AdventureWorks.BAK'
WITH DESCRIPTION = 'Full backup for AdventureWorks'
GO

Create a mirrored backup¶

This option allows you to create multiple copies of the backups, preferably to different locations.

BACKUP DATABASE AdventureWorks 
TO DISK = 'C:\AdventureWorks.BAK'
MIRROR TO DISK =  'D:\AdventureWorks_mirror.BAK'
WITH FORMAT
GO

Specifying multiple options This next example shows how you can use multiple options at the same time.

BACKUP DATABASE AdventureWorks 
TO DISK = 'C:\AdventureWorks.BAK'
MIRROR TO DISK =  'D:\AdventureWorks_mirror.BAK'
WITH FORMAT, STATS, PASSWORD = 'Q!W@E#R$'
GO

Create Transaction Log backup using Only T-SQL Commands¶

There are only two commands for backup, the primary is BACKUP DATABASE which backs up the entire database and BACKUP LOG which backs up the transaction log. The following will show different options for doing transaction log backups.

Explanation¶

The BACKUP LOG command gives you many options for creating transaction log backups. Following are different examples.

Create a simple transaction log backup to disk¶

The command is BACKUP LOG databaseName. The "TO DISK" option specifies that the backup should be written to disk and the location and filename to create the backup is specified. The file extension is "TRN". This helps me know it is a transaction log backup, but it could be any extension you like. Also, the database has to be in the FULL or Bulk-Logged recovery model and at least one Full backup has to have occurred.

BACKUP LOG AdventureWorks 
TO DISK = 'C:\AdventureWorks.TRN'
GO

Create a log backup with a password¶

This command creates a log backup with a password that will need to be supplied when restoring the database.

BACKUP LOG AdventureWorks 
TO DISK = 'C:\AdventureWorks.TRN'
WITH PASSWORD = 'Q!W@E#R$'
GO

Create a log backup with progress stats¶

This command creates a log backup and also displays the progress of the backup. The default is to show progress after every 10%.

BACKUP LOG AdventureWorks 
TO DISK = 'C:\AdventureWorks.TRN'
WITH STATS
GO

Here is another option showing stats after every 1%.

BACKUP LOG AdventureWorks 
TO DISK = 'C:\AdventureWorks.TRN'
WITH STATS = 1
GO

Create a backup and give it a description¶

This command uses the description option to give the backup a name. This can later be used with some of the restore commands to see what is contained with the backup. The maximum size is 255 characters.

BACKUP LOG AdventureWorks 
TO DISK = 'C:\AdventureWorks.TRN'
WITH DESCRIPTION = 'Log backup for AdventureWorks'
GO

Create a mirrored backup¶

This option allows you to create multiple copies of the backups, preferably to different locations.

BACKUP LOG AdventureWorks 
TO DISK = 'C:\AdventureWorks.TRN'
MIRROR TO DISK =  'D:\AdventureWorks_mirror.TRN'
WITH FORMAT
GO

Specifying multiple options¶

This example shows how you can use multiple options at the same time.

BACKUP LOG AdventureWorks 
TO DISK = 'C:\AdventureWorks.TRN'
MIRROR TO DISK =  'D:\AdventureWorks_mirror.TRN'
WITH FORMAT, STATS, PASSWORD = 'Q!W@E#R$'
GO