SQL Server : Basic Backups

Introduction

There are two things to consider when thinking about backups.

  • Recovery Point Objective : How much data can you afford to lose?
  • Recovery Time Objective : How long can the database be down for while you do a recovery.

The closer these are to zero, the more money and effort you have to put into your backup processes.

Much of the functionality discussed here is available from the SQL Server Management Studio.

  • Right-click on the database.
  • Select Tasks > Back Up.
  • Enter the backup details and click the “OK” button.

The rest of the article will discuss backups from Transact-SQL.

Note. You should probably do most of your backups using a maintenance plan, which will be covered separately.

Full Backups

The minimum you can do to perform a backup is shown below. Each time you run it, it will append the new backup to the existing file, which isn’t very useful.

BACKUP DATABASE MyDatabase
TO DISK = 'C:\backups\MyDatabase.bak'

We can use flags to influence this behaviour. There is a complete list of possible flags here. For now, let’s look at these four.

  • INIT – Overwrites the Existing backup. Without this the default is NOINIT, which appends the new backup to the existing location.
  • FORMAT – Write new media header. This would make existing content in the location (file) invalid. Without this the default is NOFORMAT, which preserves the existing headers and therefore the backups too.
  • STATS – Used to show the progress of the backup. The default is 10%, STATS=10. This is only useful if you are running the backups interactively, but it doesn’t cause a problem with scripted backups.
  • COMPRESSION – Compress the backup. The opposite is NO_COMPRESSION.

The following backup command will use the same file each time, but will overwrite the contents with a clean backup.

BACKUP DATABASE MyDatabase
TO DISK = 'C:\backups\MyDatabase.bak'
WITH INIT, FORMAT, STATS=10, COMPRESSION

We can stripe backups across multiple files.

BACKUP DATABASE MyDatabase
TO DISK = 'C:\backups\MyDatabase1.bak',
   DISK = 'C:\backups\MyDatabase2.bak'
WITH INIT, FORMAT, STATS=10, COMPRESSION

Log Backups

The command for transaction log backups is similar to that for database backups. We don’t normally want to overwrite an existing log backup so we should use a unique name each time by using an incrementing counter or date-time.

BACKUP LOG MyDatabase
TO DISK = 'C:\backups\MyDatabaseLog1.trn'
WITH INIT, FORMAT, STATS=10, COMPRESSION

Remember, if you use a maintenance plan it will take care of backup naming for you!

Differential Backups

A differential backup includes everything that has changed since the last full backup. This is cumulative, so if you do full backups on Sunday, the daily differential backup would gradually grow in size as it covers more days, like this.

Sun  Mon  Tue  Wed  Thu  Fri  Sat
 |----|
 |---------|
 |--------------|
 |-------------------|
 |------------------------|
 |-----------------------------|

A differential backup is only possible if a full backup has already been done. Recovery of a differential backup is only possible if both the full backup and the differential backups are present. You can still keep log backups to allow specific point-in-time recovery.

The backup command follows the same format as before, but you add the DIFFERENTIAL flag to the WITH clause. Remember, if you want to keep multiple differentials you will need to use a different file name each day.

BACKUP DATABASE MyDatabase
TO DISK = 'C:\backups\MyDatabaseDiff1.bak'
WITH INIT, FORMAT, STATS=10, COMPRESSION, DIFFERENTIAL

Filegroup Backups

Rather than backing up a whole database in one go you can choose to backup an individual file groups. Ultimately you will need backups of all file groups and all the transaction logs to make the database consistent again, but you might choose to reduce the frequency of backups of file groups with relatively static data to improve backup speed.

BACKUP DATABASE MyDatabase
FILEGROUP = 'MyFileGroup1', FILEGROUP = 'MyFileGroup2'
TO DISK = 'C:\backups\MyDatabase-MyFileGroup.bak'
WITH INIT, FORMAT, STATS=10, COMPRESSION

System Database Backups

Backing up system databases is essentially the same as backing up a user database.

BACKUP DATABASE master
TO DISK = 'C:\backups\master.bak'
WITH INIT, FORMAT

BACKUP DATABASE model
TO DISK = 'C:\backups\model.bak'
WITH INIT, FORMAT

BACKUP DATABASE msb
TO DISK = 'C:\backups\msb.bak'
WITH INIT, FORMAT

You can’t backup “tempdb” as it is recreated every time the instance is started.

Depending on your recovery model, you can backup logs for some of the system databases.

BACKUP LOG model
 TO DISK = 'C:\backups\model.bak'
 WITH INIT, FORMAT;

BACKUP LOG msb
 TO DISK = 'C:\backups\msb.bak'
 WITH INIT, FORMAT;

You can’t backup the logs for the master database, so either do a full backups at intervals during the day, or rely on the fact there aren’t frequent changes to the master database on a daily basis.

For more information see: