Backups are an essential part of database administration. They are so important that SQL Server provides multiple backup procedures and several ways to create backups—all designed to help you manage database backup and recovery easily and effectively. In this section, you will learn about standard backup procedures and the Transact-SQL backup process. The final component in a successful backup strategy involves database maintenance plans, which you will learn about in Chapter 15.
In SQL Server Management Studio, you can start the backup process by right-clicking the database you want to back up, pointing to Tasks, and then selecting Back Up. I will focus on how you use the Backup Database dialog box to perform backups in these situations:
When you want to create a new backup set
When you want to add to an existing backup set
Whenever you back up a database for the first time or start a new rotation on an existing backup set, follow these steps to create the backup:
Start SQL Server Management Studio. In Object Explorer view, connect to the appropriate server.
Expand the Databases folder. Right-click the database you want to back up, point to Tasks, and then select Back Up. This opens the Backup Database dialog box shown in Figure 14-2.
The database you want to back up should be selected in the Database drop-down list in the dialog box. The current recovery model for this database is also shown, but it is shaded because the recovery model cannot be changed. You cannot create transaction log backups when the recovery model is set to Simple.
Since this is a new backup set, select the type of backup you want to perform. Typically, for a first backup, you will want to perform a full backup. Then you can add to the backup set later using other types of backups.
You can back up the entire database or a subset of its files and filegroups. By default, Backup Component is set to Database to create a database backup. If you want to create a file and filegroup backup, select the Files And Filegroups option button. The Select Files And Filegroups dialog box displays, and you can choose the files and filegroups you want to back up. Click OK after making your selections.
In the Name text box in the Backup Set area, type a name for the backup set you are creating. This is an ordinary, nontechnical name that will help you tell at a glance what the backup contains. For example, name the first backup set for the Customer database Customer Backup Set 1. Then you can add the full, differential, and transaction log backups for this rotation to the set.
In the Description box, type a description of the backup, such as Set 1 contains the weekly full, daily differential, and hourly transaction log backups. This is the full backup for the week.
Use the Backup Set Will Expire options to set an expiration interval or date. This allows the backup to overwrite the media after a specified period or date.
If a backup set exists and is listed in the Destination area, select it and click Remove.
Click Add to display the Select Backup Destination dialog box shown in Figure 14-3. To use a new file as the backup destination, select the File Name option button and type the full path to the backup file, such as E:DATA BACKUPSCUST.BAK or \OMEGABACKUPSCUST.BAK. To use a backup device, select the Backup Device option button, and then choose the backup destination using the drop-down list box. Click OK when you are ready to continue.
To schedule the backup, click Schedule, and then configure the backup schedule as discussed in Chapter 15.
To set additional options for the backup, select the Options page. You use the available options as follows:
Back Up To The Existing Media Set. Select this option if you are using an existing media set. You can specify whether to append to the existing backup set or overwrite all existing backup sets.
Check Media Set Name And Backup Set Expiration. Use this option to ensure that you are writing to the correct tape set and that the tape expiration date has not been reached. If you select this option, enter the media set name that should be verified.
Back Up To A New Media Set, And Erase All Existing Backup Sets. Select this option if you want to create a new media set and erase all existing media sets. Then enter the media set name and an optional description.
Verify Backup When Finished. Choose this option to verify the entire backup and check for errors. Generally, it is a very good idea to verify your backups.
Perform Checksum Before Writing To Media. Use this option to check the data you are backing up prior to writing. This is the same as using the CHECKSUM or NOCHECKSUM options with the BACKUP statement. If you perform a checksum, you can also specify to continue on checksum error.
Truncate The Transaction Log By Removing Inactive Entries. Select this option to clean out entries that are no longer needed after the backup. These entries are for transactions that have been committed or rolled back. (This option is set by default for transaction log backups.)
Back Up The Tail Of The Log... Use this option to back up the active transaction log (those transactions that have not been completed and are at the tail of the log). When you use the full or bulk-logged recovery model, you must back up the active transaction log before you can restore the database using SQL Server Management Studio.
Unload The Tape After Backup. Select this option to eject the tape after the backup (only valid with tape devices).
Click OK to start the backup or confirm that you want to schedule the backup. If you opted to verify the data, the verification process starts immediately after the backup ends.
When you want to add to an existing backup set, complete the following steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the appropriate server.
Expand the Databases folder. Right-click the database you want to back up, point to Tasks, and then select Back Up to open the Backup Database dialog box (shown in Figure 14-2).
The database you want to back up should be selected in the Database drop-down list.
Select the type of backup you want to perform: Full, Differential, or Transaction Log. Typically, when you are adding to an existing set, you do so using a differential or transaction log backup. You cannot create transaction log backups when the recovery model is set to Simple.
You can back up the entire database or a subset of its files and filegroups. By default, Backup Component is set to Database to create a database backup. If you want to create a file and filegroup backup, select the Files And Filegroups option button. The Select Files And Filegroups dialog box is then displayed, allowing you to choose the files and filegroups to back up. Click OK after making your selections.
In the Backup Set panel’s Name box, type a name for the backup you are creating. In the Description box, type a description of the backup, such as Daily differential backup.
Use the Backup Set Will Expire options to set an expiration interval or date. This allows the backup to overwrite the media after a specified period or date.
A backup set should be listed in the Destination area. If so, click Contents to see the current contents of this backup set. If a backup set is not listed, click Add to display the Select Backup Destination dialog box, and then enter the location of the existing backup. Click OK when you are ready to continue.
Select the Options page. Since you are adding additional data to the existing backup set, the options Backup To The Existing Media Set and Append To The Existing Backup Set should be selected.
Real World
Whether you back up data to a tape or disk drive, you should use the tape rotation technique. Create multiple sets, and then write to these sets on a rotating basis. With a disk drive, for example, you could create these backup files on different network drives and use them as follows:
//omega/data1drive/backups/cust_set1.bak Used in week 1, 3, 5, and so on for full and differential backups of the customer database.
//omega/data2drive/backups/cust_set2.bak Used in week 2, 4, 6, and so on for full and differential backups of the customer database.
//omega/data3drive/backups/cust_set3.bak Used in the first week of the month for full backups of the customer database.
//omega/data4drive/backups/cust_set4.bak Used in the first week of the quarter for full backups of the customer database.
Do not forget that each time you start a new rotation on a tape set, you should overwrite the existing media. For example, you would append all backups in week 1. Then, when starting the next rotation in week 3, you would overwrite the existing media for the first backup and then append the remaining backups for the week.
For transaction log backups, you usually will want to select the Truncate The Transaction Log By Removing Inactive Entries check box. This ensures that inactive entries are cleared out of the transaction log after a backup.
To schedule the backup, click Schedule, and then configure the backup schedule as discussed in Chapter 15.
Click OK to start the backup or to confirm that you want to schedule the backup. If you opted to verify the data, the verify process starts immediately after the backup ends.
Through a process called parallel striped backups, SQL Server can perform backups to multiple backup devices simultaneously. As you can imagine, writing multiple backup files at the same time can dramatically speed backup operations. The key to this speed, however, is having physically separate devices, such as three different tape devices or three different drives that you are using for the backup. You cannot write parallel backups to a single tape device, and you cannot write parallel backups to the same drive.
Multiple devices used in a backup operation are referred to as a media set. SQL Server allows you to use from 2 to 32 devices to form the media set. These devices must be of the same type. For example, you cannot create a striped backup with one backup tape device and one backup drive device.
The two main operations involved in parallel striped backups are:
Creating a new media set.
Adding to an existing media set.
To create a new media set using multiple devices, complete the following steps:
Select the server you want to use, and then create each of the backup devices you need in the media set, as described in the section titled "Creating a Backup Device" earlier in this chapter.
Right-click the database you want to back up, point to Tasks, and then select Back Up to display the Backup Database dialog box.
Follow the steps outlined in the subsection titled "Creating a New Backup Set" earlier in this chapter. Repeat step 10 for each backup device you want to use in the media set.
To add to an existing media set, complete the following steps:
Right-click the database you want to back up, point to Tasks, and then select Back Up to display the Backup Database dialog box.
Follow the steps outlined in the subsection titled "Adding to an Existing Backup Set" earlier in this chapter. The only difference is that in step 8 you should see a list of all the backup devices used in the media set. If you do not, you will need to add them one by one using the Add button and the related Select Backup Destination dialog box.
An alternative to using the backup procedures in SQL Server Management Studio is to use the T-SQL BACKUP statement. You use BACKUP DATABASE to back up databases and BACKUP LOG to back up transaction logs.
Tip
If you back up databases using Transact-SQL, you lose one of the biggest benefits of SQL Server—the automated recovery process. With automated recovery, you do not have to worry about which backup to apply in which situation, which command flags to use, and so on. Furthermore, because you can schedule automated and unattended backups, you do not really need to run backups manually through SQL as often as in the past. I recommend using the SQL Server Management Studio backup and restore process whenever possible.
BACKUP DATABASE has dual syntax. Example 14-2 shows the syntax and usage for full and differential backups. A full backup is the default operation.
Example 14-2. BACKUP DATABASE Syntax and Usage for Full and Differential Backups
Syntax
BACKUP DATABASE { database_name | @database_name_var }
TO < backup_device > [ ,...n ]
[ [ MIRROR TO < backup_device > [ ,...n ] ] [ ...next-mirror ] ]
[ WITH
[ BLOCKSIZE = { blocksize | @blocksize_variable } ]
[ [ , ] { CHECKSUM | NO_CHECKSUM } ]
[ [ , ] { STOP_ON_ERROR | CONTINUE_AFTER_ERROR } ]
[ [ , ] DESCRIPTION = { 'text' | @text_variable } ]
[ [ , ] DIFFERENTIAL ]
[ [ , ] EXPIREDATE = { date | @date_var }
| RETAINDAYS = { days | @days_var } ]
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] { FORMAT | NOFORMAT } ]
[ [ , ] { INIT | NOINIT } ]
[ [ , ] { NOSKIP | SKIP } ]
[ [ , ] MEDIADESCRIPTION = { 'text' | @text_variable } ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[[ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
[ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] RESTART ]
[ [ , ] STATS [ = percentage ] ]
[ [ , ] COPY_ONLY ]
]Usage
USE master EXEC sp_addumpdevice 'disk', 'Customer Backup Set 1', 'f:dataackupCust2.dat' BACKUP DATABASE 'Customer' TO 'Customer Backup Set 1'
Example 14-3 shows the BACKUP DATABASE syntax for file and filegroup backups.
Example 14-3. Sample 14-3 BACKUP DATABASE Syntax and Usage for File or Filegroup Backups
Syntax
BACKUP DATABASE { database_name | @database_name_var }
<file_or_filegroup> [ ,...f ]
TO <backup_device> [ ,...n ]
[ [ MIRROR TO <backup_device> [ ,...n ] ] [ ...next-mirror ] ]
[ WITH
[ BLOCKSIZE = { blocksize | @blocksize_variable } ]
[ [ , ] { CHECKSUM | NO_CHECKSUM } ]
[ [ , ] { STOP_ON_ERROR | CONTINUE_AFTER_ERROR } ]
[ [ , ] DESCRIPTION = { 'text' | @text_variable } ]
[ [ , ] DIFFERENTIAL ]
[ [ , ] EXPIREDATE = { date | @date_var }
| RETAINDAYS = { days | @days_var } ]
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] { FORMAT | NOFORMAT } ]
[ [ , ] { INIT | NOINIT } ]
[ [ , ] { NOSKIP | SKIP } ]
[ [ , ] MEDIADESCRIPTION = { 'text' | @text_variable } ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable }]
[ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] RESTART ]
[ [ , ] STATS [ = percentage ] ]
[ [ , ] COPY_ONLY ]
]
<file_or_filegroup> :: =
{ FILE = { logical_file_name | @logical_file_name_var }
|
FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }
| READ_WRITE_FILEGROUPS }Usage
USE master EXEC sp_addumpdevice 'disk', 'Customer Backup Set 1', 'f:dataackupCust2.dat' BACKUP DATABASE Customer FILE = 'Customer_data', FILEGROUP = 'Primary', FILE = 'Customer_data2', FILEGROUP = 'Secondary' TO 'Customer Backup Set 1'
Example 14-4 shows the syntax for BACKUP LOG. By default, this command truncates the log after the backup.
Example 14-4. BACKUP LOG Syntax and Usage
Syntax for Backing Up the Log
BACKUP LOG { database_name | @database_name_var }
{
TO <backup_device> [ ,...n ]
[ [ MIRROR TO <backup_device> [ ,...n ] ] [ ...next-mirror ] ]
[ WITH
[ BLOCKSIZE = { blocksize | @blocksize_variable } ]
[ [ , ] { CHECKSUM | NO_CHECKSUM } ]
[ [ , ] { STOP_ON_ERROR | CONTINUE_AFTER_ERROR } ]
[ [ , ] DESCRIPTION = { 'text' | @text_variable } ]
[ [ ,] EXPIREDATE = { date | @date_var }
| RETAINDAYS = { days | @days_var } ]
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] { FORMAT | NOFORMAT } ]
[ [ , ] { INIT | NOINIT } ]
[ [ , ] { NOSKIP | SKIP } ]
[ [ , ] MEDIADESCRIPTION = { 'text' | @text_variable } ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable }]
[ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]
[ [ , ] NO_TRUNCATE ]
[ [ , ] { NORECOVERY | STANDBY = undo_file_name } ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] RESTART ]
[ [ , ] STATS [ = percentage ] ]
[ [ , ] COPY_ONLY ]
]
}
<backup_device> ::=
{
{ logical_backup_device_name | @logical_backup_device_name_var }
|
{ DISK | TAPE } = { 'physical_backup_device_name' |
@physical_backup_device_name_var }
}Syntax for Truncating the Log
BACKUP LOG { database_name | @database_name_var }
{
WITH
{ NO_LOG | TRUNCATE_ONLY } ]
}Usage
USE master EXEC sp_addumpdevice 'disk', 'Customer_log1', 'f:dataackupCust_log.dat' BACKUP LOG Customer TO Customer_log1
Transaction logs are essential to the timely recovery of SQL Server databases. Unlike database backups, which can be full or differential, transaction log backups are usually incremental. This means that each transaction log backup has a record of transactions only within a certain time frame. Transaction logs are always applied in sequence—with the completion time of the last full or differential backup marking the beginning of a transaction log sequence.
Consequently, to restore the database you must apply each transaction log in sequence up to the point of failure. For example, if you run a full backup at 1:00 P.M. and the database fails at 1:46 P.M., you would restore the last full backup and then apply each transaction log created after that time, such as the backups at 1:15 P.M., 1:30 P.M., and 1:45 P.M. As you can see, without the incremental transaction log backups, you would lose all the transactions that took place after the 1:00 P.M. full backup.
You can perform transaction log backups like any other backup. Still, there are a few details that you should know before beginning this kind of backup, and the following subsections cover these details.
Although the normal backup process for transaction logs is fairly straightforward, SQL Server has some tricky features involving the option flags that you can set for the backup or the database, or both. The following database options prevent you from using a transaction log sequence to recover a database:
Truncate Log On Checkpoint. Clears out inactive entries in the transaction log on checkpoint, which means you cannot use the log for recovery.
Using Non-Logged Operations. Commands that bypass the log invalidate a log backup sequence.
ALTER DATABASE. Adding or deleting files with ALTER DATABASE invalidates a backup sequence.
When you back up transaction logs, you have several options that determine how the backups are made. With SQL Server Backup in SQL Server Management Studio, you can use the Truncate The Transaction Log By Removing Inactive Entries option. Setting this option clears committed transactions out of the log after a log backup. The BACKUP LOG command normally clears out committed or aborted transactions after a log backup as well. However, you can override this behavior with these options:
TRUNCATE_ONLY. Removes inactive entries from the log without creating a backup. This invalidates the log sequence.
NO_LOG. Same as TRUNCATE_ONLY, but this option does not log the BACKUP LOG command in the transaction log. This option is designed for a situation in which the transaction log or its home drive is full, and you must truncate the log without writing to the log device.
NO_TRUNCATE. Writes all the transaction log entries from the last backup to the point of failure. Use this option when the database is corrupt and you are about to restore it.
Tip
After you use TRUNCATE_ONLY or NO_LOG, always perform a full or differential backup. This revalidates the log sequence. Additionally, because you can grow logs automatically, you should rarely encounter a situation in which you need to truncate the log without logging. The log can run out of space only if you set a maximum size or the drive(s) that the log uses runs out of space.
In SQL Server 2005, full-text catalogs are treated as files and are included in the database file set for the purposes of backup and restore. When you back up a database, the full-text catalogs are backed up automatically. When you back up the filegroup associated with one or more full-text catalogs, the catalogs are backed up as well. Changes to full-text catalogs are backed up with standard differential backups of a database or the associated filegroup.
You can back up a full-text catalog by itself using T-SQL. To do this, you specify the logical file name of the full-text catalog using the FILE clause of the BACKUP statement. Consider the following example:
USE master EXEC sp_addumpdevice 'disk', 'Customer_Catalog', '\omegaackupscust.bak' BACKUP DATABASE Customer FILE = 'customerdb_cat' TO Customer_Catalog
In this example, you specify the backup device to use as Customer_Catalog. You then create a full backup of the full-text catalog named customerdb_cat. If you later wanted to save only the changes since the full backup of the catalog, you could create a differential backup of the catalog:
USE master BACKUP DATABASE Customer FILE = 'customerdb_cat' TO Customer_Catalog WITH DIFFERENTIAL
You can back up multiple full-text catalogs using filegroup backups. For example, if you have created a filegroup called Catalogs_Primary and associated multiple catalogs with it, you can perform a full backup of the filegroup using:
USE master BACKUP DATABASE Customer FILEGROUP = 'Catalogs_Primary' TO Customer_Catalog