--- PDF PAGE 438 ---
424
On the Media Option page, you can specify if you want to use an existing media 
set or create a new one. If you choose to use an existing media set, then specify if you 
want to overwrite the content of the media set or append a new backup set to the 
media set. If you choose to create a new media set, then you can specify the name 
and, optionally, a description for the media set. If you use an existing media set, you 
can verify the date and time that the media set and backup set expire. These checks 
may cause the backup set to be appended to the existing backup device, instead of 
overwriting the backup sets.
Under the Reliability section, specify if the backup should be verified after 
completion. This is usually a good idea, especially if you are backing up to a URL, since 
backups across the network are prone to corruption. Choosing the Perform Checksum 
Before Writing To Media option causes the page checksum of each page of the database 
to be verified before it is written to the backup device. This causes the backup operation 
to use additional resources, but if you are not running DBCC CHECKDB as frequently 
as you take backups, then this option may give you an early warning of any database 
corruption. (Please see Chapter 9 for more details.) The Continue On Error option 
causes the backup to continue, even if a bad checksum is discovered during verification 
of the pages.
On the Backup Options page, you are able to set the expiration date of the backup 
set as well as select if you want the backup set to be compressed or encrypted. For 
compression, you can choose to use the instance default setting, or you can override this 
setting by specifically choosing to compress, or not compress, the backup.
If you choose to encrypt the backup, then you need to select a preexisting certificate. 
(You can find details of how to create a certificate in Chapter 11.) You then need to select 
the algorithm that you wish to use to encrypt the backup. Available algorithms in SQL 
Server 2019 are AES 128, AES 192, AES 256, or 3DES (Triple_DES_3Key). You should 
usually select an AES algorithm, because support for 3DES will be removed in a future 
version of SQL Server.
Backing Up via T-SQL
When you back up a database or log via T-SQL, you can specify many arguments. These 
can be broken down into the following categories:
•	
Backup options (described in Table 12-1).
•	
WITH options (described in Table 12-2).
Chapter 12  Backups and Restores

--- PDF PAGE 439 ---
425
•	
Backup set options (described in Table 12-3).
•	
Media set options (described in Table 12-4).
•	
Error management options (described in Table 12-5).
•	
Tape options have been deprecated for many versions and should 
not be used. Therefore, details of tape options are omitted from this 
chapter.
•	
Log-specific options (described in Table 12-6).
•	
Miscellaneous options (described in Table 12-7).
Table 12-1.  Backup Options
Argument
Description
DATABASE/LOG
Specify DATABASE to perform a full or differential backup. Specify 
LOG to perform a transaction log backup.
database_name
The name of the database to perform the backup operation against. 
Can also be a variable containing the name of the database.
file_or_filegroup
A comma-separated list of files or filegroups to back up, in the 
format FILE = logical file name or FILEGROUP = Logical 
filegroup name.
READ_WRITE_ 
FILEGROUPS
Performs a partial backup by backing up all read/write filegroups. 
Optionally, use comma-separated FILEGROUP = syntax after 
this clause to add read-­only filegroups.
TO
A comma-separated list of backup devices to stripe the backup set 
over, with the syntax DISK = physical device,  
TAPE = physical device, or URL = physical device.
MIRROR TO
A comma-separated list of backup devices to which to mirror the 
backup set. If the MIRROR TO clause is used, the number of backup 
devices specified must equal the number of backup devices specified 
in the TO clause.
Chapter 12  Backups and Restores

--- PDF PAGE 438 ---
424
On the Media Option page, you can specify if you want to use an existing media 
set or create a new one. If you choose to use an existing media set, then specify if you 
want to overwrite the content of the media set or append a new backup set to the 
media set. If you choose to create a new media set, then you can specify the name 
and, optionally, a description for the media set. If you use an existing media set, you 
can verify the date and time that the media set and backup set expire. These checks 
may cause the backup set to be appended to the existing backup device, instead of 
overwriting the backup sets.
Under the Reliability section, specify if the backup should be verified after 
completion. This is usually a good idea, especially if you are backing up to a URL, since 
backups across the network are prone to corruption. Choosing the Perform Checksum 
Before Writing To Media option causes the page checksum of each page of the database 
to be verified before it is written to the backup device. This causes the backup operation 
to use additional resources, but if you are not running DBCC CHECKDB as frequently 
as you take backups, then this option may give you an early warning of any database 
corruption. (Please see Chapter 9 for more details.) The Continue On Error option 
causes the backup to continue, even if a bad checksum is discovered during verification 
of the pages.
On the Backup Options page, you are able to set the expiration date of the backup 
set as well as select if you want the backup set to be compressed or encrypted. For 
compression, you can choose to use the instance default setting, or you can override this 
setting by specifically choosing to compress, or not compress, the backup.
If you choose to encrypt the backup, then you need to select a preexisting certificate. 
(You can find details of how to create a certificate in Chapter 11.) You then need to select 
the algorithm that you wish to use to encrypt the backup. Available algorithms in SQL 
Server 2019 are AES 128, AES 192, AES 256, or 3DES (Triple_DES_3Key). You should 
usually select an AES algorithm, because support for 3DES will be removed in a future 
version of SQL Server.
Backing Up via T-SQL
When you back up a database or log via T-SQL, you can specify many arguments. These 
can be broken down into the following categories:
•	
Backup options (described in Table 12-1).
•	
WITH options (described in Table 12-2).
Chapter 12  Backups and Restores

