Types of Backups

In this section, we'll explore the many types of backups you can use in SQL Server. I'll show you how to use both the GUI and T-SQL. I always prefer using T-SQL to script my backups since you can reproduce the process, and also because you have more options.

Note 

You must be a member of the sysadmin, db_owner, or db_backupoperator role to create a backup of a database. Additionally, you may need the password for the backup media if one is set.

Full Backups in Enterprise Manager

The full database backup is the easiest to perform and is the only type of backup that works on the master database. A full database backup takes a complete snapshot of your database objects and relationships. It also backs up users and their permissions. Under the covers, here's what happens when you start a full backup:

1. The database backup begins and the time is noted.

2. Data pages are backed up in a sequential manner from each data file.

3. Transactions that have occurred since step 1 are appended to the backup.

Let's take a simple scenario. You're backing up your database using a full database backup. You begin the backup at 2:00 A.M. Meanwhile, transactions are still occurring on your e-commerce system and sales are recorded. When the data pages are finished being backed up at 4:00 A.M., the two hours of transactions are also backed up. This ensures that your backup is consistent with the end time.

Caution 

A full backup will back up your users, but not your logins-that is, unless you back up the master database. Once you restore your database to a different server, your users and your login IDs will be out of synch. I'll show you how to fix this later in this chapter.

To back up a database in Enterprise Manager, right-click on the database and select All Tasks | Backup Database. In the SQL Server Backup screen (shown in Figure 8-1), most of the important settings are automatically set. The options that are available in this screen vary according to the recovery model your database is in.

Figure 8-1: Backing up a database in Enterprise Manager

Most of the options are self-describing and need no explanation. For the Backup option, select the backup type you want to perform. In this example, I'm performing a complete (full) backup. If you have a tape drive installed, you can select to back up to it or to disk. If you have no tape device, only the disk option is available.

If this is the first time you've performed a backup on the database, there won't be any backup devices or files in the Backup To box. If this is the case, you'll have to add a backup device or file. A backup device is essentially a predefined backup location where you can deposit a number of backups. It is similar to a data device in SQL Server 6.5, where you could store many databases on a single device. A database file can also hold multiple backups, but the file is not created until the backup actually occurs. To add a new backup device or database file, click the Add button, then specify the location under the Backup Device option.

Tip 

If you're tired of changing the backup directory, the default directory for backups can be changed by modifying the BackupDirectory data item in the HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer registry key.

You can keep adding additional backup devices or files by clicking the Add button and specifying a location. If you add more than one file or device, the information is spread over the set of backup files evenly. By spreading your backup over several files or devices, you can speed up the backup and restore process for large databases. This is especially useful when you're backing up a large database to tape. Note that in order to restore the database, you will need to make sure you have all the backup files.

Caution 

It is possible to back up corrupt databases. Before you issue a backup command, you should issue a DBCC CHECKDB to check the health of the database.

In the Overwrite area, the Append To Media option adds backups to the end of the file, while the Overwrite Existing Media option deletes the old backups and creates a new one. The last option on this tab gives you the ability to create a job to execute the backup. You can schedule your job to back up your database through SQL Server Agent. By clicking the '…' button, you can schedule your backup with the following options:

• Start Automatically When SQL Server Agent Starts This is generally set to execute when SQL Server first starts.

• Start Whenever the CPU(s) Becomes Idle The backup starts after the CPU reaches a certain level that you designate in the SQL Server Agent properties. We discussed this property in Chapter 4.

• One Time The backup can execute at a defined time. This is handy when you have a large job to execute at an off-peak time.

• Recurring You can configure the backup to run at an interval as small as a minute or as large as once every 99 months.

  Note 

  If you use a backup file, the space is not reserved until the backup actually occurs. If you choose the Append To Media option, the backup files can become large very quickly.

In the Options tab (shown in Figure 8-2), you can configure some of the more advanced backup settings:

Figure 8-2: Options tab of backup creation

• Verify Backup Upon Completion This option compares the backup against the live database. If you check this option, your server's performance will drop significantly while this operation occurs, but you increase the likelihood of catching a bad backup before it becomes a problem.

• Eject Tape After Backup This option ejects the media after it completes the backup. This could be a bad option to check if you're storing multiple iterations of the backup on one tape. It's handy if you want to be visually alerted when the backup completes.

