SQL Server BACKUP DATABASE command     

(BACKUP DATABASE)  

Overview

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

SQL Server BACKUP LOG command     

(BACKUP LOG)  

Overview

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

SQLServer database backup Using with passwords

Problem

One issue when creating backups for SQL Server databases is that these backup files can be restored to any other SQL Server as long as the version of SQL Server supports the backup file that you are trying to restore.  In most cases this is not an issue unless you are trying to restore a backup from 6.5 or earlier onto a SQL Server 7.0 or later installation.  Because of this ability to restore the backups to any other SQL Server this exposes your data to potential theft or misuse of your data.  Most of the data in backup files is highly compressible, so a backup that maybe 1GB can easily be compressed to about 200MB.  So even your largest backup files could be vulnerable to someone copying them to their laptop or burning them to a DVD and then using the data in a way that you never intended.

Solution
One option that native SQL Server backups has is the use of password protected backup files.  When you create your backup you can specify a password for the backup file. Then when you want to restore the backup, the password must be specified otherwise the backup fails.  One problem with using this feature to protect your backups is that the GUI, both Enterprise Manger and SQL Server Management Studio do not support this option.   Also, when you create backups using maintenance plans this is not a supported option either.  So the only way to create password protected backups is by using the T-SQL commands for both backup and restore functions.

To do this it is very straight forward, here is the T-SQL command to backup your database:

BACKUP DATABASE Northwind TO DISK='C:\Northwind.BAK' WITH MEDIAPASSWORD='mssqltips'

Once the backup has been create we can run restore commands to see that the file can not be accessed without the password.

RESTORE FILELISTONLY FROM DISK='C:\Northwind.BAK'

RESTORE HEADERONLY FROM DISK='C:\Northwind.BAK'

RESTORE VERIFYONLY FROM DISK='C:\Northwind.BAK'

RESTORE DATABASE Northwind FROM DISK='C:\Northwind.BAK'

If we specify the password for any of these commands the commands work without issue.

RESTORE FILELISTONLY FROM DISK='C:\Northwind.BAK' WITH MEDIAPASSWORD='mssqltips' RESTORE HEADERONLY FROM DISK='C:\Northwind.BAK' WITH MEDIAPASSWORD='mssqltips' RESTORE VERIFYONLY FROM DISK='C:\Northwind.BAK' WITH MEDIAPASSWORD='mssqltips'
RESTORE DATABASE Northwind FROM DISK='C:\Northwind.BAK' WITH MEDIAPASSWORD='mssqltips'

When trying to use the GUI to do a restore, if the backup is password protected you will get these error messages:

SQL 2000

SQL 2005

Take a look at using passwords to protect your backup files. Although it does not offer the highest degree of security it does at least offer one additional hurdle someone will need to overcome in order to do the restore.

Next Steps

• Although this does add a level of security if someone really wants to crack the passwords they will find a way, so look for additional ways to secure your data
• You should make sure your backup files are not in a location that is easily accessible to people that should not have access to these files
• Make sure that people do not have the ability to create backup files if they are not supposed to.  The password option has be issued, so if someone can create a backup without the password and they can easily do a restore too.
• Keep your passwords in a secure place.  If you don't remember the password you will not be able to restore the backup file.

Creating a backup using SQL Server Management Studio

Creating backups using SQL Server Management Studio is pretty simple as well.  Based on how simple the T-SQL commands are, there is a lot of clicking that needs to occur in SSMS to create a backup.

ExplanationThe following screen shots show you how to create a full backup and a transaction log backup.

• Expand the "Databases" tree
• Right click on the database name you want to backup
• Select "Tasks" then "Back Up..." as shown below

• Specify the "Backup type"; Full, Differential or Transaction Log

• Click on "Add..." to add the location and the name of the backup file
• Click "OK" to close this screen

• And click "OK" again to create the backup