--- PDF PAGE 440 ---
426
Table 12-2.  WITH Options
Argument
Description
CREDENTIAL
Use when backing up to a Windows Azure Blob.
DIFFERENTIAL
Specifies that a differential backup should be taken. If this option is 
omitted, then a full backup is taken.
ENCRYPTION
Specifies the algorithm to use for the encryption of the backup. If the 
backup is not to be encrypted, then NO_ENCRYPTION can be specified, 
which is the default option. Backup encryption is only available in 
Enterprise, Business Intelligence, and Standard Editions of SQL Server.
encryptor_name
The name of the encryptor in the format SERVER CERTIFICATE = 
encryptor name or SERVER ASYMMETRIC KEY = encryptor name.
Table 12-3.  Backup Set Options
Argument
Description
COPY_ONLY
Specifies that a copy_only backup of the database or log should be taken. 
This option is ignored if you perform a differential backup.
COMPRESSION/NO 
COMPRESSION
By default, SQL Server decides if the backup should be compressed 
based on the instance-level setting. (These can be viewed in sys.
configurations.) You can override this setting, however, by specifying 
COMPRESSION or NO COMPRESSION, as appropriate. Backup compression 
is only available in Enterprise, Business Intelligence, and Standard Editions 
of SQL Server.
NAME
Specifies a name for the backup set.
DESCRIPTION
Adds a description to the backup set.
EXPIRYDATE/
RETAINEDDAYS
Use EXPIRYDATE = datetime to specify a precise date and time that 
the backup set expires. After this date, the backup set can be overwritten. 
Specify RETAINDAYS = int to specify a number of days before the backup 
set expires.
Chapter 12  Backups and Restores

--- PDF PAGE 439 ---
425
•	
Backup set options (described in Table 12-3).
•	
Media set options (described in Table 12-4).
•	
Error management options (described in Table 12-5).
•	
Tape options have been deprecated for many versions and should 
not be used. Therefore, details of tape options are omitted from this 
chapter.
•	
Log-specific options (described in Table 12-6).
•	
Miscellaneous options (described in Table 12-7).
Table 12-1.  Backup Options
Argument
Description
DATABASE/LOG
Specify DATABASE to perform a full or differential backup. Specify 
LOG to perform a transaction log backup.
database_name
The name of the database to perform the backup operation against. 
Can also be a variable containing the name of the database.
file_or_filegroup
A comma-separated list of files or filegroups to back up, in the 
format FILE = logical file name or FILEGROUP = Logical 
filegroup name.
READ_WRITE_ 
FILEGROUPS
Performs a partial backup by backing up all read/write filegroups. 
Optionally, use comma-separated FILEGROUP = syntax after 
this clause to add read-­only filegroups.
TO
A comma-separated list of backup devices to stripe the backup set 
over, with the syntax DISK = physical device,  
TAPE = physical device, or URL = physical device.
MIRROR TO
A comma-separated list of backup devices to which to mirror the 
backup set. If the MIRROR TO clause is used, the number of backup 
devices specified must equal the number of backup devices specified 
in the TO clause.
Chapter 12  Backups and Restores

--- PDF PAGE 440 ---
426
Table 12-2.  WITH Options
Argument
Description
CREDENTIAL
Use when backing up to a Windows Azure Blob.
DIFFERENTIAL
Specifies that a differential backup should be taken. If this option is 
omitted, then a full backup is taken.
ENCRYPTION
Specifies the algorithm to use for the encryption of the backup. If the 
backup is not to be encrypted, then NO_ENCRYPTION can be specified, 
which is the default option. Backup encryption is only available in 
Enterprise, Business Intelligence, and Standard Editions of SQL Server.
encryptor_name
The name of the encryptor in the format SERVER CERTIFICATE = 
encryptor name or SERVER ASYMMETRIC KEY = encryptor name.
Table 12-3.  Backup Set Options
Argument
Description
COPY_ONLY
Specifies that a copy_only backup of the database or log should be taken. 
This option is ignored if you perform a differential backup.
COMPRESSION/NO 
COMPRESSION
By default, SQL Server decides if the backup should be compressed 
based on the instance-level setting. (These can be viewed in sys.
configurations.) You can override this setting, however, by specifying 
COMPRESSION or NO COMPRESSION, as appropriate. Backup compression 
is only available in Enterprise, Business Intelligence, and Standard Editions 
of SQL Server.
NAME
Specifies a name for the backup set.
DESCRIPTION
Adds a description to the backup set.
EXPIRYDATE/
RETAINEDDAYS
Use EXPIRYDATE = datetime to specify a precise date and time that 
the backup set expires. After this date, the backup set can be overwritten. 
Specify RETAINDAYS = int to specify a number of days before the backup 
set expires.
Chapter 12  Backups and Restores

--- PDF PAGE 439 ---
425
•	
Backup set options (described in Table 12-3).
•	
Media set options (described in Table 12-4).
•	
Error management options (described in Table 12-5).
•	
Tape options have been deprecated for many versions and should 
not be used. Therefore, details of tape options are omitted from this 
chapter.
•	
Log-specific options (described in Table 12-6).
•	
Miscellaneous options (described in Table 12-7).
Table 12-1.  Backup Options
Argument
Description
DATABASE/LOG
Specify DATABASE to perform a full or differential backup. Specify 
LOG to perform a transaction log backup.
database_name
The name of the database to perform the backup operation against. 
Can also be a variable containing the name of the database.
file_or_filegroup
A comma-separated list of files or filegroups to back up, in the 
format FILE = logical file name or FILEGROUP = Logical 
filegroup name.
READ_WRITE_ 
FILEGROUPS
Performs a partial backup by backing up all read/write filegroups. 
Optionally, use comma-separated FILEGROUP = syntax after 
this clause to add read-­only filegroups.
TO
A comma-separated list of backup devices to stripe the backup set 
over, with the syntax DISK = physical device,  
TAPE = physical device, or URL = physical device.
MIRROR TO
A comma-separated list of backup devices to which to mirror the 
backup set. If the MIRROR TO clause is used, the number of backup 
devices specified must equal the number of backup devices specified 
in the TO clause.
Chapter 12  Backups and Restores

--- PDF PAGE 441 ---
427
Table 12-4.  Media Set Options
Argument
Description
INIT/NOINIT
INIT attempts to overwrite the existing backup sets in the media set 
but leaves the media header intact. It first checks the name and expiry 
date of the backup set, unless SKIP is specified. NOINIT appends the 
backup set to the media set, which is the default behavior.
SKIP/NOSKIP
SKIP causes the INIT checks of backup set name and expiration date 
to be skipped. NOSKIP enforces them, which is the default behavior.
FORMAT/NOFORMAT
FORMAT causes the media header to be overwritten, leaving any backup 
sets within the media set unusable. This essentially creates a new media 
set. The backup set names and expiry dates are not checked. NOFORMAT 
preserves the existing media header, which is the default behavior.
MEDIANAME
Specifies the name of the media set.
MEDIADESCRIPTION
Adds a description of the media set.
BLOCKSIZE
Specifies the block size in bytes that will be used for the backup.  
The BLOCKSIZE defaults to 512 for disk and URL and defaults to 
65,536 for tape.
Table 12-5.  Error Management Options
Argument
Description
CHECKSUM/NO_CHECKSUM
Specifies if the page checksum of each page should be validated 
before the page is written to the media set.
CONTINUE_AFTER_ERROR/
STOP_ON_ERROR
STOP_ON_ERROR is the default behavior and causes the backup 
to fail if a bad checksum is discovered when verifying the page 
checksum. CONTINUE_AFTER_ERROR allows the backup to 
continue if a bad checksum is discovered.
Chapter 12  Backups and Restores

