Database Reference
In-Depth Information
Backing Up the Database
You can backup and restore the database using Management Studio UI, T-SQL, and PowerShell, as well as with
third-party tools. In this chapter, we will focus on the T-SQL implementation.
Listing 30-1 shows the T-SQL statements that perform a full database backup with a disk as the destination.
Listing 30-1.
Performing a full database backup
BACKUP DATABASE [OrderEntryDb]
TO DISK = N'e:\backups\OrderEntry.bak'
WITH FORMAT, INIT,
NAME = N'OrderEntryDb-Full Database Backup',
STATS = 5, CHECKSUM, COMPRESSION
SQL Server allows you to store multiple backups in a single file. This approach, however, has its pros and cons.
While it reduces the number of files on the disk and simplifies their management, you should be extremely careful
during backup operations to make sure that it does not override existing backups. Moreover, you should design your
backup placement in a way that reduces the amount of data that needs to be copied over the network in case of disaster.
■
Do not store backups from the different log chains in the same file. Moreover, do not store differential backups
together with other redundant differential and/or log backups. this reduces the size of the backup file, and the time it
takes to copy the file over a network in case of disaster.
Tip
FORMAT
and
INIT
options tell SQL Server to override all existing backups in the backup file.
The
CHECKSUM
option forces SQL Server to validate the checksum on the data pages and generate a checksum
of the backup file. This helps to validate that the data pages have not been corrupted by the I/O subsystem after they
were saved to disk. It is also worth mentioning that this option adds extra CPU load and reduces backup throughput.
■
you should not use
BACKUP WITH CHECKSUM
as a replacement for a regular database consistency check with
the
DBCC CHECKDB
command. it does not test the integrity of the database objects and allocation map pages, nor does it
test pages that do not have a
CHECKSUM
generated.
Note
Finally, the
COMPRESSION
option forces SQL Server to compress the backup. Backup compression can significantly
reduce the size of the backup file, although it uses more CPU resources during the backup and restore processes.
It is recommended that you use backup compression unless the system is heavily CPU-bound or the database is
encrypted. In the latter case, backup compression does not introduce any space saving.
Backup compression is available in the Enterprise and Standard editions of SQL Server 2008R2 and above and
in the Enterprise edition of SQL Server 2008. It is worth mentioning that every edition of SQL Server can restore
compressed backup.
■
you can look at all of the available
BACKUP
command options at
http://technet.microsoft.com/en-us/
Note
