OverviewThere 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.
ExplanationThe BACKUP DATABASE command gives you many options for creating backups. Following are different examples.
Create a full backup to diskThe 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 backupThis command adds the "WITH DIFFERENTIAL" option.
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\AdventureWorks.BAK'
WITH DIFFERENTIAL
GO |
Create a file level backupThis 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 backupThis 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 filesThis 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 passwordThis 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 statsThis 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 descriptionThis 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 backupThis 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 optionsThis 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 |