--- PDF PAGE 439 ---
425
•	
Backup set options (described in Table 12-3).
•	
Media set options (described in Table 12-4).
•	
Error management options (described in Table 12-5).
•	
Tape options have been deprecated for many versions and should 
not be used. Therefore, details of tape options are omitted from this 
chapter.
•	
Log-specific options (described in Table 12-6).
•	
Miscellaneous options (described in Table 12-7).
Table 12-1.  Backup Options
Argument
Description
DATABASE/LOG
Specify DATABASE to perform a full or differential backup. Specify 
LOG to perform a transaction log backup.
database_name
The name of the database to perform the backup operation against. 
Can also be a variable containing the name of the database.
file_or_filegroup
A comma-separated list of files or filegroups to back up, in the 
format FILE = logical file name or FILEGROUP = Logical 
filegroup name.
READ_WRITE_ 
FILEGROUPS
Performs a partial backup by backing up all read/write filegroups. 
Optionally, use comma-separated FILEGROUP = syntax after 
this clause to add read-­only filegroups.
TO
A comma-separated list of backup devices to stripe the backup set 
over, with the syntax DISK = physical device,  
TAPE = physical device, or URL = physical device.
MIRROR TO
A comma-separated list of backup devices to which to mirror the 
backup set. If the MIRROR TO clause is used, the number of backup 
devices specified must equal the number of backup devices specified 
in the TO clause.
Chapter 12  Backups and Restores

--- PDF PAGE 441 ---
427
Table 12-4.  Media Set Options
Argument
Description
INIT/NOINIT
INIT attempts to overwrite the existing backup sets in the media set 
but leaves the media header intact. It first checks the name and expiry 
date of the backup set, unless SKIP is specified. NOINIT appends the 
backup set to the media set, which is the default behavior.
SKIP/NOSKIP
SKIP causes the INIT checks of backup set name and expiration date 
to be skipped. NOSKIP enforces them, which is the default behavior.
FORMAT/NOFORMAT
FORMAT causes the media header to be overwritten, leaving any backup 
sets within the media set unusable. This essentially creates a new media 
set. The backup set names and expiry dates are not checked. NOFORMAT 
preserves the existing media header, which is the default behavior.
MEDIANAME
Specifies the name of the media set.
MEDIADESCRIPTION
Adds a description of the media set.
BLOCKSIZE
Specifies the block size in bytes that will be used for the backup.  
The BLOCKSIZE defaults to 512 for disk and URL and defaults to 
65,536 for tape.
Table 12-5.  Error Management Options
Argument
Description
CHECKSUM/NO_CHECKSUM
Specifies if the page checksum of each page should be validated 
before the page is written to the media set.
CONTINUE_AFTER_ERROR/
STOP_ON_ERROR
STOP_ON_ERROR is the default behavior and causes the backup 
to fail if a bad checksum is discovered when verifying the page 
checksum. CONTINUE_AFTER_ERROR allows the backup to 
continue if a bad checksum is discovered.
Chapter 12  Backups and Restores

--- PDF PAGE 439 ---
425
•	
Backup set options (described in Table 12-3).
•	
Media set options (described in Table 12-4).
•	
Error management options (described in Table 12-5).
•	
Tape options have been deprecated for many versions and should 
not be used. Therefore, details of tape options are omitted from this 
chapter.
•	
Log-specific options (described in Table 12-6).
•	
Miscellaneous options (described in Table 12-7).
Table 12-1.  Backup Options
Argument
Description
DATABASE/LOG
Specify DATABASE to perform a full or differential backup. Specify 
LOG to perform a transaction log backup.
database_name
The name of the database to perform the backup operation against. 
Can also be a variable containing the name of the database.
file_or_filegroup
A comma-separated list of files or filegroups to back up, in the 
format FILE = logical file name or FILEGROUP = Logical 
filegroup name.
READ_WRITE_ 
FILEGROUPS
Performs a partial backup by backing up all read/write filegroups. 
Optionally, use comma-separated FILEGROUP = syntax after 
this clause to add read-­only filegroups.
TO
A comma-separated list of backup devices to stripe the backup set 
over, with the syntax DISK = physical device,  
TAPE = physical device, or URL = physical device.
MIRROR TO
A comma-separated list of backup devices to which to mirror the 
backup set. If the MIRROR TO clause is used, the number of backup 
devices specified must equal the number of backup devices specified 
in the TO clause.
Chapter 12  Backups and Restores

