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.


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 


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 ]
    [ 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
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
BACKUP DATABASE Northwind TO DISK = 'D:\backup\NWtemp.bak'
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.
TO NWStripe1, NWStripe2, NWStripe3
NAME = 'Northwind full backup',
DESCRIPTION = 'Striped to three devices',

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.


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.

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features