Backing Up the Database

Having discussed backup types, models, and creating devices, it's time now to look at the actual backup commands. Never has an Enterprise-level Database Management System backup been easier to implement. You can use Transact -SQL commands stored in scripts to do your backups, or Enterprise Manager, which will allow you to set up one-time backups or scheduled backups with just a few clicks. SQL Agent can also be configured to perform backups due to performance conditions and alerts, and there is a Database Maintenance Plan Wizard that steps you through setting up and scheduling your backups .

NOTE

For more information on the SQL Agent and the Maintenance Plan Wizard, see Chapters 17, "Database Maintenance," and 18, "SQL Server Scheduling and Notification."

Backing Up Databases with T-SQL

The BACKUP DATABASE command (oddly enough) is used to back up a database. To initiate a full database backup to a permanent backup device the statement would be:

 BACKUP DATABASE northwind TO nwbackup 

NOTE

In versions of SQL Server prior to Version 7.0, backups were performed using the DUMP command. This command is still supported for backward compatibility purposes, but should be replaced with the BACKUP command as the DUMP command will likely not be supported in future versions of SQL Server.

Now that I've eased you into the backup command, let's look at the full syntax. Don't worry. It's not as bad as it looks, and I'll provide some examples later with the most commonly used commands. Listing 16.1 shows the BACKUP DATABASE syntax.

Listing 16.1 Complete Syntax for BACKUP DATABASE

 BACKUP DATABASE {  database_name   @database_name_var  }     [< file_or_filegroup > [ ,...  n  ] ] TO < backup_device > [ ,...  n  ] [ WITH     [ BLOCKSIZE = {  blocksize   @blocksize_variable  } ]     [ [ , ] DESCRIPTION = { '  text  '  @text_variable  } ]     [ [ , ] DIFFERENTIAL ]     [ [ , ] EXPIREDATE = {  date   @date_var  }          RETAINDAYS = {  days   @days_var  } ]     [ [ , ] PASSWORD = {  password   @password_variable  } ]     [ [ , ] FORMAT  NOFORMAT ]     [ [ , ] { INIT  NOINIT } ]     [ [ , ] MEDIADESCRIPTION = { '  text  '  @text_variable  } ]     [ [ , ] MEDIANAME = {  media_name   @media_name_variable  } ]     [ [ , ] MEDIAPASSWORD = {  mediapassword   @mediapassword_variable  } ]     [ [ , ] NAME = {  backup_set_name   @backup_set_name_var  } ]     [ [ , ] { NOSKIP  SKIP } ]     [ [ , ] { NOREWIND  REWIND } ]     [ [ , ] { NOUNLOAD  UNLOAD } ]     [ [ , ] RESTART ]     [ [ , ] STATS [ =  percentage  ] ] ] } < backup_device > ::=     {         {  logical_backup_device_name   @logical_backup_device_name_var  }                  { DISK  TAPE } =             { '  physical_backup_device_name  '  @physical_backup_device_name_var  }     } < file_or_filegroup > ::=     {         FILE = {  logical_file_name   @logical_file_name_var  }                  FILEGROUP = {  logical_filegroup_name   @logical_filegroup_name_var  }     } 

Table 16.1. Detailed Description of Each of the Preceding Parameters

Parameter	Description
BLOCKSIZE	The physical block size specified in bytes. SQL Server will choose a block size that is appropriate to the device if one is not specified.
DESCRIPTION	A text description of the backup set.
DIFFERENTIAL	This indicates that this is to be a differential backup.
EXPIREDATE	Specifies a date, at which time the backup can be overwritten.
RETAINDAYS	Similar to EXPIREDATE , the number of days the backup is retained before it can be overwritten.
PASSWORD	Specifies a password for the backup set. If defined, the password must be supplied to perform a restore.
FORMAT NOFORMAT	FORMAT overwrites the backup device and media header. This will render the entire media set unusable, so use caution. FORMAT implies skip and init. NOFORMAT does not rewrite the media header, and does not rewrite the backup device, unless INIT is also specified.
INIT NOINIT	Specifies whether the backup will be appended ( NOINIT ) to the device or overwrite existing backups ( INIT ).
MEDIADESCRIPTION	A text description of the entire media set.
MEDIANAME	This labels your media set. This name can then be referenced in a restore operation.
MEDIAPASSWORD	Specifies a password for the media set. If defined, the password must be supplied to perform a restore.
NAME	A name for the backup set.
NOSKIP SKIP	Indicates whether to read ( NOSKIP ) or ignore ( SKIP ) ANSI tape labels. The default is NOSKIP .
NOREWIND REWIND	Indicates whether to rewind the tape.
NOUNLOAD UNLOAD	Indicates whether to unload the tape.
RESTART	Saves time by restarting an interrupted backup at the point it was interrupted .
STATS	A percentage can be specified to gauge progress of the backup. If not specified, a message is displayed in 10% increments .