• Remove Inactive Entries From Transaction Log This option is only available when you're performing a transaction log backup. This option truncates the transaction log whenever the backup is complete. It is checked by default. If you don't want your transaction log backup files to be cumulative, deselect this option.

• Check Media Set Name and Backup Set Expiration This option confirms the media name and expiration date of the media before overwriting the file. Trying to overwrite a backup media set that hasn't expired results in the error shown in Figure 8-3. If you select this option, specify the name of the media set to check or leave it blank if you didn't specify a media set name.

  
  Figure 8-3: Error received when attempting to overwrite a backup media set

• Backup Set Will Expire Under this option, specify how long the backup will be kept before being overwritten. This option allows you to override the default retention time that is set in the main Server Properties dialog box in the Database Settings tab.

In the Trenches

If you try to back up a database with an incomplete multifile backup media set (a backup with multiple backup files), you receive the following error:

The only way around this error is to either find the additional file or device, or select the Overwrite option from the General tab and the Initialize option under the Options tab.

Tip 

You can set the default expiration for a backup in the SQL Server Properties dialog box. Set the Default Backup Media Retention Day(s) option in the Database Settings tab. By default, SQL Server is configured to retain backups indefinitely.

• Initialize and Label Media This option deletes the contents of any existing backups in the backup file or device. You can also use the Media Set Name and Description options to help you locate your backups. This option is not available unless you selected the Overwrite button in the General tab.

Tip 

If you want to back up a database to a network drive, use the full UNC path (\\ComputerName\ShareName\Path\Filename.bak). SQL Server cannot see mapped drives. Also, make sure that the account that starts SQL Server and SQL Server agent can see the network share.

Full Backups in T-SQL

Using T-SQL to back up your database provides options that are not available in Enterprise Manager. To back up a database with this method, use the BACKUP DATABASE syntax as shown in the following:

BACKUP DATABASE { database_name | @database_name_var }  TO < backup_device > [DISK=<backup file>][ ,...n ]  [ WITH      [ BLOCKSIZE = { <block size> | @blocksize_variable } ]      [ [ , ] DESCRIPTION = { <'text'> | @text_variable } ]      [ [ , ] DIFFERENTIAL ]      [ [ , ] EXPIREDATE = { <date> | @date_var }          | RETAINDAYS = { <days to retain> | @days_var } ]      [ [ , ] PASSWORD = { <password> | @password_variable } ]      [ [ , ] FORMAT | NOFORMAT ]      [ [ , ] { INIT | NOINIT } ]      [ [ , ] MEDIADESCRIPTION = { <'text'> | @text_variable } ]      [ [ , ] MEDIANAME = { <media name> | @media_name_variable } ]      [ [ , ] MEDIAPASSWORD = { <media password> | @mediapassword_variable } ]     [ [ , ] NAME = { <backup set name> | @backup_set_name_var } ]      [ [ , ] { NOSKIP | SKIP } ]      [ [ , ] { NOREWIND | REWIND } ]      [ [ , ] { NOUNLOAD | UNLOAD } ]      [ [ , ] RESTART ]      [ [ , ] STATS [ = <percentage> ] ]

As you can see, you have more options available, as follows:

• BLOCKSIZE The number of bytes per block that SQL Server uses to back up the database. SQL Server automatically finds the best setting for this option, and usually you should not adjust it. In rare cases, some media devices such as CD-ROM writers may require a change in this setting.

• DESCRIPTION A description of the backup that appears in the backup file's header. This setting is useful for organizational purposes when you're trying to restore the database.

• EXPIREDATE The date when the backup can be purged.

• RETAINDAYS Specifies how many days after the backup the file is protected from overwriting.

• PASSWORD Sets a password on the backup set. This prevents anyone from restoring the backup unless they know the password. This password does not prevent you from overwriting the backup file.

• FORMAT | NOFORMAT The FORMAT option erases all header information on the media before backing up the database. This is handy when you want to format the tape drive to begin a new set of backups. You should use this option only when you're sure you won't need any old information on the tape. If you use the FORMAT option, the INIT and SKIP options are assumed.

• INIT | NOINIT The INIT option erases all information on the backup set. NOINIT will append the backup to the end of the existing file.

• MEDIADESCRIPTION This option adds a media description to the media, such as a tape drive. This allows a backup operator to quickly find the proper media since most third-party tools can read the description.

• MEDIANAME This adds a media name to the media, such as a tape drive.

• NAME This adds the name of the backup set and can contain up to 128 characters.

• NOSKIP | SKIP The NOSKIP option tells SQL Server to check the media name and expiration date before overwriting the backup.

• NOREWIND | REWIND The NOREWIND option is used to prevent other applications from using the tape until SQL Server issues a BACKUP or RESTORE command. If REWIND is specified, SQL Server releases control of the tape media and rewinds the tape.

• NOUNLOAD | UNLOAD When UNLOAD is specified, the tape is rewound and unloaded.

• RESTART On tape operations, this option restarts a backup operation that has failed previously.

• STATS This option returns every specified percentage of backup completion. For example, a setting of 10 reports every 10 percent.

You can use the following syntax to back up the Northwind database to two backup files, for example. The NOINIT option means that the data will be appended to the end of the backup file if one already exists.

BACKUP DATABASE Northwind  TO  DISK =  N'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\northwind.bak',     DISK =  N'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Northwind2.bak'     WITH  NOINIT , NAME = N'Northwind backup',     NOSKIP , STATS = 25,  NOFORMAT

This command returns the following results:

27 percent backed up. 52 percent backed up. 77 percent backed up. Processed 352 pages for database 'Northwind', file 'Northwind' on file 5. 100 percent backed up. Processed 1 pages for database 'Northwind', file 'Northwind_log' on file 5. BACKUP DATABASE successfully processed 353 pages  in 2.757 seconds (1.046 MB/sec).

For larger databases, you may want to set the STAT option to a much lower setting (such as 5) so you receive a status report more frequently.

Tip 

You can't back up a database to a removable disk (like a Zip drive) in Enterprise Manager. Instead, you can use T-SQL to write to the drive as long as it's formatted.

Configuring Devices with T-SQL

To add a backup device, use the sp_addumpdevice system stored procedure. For instance, the following syntax adds a backup device called NorthwindDevice on the C drive:

EXEC sp_addumpdevice 'disk', 'NorthwindDevice',    'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\NorthwindBakDevice.dat'

Backing up to the database device is simple. All you have to do is state the device name instead of using the DISK option:

BACKUP DATABASE Northwind TO NorthwindDevice

This outputs the following results:

Processed 352 pages for database 'Northwind', file 'Northwind' on file 1. Processed 1 pages for database 'Northwind', file 'Northwind_log' on file 1. BACKUP DATABASE successfully processed 353 pages in 1.348 seconds (2.139 MB/sec).

Differential Backups

Differential backups capture only the data that changed since the last full backup. Since these backups are considerably smaller and faster to perform than full backups, you can perform them more frequently and add them to your backup arsenal. To perform a differential backup in Enterprise Manager, simply select the Differential option when you create the backup.

To perform a differential backup in T-SQL, use the BACKUP command, but select the DIFFERENTIAL option as shown here:

BACKUP DATABASE Northwind TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\NorthwindDiff.bak'       WITH  NOINIT ,  NOUNLOAD , DIFFERENTIAL , NAME = N'Northwind backup',       STATS = 10,  NOFORMAT

This outputs results similar to those for a full backup. If the database doesn't have a lot of changes, shorten the STAT parameter to compensate.

Transaction Log Backups

Transaction log backups are similar to incremental Windows backups. This type of backup backs up transactions that have occurred in the database, and optionally purges the transaction log afterward. Transaction log backups give you true point-in-time recovery flexibility. To create a transaction log backup in Enterprise Manager, select the Transaction Log option.

To perform a transaction log backup, you'll have to ensure that your database is in Full or Bulk-Logged recovery models. I'll discuss how to change to these recovery models in a later section of this chapter. If you're not in one of these models, the Transaction Log option is not available in the Backup dialog box. If you try to back up a database that is in Simple recovery model, you'll receive the following error:

Server: Msg 4208, Level 16, State 1, Line 1 The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE. Server: Msg 3013, Level 16, State 1, Line 1 BACKUP LOG is terminating abnormally.

Transaction Log Backups in T-SQL

To perform a transaction log backup in T-SQL, use the BACKUP LOG command instead of the BACKUP DATABASE command. The rest of the syntax is similar to the BACKUP DATABASE command. There are two options that are new to the BACKUP LOG command:

• NO_LOG | TRUNCATE_ONLY These two synonymous options truncate the transaction log without backing it up.

• NO_TRUNCATE This option allows you to back up the transaction log and not truncate it afterward. This is handy when you're trying to back up a transaction log that may have a damaged database due to a physical device failure. Never let your log grow for a long time by specifying this option.

To back up the transaction log in the Northwind database, use the following syntax:

BACKUP LOG Northwind TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\NorthwindTran.bak' WITH  NOINIT , NOUNLOAD , NAME = N'Northwind backup', STATS = 10, NOFORMAT

This outputs the following results:

80 percent backed up. 100 percent backed up. Processed 10 pages for database 'Northwind', file 'Northwind_log' on file 5. BACKUP LOG successfully processed 10 pages in 0.089 seconds (0.920 MB/sec).

If there is no data in the transaction log to be backed up, you receive the following message:

There is no current database backup. This log backup cannot be used to roll forward a preceding database backup. 100 percent backed up. Processed 1 pages for database 'Northwind', file 'Northwind_log' on file 3. BACKUP LOG successfully processed 1 pages in 0.137 seconds (0.007 MB/sec).

If you only want to truncate the transaction log, use the following syntax:

BACKUP LOG Northwind WITH NO_LOG

Backing Up Individual Files and File Groups

You also have the ability to back up individual files or file groups. This is perfect when you have a VLDB (very large database) with multiple file groups that take hours to back up. This allows you to spread your backups over time to avoid slowing down the server. The main thing to keep in mind when performing file or file group backups is to watch the database design. You don't want to restore one file group that has the Customers table in it and not have the Customer_Details table in the same file group. If this occurs, your data will be out of synch.

To back up a file or file group in Enterprise Manager, simply select the File And Filegroup option in the Database Backup screen and select the file or file group to back up. To back up a file group through T-SQL, you must add the FILEGROUP parameter and specify the file group name as shown here:

BACKUP DATABASE [Northwind]   FILEGROUP = N'PRIMARY'  TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Northwind.bak' WITH  NOINIT ,   NAME = N'Primary FileGroup Backup',  NOSKIP ,  STATS = 10

backupset Table

A useful table you can use to determine information about your backups is the backupset table in the msdb database. The backupset table gives you detailed information about when your backup began and ended, as well as when the last backup occurred and against which database. It also tells you who performed the backup and what type of backup it is. You can use the following query to find out an abridged amount of information about the backupset table, including how long your backups are taking to run:

SELECT database_name, position,backup_size, DATEDIFF(second,backup_start_date, backup_finish_date) as Time, backup_finish_date as Last_Backup FROM msdb.dbo.backupset

This query outputs the following results (your results will vary):

database_name  position    backup_size  Time  Last_Backup  -------------- ----------- ------------ ----- ----------------------- Ecommerce      1           870912       1     2001-02-12 16:23:45.000 SSC            1           1066496      1     2001-03-04 15:28:47.000 SSC            2           1064960      1     2001-03-04 15:30:58.000 Northwind      1           2968064      2     2001-03-04 15:35:00.000 Northwind      1           2985984      3     2001-03-04 16:09:01.000 Northwind      2           2982912      3     2001-03-04 16:14:20.000 Northwind      3           2982912      3     2001-03-04 16:50:50.000 (7 row(s) affected)

Optimizing Backup and Restore Performance

Performing backups of your databases can take many hours for large databases. You can use these tips to speed up the backup and restore of your databases:

• It is faster to back up files locally on the server and then move them over to a separate network or tape drive.

• Perform complete backups during off-peak hours. Backups can be quite I/O intensive on some machines and can slow down your server noticeably.

• Back up files to a RAID 1 or 10 drive. This is because of the large number of writes that occur during the backup process. Also, make sure that you separate your tape SCSI controller and your disk controller.

• Use storage area network (SAN) drives for backing up large databases. Most third-party backup systems provide a LAN-less backup system where the file will be backed up right over the SAN without having to tax your network.

• Back up large databases to multiple backup devices in parallel. SQL Server creates a backup thread for each backup device.

• If you're backing up to a tape drive, try to back up to parallel tape drives.

• If you're recovering from tape, enable hardware compression on the tape drive to improve throughput.