--- PDF PAGE 442 ---
428
Table 12-6.  Log-Specific Options
Argument
Description
NORECOVERY/
STANDBY
NORECOVERY causes the database to be left in a restoring state when the 
backup completes, making it inaccessible to users. STANDBY leaves the 
database in a read-only state when the backup completes. STANDBY requires 
that you specify the path and file name of the transaction undo file, so it 
should be used with the format STANDBY = transaction_undo_file. 
If neither option is specified, then the database remains online when the 
backup completes.
NO_TRUNCATE
Specifies that the log backup should be attempted, even if the database 
is not in a healthy state. It also does not attempt to truncate an inactive 
portion of the log. Taking a tail-log backup involves backing up the log with 
NORECOVERY and NO_TRUNCATE specified.
Table 12-7.  Miscellaneous Options
Argument
Description
BUFFERCOUNT
The total number of IO buffers used for the backup operation.
MAXTRANSFERSIZE
The largest possible unit of transfer between SQL Server and the backup 
media, specified in bytes.
STATS
Specifies how often progress messages should be displayed. The default 
is to display a progress message in 10% increments.
To perform the full database backup of the Chapter12 database, which we 
demonstrate through the GUI, we can use the command in Listing 12-4. Before running 
this script, modify the path of the backup device to meet your system’s configuration.
Listing 12-4.  Performing a Full Backup
BACKUP DATABASE Chapter12
        TO  DISK = 'H:\MSSQL\Backup\Chapter12.bak'
        WITH  RETAINDAYS = 90
        , FORMAT
        , INIT
Chapter 12  Backups and Restores

--- PDF PAGE 439 ---
425
•	
Backup set options (described in Table 12-3).
•	
Media set options (described in Table 12-4).
•	
Error management options (described in Table 12-5).
•	
Tape options have been deprecated for many versions and should 
not be used. Therefore, details of tape options are omitted from this 
chapter.
•	
Log-specific options (described in Table 12-6).
•	
Miscellaneous options (described in Table 12-7).
Table 12-1.  Backup Options
Argument
Description
DATABASE/LOG
Specify DATABASE to perform a full or differential backup. Specify 
LOG to perform a transaction log backup.
database_name
The name of the database to perform the backup operation against. 
Can also be a variable containing the name of the database.
file_or_filegroup
A comma-separated list of files or filegroups to back up, in the 
format FILE = logical file name or FILEGROUP = Logical 
filegroup name.
READ_WRITE_ 
FILEGROUPS
Performs a partial backup by backing up all read/write filegroups. 
Optionally, use comma-separated FILEGROUP = syntax after 
this clause to add read-­only filegroups.
TO
A comma-separated list of backup devices to stripe the backup set 
over, with the syntax DISK = physical device,  
TAPE = physical device, or URL = physical device.
MIRROR TO
A comma-separated list of backup devices to which to mirror the 
backup set. If the MIRROR TO clause is used, the number of backup 
devices specified must equal the number of backup devices specified 
in the TO clause.
Chapter 12  Backups and Restores

--- PDF PAGE 442 ---
428
Table 12-6.  Log-Specific Options
Argument
Description
NORECOVERY/
STANDBY
NORECOVERY causes the database to be left in a restoring state when the 
backup completes, making it inaccessible to users. STANDBY leaves the 
database in a read-only state when the backup completes. STANDBY requires 
that you specify the path and file name of the transaction undo file, so it 
should be used with the format STANDBY = transaction_undo_file. 
If neither option is specified, then the database remains online when the 
backup completes.
NO_TRUNCATE
Specifies that the log backup should be attempted, even if the database 
is not in a healthy state. It also does not attempt to truncate an inactive 
portion of the log. Taking a tail-log backup involves backing up the log with 
NORECOVERY and NO_TRUNCATE specified.
Table 12-7.  Miscellaneous Options
Argument
Description
BUFFERCOUNT
The total number of IO buffers used for the backup operation.
MAXTRANSFERSIZE
The largest possible unit of transfer between SQL Server and the backup 
media, specified in bytes.
STATS
Specifies how often progress messages should be displayed. The default 
is to display a progress message in 10% increments.
To perform the full database backup of the Chapter12 database, which we 
demonstrate through the GUI, we can use the command in Listing 12-4. Before running 
this script, modify the path of the backup device to meet your system’s configuration.
Listing 12-4.  Performing a Full Backup
BACKUP DATABASE Chapter12
        TO  DISK = 'H:\MSSQL\Backup\Chapter12.bak'
        WITH  RETAINDAYS = 90
        , FORMAT
        , INIT
Chapter 12  Backups and Restores