As promised , Listings 16.2, 16.3 and 16.4 provide some BACKUP DATABASE examples.

Listing 16.2 Sample Script for a Full Backup

 -- Backup to a permanent backup device. Don't  unload the tape. -- Provide a name and description for the backup USE MASTER BACKUP DATABASE Northwind TO NWbackup WITH NOUNLOAD, NAME = 'Northwind full database backup', DESCRIPTION = 'Full backup for Wednesday' 

Listing 16.3 Sample Script for a Differential Backup

 -- Backup to a temporary backup device -- Perform a differential backup USE MASTER BACKUP DATABASE Northwind TO DISK = 'D:\backup\NWtemp.bak' WITH DIFFERENTIAL 

Listing 16.4 Sample Script for a Full Backup to Multiple Devices

 -- Perform a striped backup to 3 permanent devices. -- Name the backup and provide a Description. -- Format the media and name the media set. USE MASTER BACKUP DATABASE Northwind TO NWStripe1, NWStripe2, NWStripe3 WITH NAME = 'Northwind full backup', DESCRIPTION = 'Striped to three devices', FORMAT,  MEDIANAME = 'NWSTRIPE' 

Transact-SQL provides a powerful command interface to back up SQL Server databases. The T-SQL commands can be saved as scripts to provide easily repeatable backup operations. In the next section you'll look at using Enterprise Manager to perform backups. Enterprise Manager uses a GUI interface to generate and schedule backups.

Backing Up Databases with SQL Enterprise Manager

With Enterprise Manager, Microsoft has made performing backups as easy as a couple of mouse clicks. You're probably thinking if it's that easy, it can't be good. Nothing could be farther from the truth. All the Enterprise Manager does is write the BACKUP DATABASE statement for you (without the typos), allows you to run the backup immediately, or saves it as a job and provides a scheduler to run it. After a backup has been saved in this way, you can edit the job at any time to change its parameters.

As with most things in Enterprise Manager there are several ways to perform a backup. If you are using Taskpad View (select View, Taskpad), hovering over the Maintenance arrow provides a drop-down list, from which you can select backup database. This screen is shown in Figure 16.2.

Figure 16.2. The Task Pad Maintenance List.

You can also select Backup Database from the Tools menu. Failing that, right-click the Backup icon under management. Or, my personal favorite, right-click the database you want to back up, select All Tasks from the pop-up menu, and then select Backup Database. Any of these actions will bring up the SQL Server Backup dialog box, which is what you are after. This dialog box is illustrated in Figure 16.3.

Figure 16.3. The Backup dialog box.

From this dialog box, you can back up databases (full and differential), files and filegroups, and transaction logs. You even have the option of creating backup devices if you haven't yet done so. With the exception of the BLOCKSIZE parameter, all BACKUP DATABASE options are presented. Scheduling backups is as easy as checking the Schedule box, and then selecting the ellipse beside the schedule window. Enter your desired schedule, and when you click OK to exit the dialog box, the backup will be saved as a job under SQL Server Agent. Figure 16.4 shows the location of scheduled backup jobs.

Figure 16.4. Scheduled backup jobs.

Double-clicking any of these backup jobs allows you to edit any of the parameters you saved when the job was created. You can even access the BACKUP DATABASE script.

I recommend you play a bit with this interface creating various types of backups and examining the scripts each generates. This is a surefire way to quickly become proficient at the syntax for backing up SQL Server databases.

NOTE

If you hate memorizing syntax and need a quick way to write backup scripts (without syntax errors), create a backup job with Enterprise Manager, edit the job it generates, and cut-and-paste the BACKUP DATABASE command into your script.