You can perform backup operations using the following:
- Transact-SQL statements
- SQL Server Management Studio
Each of these backup methods is described in the following sections.
1. Backing Up Using Transact-SQL Statements
All types of backup operations can be executed using two Transact-SQL statements:
- BACKUP DATABASE
- BACKUP LOG
Before these two Transact-SQL statements are described, the existing types of backup devices will be explained.
1.1. 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.
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 You can use third-party backup utilities, too. They provide a variety of options beyond what is delivered by the Database Engine.
1.2. 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]
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| URL}= {‘physical_device_name’| @physical_device_name_var}
where the device name can be either a logical name (or a variable) or a physical name beginning with the DISK, TAPE, or URL keyword. The meaning of the first two options is obvious. The URL format is used for creating backups to the Microsoft Azure storage service. (The TAPE option will be removed in a future version of the Database Engine.)
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:
- DIFFERENTIAL
- NOSKIP/SKIP
- NOINIT/INIT
- NOFORMAT/FORMAT
- UNLOAD/NOUNLOAD
- MEDIANAME and MEDIADESCRIPTION
- BLOCKSIZE
- COMPRESSION
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 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:\temp\sample.bak’
WITH INIT, COMPRESSION;
If you want to know whether the particular backup file is compressed, view the output of the RESTORE HEADERONLY statement, which is described later in this chapter.
1.3. 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]
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.)
2. Backing Up Using SQL Server Management Studio
Before you can perform a database or transaction log backup, you must specify (or create) backup devices. Management Studio allows you to create disk devices and tape devices in a similar manner. In both cases, expand the server, expand Server Objects, right-click Backup Devices, and choose New Backup Device. In the Backup Device dialog box, enter the name of either the disk device (if you clicked File) or the tape device (if you clicked Tape). In the former case, you can click the … button on the right side of the field to display existing backup device locations. In the latter case, if Tape cannot be activated, then no tape devices exist on the local computer. Click OK.
After you specify backup devices, you can do a database backup. Expand the server, expand Databases, right-click the database, and choose Tasks | Back Up. The Back Up Database dialog box appears (see Figure 16-1). On the General page of the dialog box, choose the backup type in the Backup Type drop-down list (Full, Differential, or Transaction Log). In the same dialog box, you can choose an expiration date for the backup.
In the Destination frame, select an existing device by clicking Add. (The Remove button allows you to remove one or more backup devices from the list of devices to be used.)
On the Media Options page (see Figure 16-2), to append to an existing backup on the selected device, click the Append to the Existing Backup Set radio button. Choosing the Overwrite All Existing Backup Sets radio button in the same frame overwrites any existing backups on the selected backup device.
For verification of the database backup, check the Verify Backup When Finished check box in the Reliability frame. On the Media Options page, you can also choose to back up to a new media set by clicking the Back Up to a New Media Set, and Erase All Existing Backup Sets radio button and then entering the media set name and description.
For creation and verification of a differential database backup or transaction log backup, follow the same steps, but choose the corresponding backup type in the Backup Type field on the General page.
After you have chosen all your options, click OK. The database or the transaction log is then backed up. You can display the name, physical location, and the type of the backup devices by selecting the server, expanding the Server Objects folder, expanding the Backup Devices folder, and then selecting the particular file.
2.1.Scheduling Backups with SQL Server Management Studio
A well-planned timetable for the scheduling of backup operations will help you avoid system shortages when users are working. Management Studio supports this planning by offering an easy-to-use graphical interface for scheduling backups. Scheduling backups using Management Studio is explained in detail in Chapter 17.
3. Determining Which Databases to Back Up
The following databases should be backed up regularly:
- The master database
- All production databases
3.1. Backing Up the master Database
The master database is the most important system database because it contains information about all of the databases in the system. Therefore, you should back up the master database on a regular basis. Additionally, you should back up the master database anytime certain statements and stored procedures are executed, because the Database Engine modifies the master database automatically.
NOTE You can perform only full database backups of the master database. (The system does not support differential, transaction log, and file backups for the master database.)
Many activities cause the modification of the master database. Some of them are listed here:
- The creation, alteration, and removal of a database
- The alteration of the transaction log
NOTE Without a backup of the master database, you must completely rebuild all system databases, because if the master database is damaged, all references to the existing user-defined databases are lost.
3.2. Backing Up Production Databases
You should back up each production database on a regular basis. Additionally, you should back up any production database when the following activities are executed:
- After creating it
- After creating indices
- After clearing the transaction log
- After performing nonlogged operations
Always make a full database backup after it has been created, in case a failure occurs between the creation of the database and the first regular database backup. Remember that backups of the transaction log cannot be applied without a full database backup.
Backing up the database after creation of one or more indices saves time during the restore process, because the index structures are backed up together with the data. Backing up the transaction log after creation of indices does not save time during the restore process at all, because the transaction log records only the fact that an index was created (and does not record the modified index structure).
Backing up the database after clearing the transaction log is necessary because the transaction log no longer contains a record of database activity, which is used to recover the database. All operations that are not recorded to the transaction log are called nonlogged operations. Therefore, all changes made by these operations cannot be restored during the recovery process.
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.