You can perform backup operations using the following:
- Transact-SQL statements
- SQL Server Management Studio
All types of backup operations can be executed using two Transact-SQL statements:
BACKUP DATABASEBACKUP LOG
Before these two Transact-SQL statements are described, the existing types of backup devices will be explained.
Types of Backup Devices
The Database Engine allows you to back up databases, transaction logs, and files to the following backup devices:
- Disk
- Tape
Note – There is also another form of backup device called a network share. I will not describe it separately because it is simply a special form of a disk drive that specifies a network drive to use for backups.
Disk files are the most common media used for storing backups. Disk backup devices can be located on a server’s local hard disk or on a remote disk on a shared network resource. The Database Engine allows you to append a new backup to a file that already contains backups from the same or different databases. By appending a new backup set to existing media, the previous contents of the media remain intact, and the new backup is written after the end of the last backup on the media. (The backup set includes all stored data of the object you chose to back up.) By default, the Database Engine always appends new backups to disk files.
Caution – Do not back up to a file on the same physical disk where the database or its transaction log is stored! If the disk with the database crashes, the backup that is stored on the same disk will also be damaged.
Tape backup devices are generally used in the same way as disk devices. However, when you back up to a tape, the tape drive must be attached locally to the system. The advantage of tape devices relative to disk devices is their simple administration and operation.
Note – Always verify the backup on a network to ensure that there are no possible network errors.
BACKUP DATABASE Statement
The BACKUP DATABASE statement is used to perform a full database backup or a differential database backup. This statement has the following syntax:
BACKUP DATABASE {db_name | @variable}
TO device_list
[MIRROR TO device_list2]
[WITH | option_list]Code language: SQL (Structured Query Language) (sql)db_name is the name of the database that should be backed up. (The name of the database can also be supplied using a variable, @variable.) device_list specifies one or more device names, where the database backup will be stored. device_list can be a list of names of disk files or tapes. The syntax for a device is
{ logical_device_name | @logical_device_name_var }
| { DISK | TAPE } = { 'physical_device_name' | @physical_device_
name_var }Code language: SQL (Structured Query Language) (sql)where the device name can be either a logical name (or a variable) or a physical name beginning with the DISK or TAPE keyword. (The TAPE option will be removed in a future version of SQL Server.)
The MIRROR TO option indicates that the accompanying set of backup devices is a mirror within a mirrored media set. The backup devices must be identical in type and number to the devices specified in the TO clause. In a mirrored media set, all the backup devices must have the same properties. (See also the description of mirrored media in the section “Database Mirroring” later in this chapter.)
option_list comprises several options that can be specified for the different backup types. The most important options are the following:
DIFFERENTIALNOSKIP/SKIPNOINIT/INITNOFORMAT/FORMATUNLOAD/NOUNLOADMEDIANAME and MEDIADESCRIPTIONBLOCKSIZECOMPRESSION
The first option, DIFFERENTIAL, specifies a differential database backup. All other clauses in the list concern full database backups.
The SKIP option disables the backup set expiration and name checking, which is usually performed by BACKUP DATABASE to prevent overwrites of backup sets. The NOSKIP option, which is the default, instructs the BACKUP statement to check the expiration date and name of all backup sets before allowing them to be overwritten.
The INIT option is used to overwrite any existing data on the backup media. This option does not overwrite the media header, if one exists. If there is a backup that has not yet expired, the backup operation fails. In this case, use the combination of SKIP and INIT options to overwrite the backup device. The NOINIT option, which is the default, appends a backup to existing backups on the media.
The FORMAT option is used to write a header on all of the files (or tape volumes) that are used for a backup. Therefore, use this option to initialize a backup medium. When you use the FORMAT option to back up to a tape device, the INIT option and the SKIP option are implied. Similarly, the INIT option is implied if the FORMAT option is specified for a file device. NOFORMAT, which is the default, specifies that the backup operation processes the existing media header and backup sets on the media volumes.
The UNLOAD and NOUNLOAD options are performed only if the backup medium is a tape device. The UNLOAD option, which is the default, specifies that the tape is automatically rewound and unloaded from the tape device after the backup is completed. Use the NOUNLOAD option if the database system should not rewind (and unload) the tape from the tape device automatically.
MEDIADESCRIPTION and MEDIANAME specify the description and the name of the media set, respectively. The BLOCKSIZE option specifies the physical block size, in bytes. The supported sizes are 512, 1024, 2048, 4096, 8192, 16384, 32768, and 65536 (64KB) bytes. The default is 65536 bytes for tape devices and 512 bytes otherwise.
The Database Engine supports backup compression. To specify backup compression, use the COMPRESSION option of the BACKUP DATABASE statement. Example 16.1 backs up the sample database and compresses the backup file.
Example 16.1
USE master;
BACKUP DATABASE sample
TO DISK = 'C:\sample.bak'
WITH INIT, COMPRESSION;Code language: SQL (Structured Query Language) (sql)Note If you get the “Access Denied” error for the C: \directory, change the storage location of the sample.bak file to another directory (tmp, for instance).
If you want to know whether the particular backup file is compressed, use the output of the RESTORE HEADERONLY statement, which is described later in this chapter.
BACKUP LOG Statement
The BACKUP LOG statement is used to perform a backup of the transaction log. This statement has the following syntax:
BACKUP LOG {db_name | @variable}
TO device_list
[MIRROR TO device_list2]
[WITH option_list]Code language: SQL (Structured Query Language) (sql)db_name, @variable, device_list, and device_list2 have the same meanings as the parameters with the same names in the BACKUP DATABASE statement. option_list has the same options as the BACKUP DATABASE statement and also supports the specific log options NO_TRUNCATE, NORECOVERY, and STANDBY.
You should use the NO_TRUNCATE option if you want to back up the transaction log without truncating it—that is, this option does not clear the committed transactions in the log. After the execution of this option, the system writes all recent database activities in the transaction log. Therefore, the NO_TRUNCATE option allows you to recover data right up to the point of the database failure.
The NORECOVERY option backs up the tail of the log and leaves the database in the restoring state. NORECOVERY is useful when failing over to a secondary database or when saving the tail of the log before a restore operation. The STANDBY option backs up the tail of the log and leaves the database in a read-only and standby state. (The restore operation and the standby state are explained later in this chapter.)
