Database Reference
In-Depth Information
You can perform a differential backup using the
DIFFERENTIAL
option, as shown in Listing 30-2.
Listing 30-2.
Performing a differential database backup
BACKUP DATABASE [OrderEntryDb]
TO DISK = N'e:\backups\OrderEntry.bak'
WITH
DIFFERENTIAL
, NOFORMAT, NOINIT,
NAME = N'OrderEntryDb-Differential Database Backup',
STATS = 5, CHECKSUM, COMPRESSION
Now our backup file
OrderEntry.bak
has two backups: one FULL and another DIFFERENTIAL. Finally, Listing 30-3
shows you how to perform a transaction log backup by placing it into another file.
Listing 30-3.
Performing a transaction log backup
BACKUP LOG [OrderEntryDb]
TO DISK = N'e:\backups\OrderEntry.trn'
WITH FORMAT, INIT,
NAME = N'OrderEntryDb-Transaction Log Backup',
STATS = 5, CHECKSUM, COMPRESSION
■
you should have
BACKUP DATABASE
and
BACKUP LOG
permissions granted to perform database and log
backups. By default, those permissions are granted to the members of the
sysadmin
server role,
db_owner
, and
db_backupoperator
database roles. Moreover, the sQL server startup account should have adequate permissions
to write a backup file to the designated location.
Note
You can specify multiple destination backup files and allow SQL Server to stripe backup across all of them.
This can improve the performance of backup and subsequent restore operations if the I/O performance of the backup
drive becomes a bottleneck.
The
COPY_ONLY
option allows you to perform a backup without breaking the log chain. One possible use-case for
such option is the situation where you need to bring the copy of the database to a development environment.
SQL Server stores the information about every backup and restore operation on a server instance in the set of the
tables defined in the
msdb
database. A description of those tables is outside of the scope of this topic. You can read the
Books Online article “Backup History and Header Information,” at:
http://msdn.microsoft.com/en-us/library/
ms188653.aspx
for more details.
Finally, SQL Server writes information about every backup to the error log file. This could quickly balloon the size
of the log file if backups are running frequently. You can disable this behavior with Trace Flag
T3226
. This makes error
logs more compact at the cost of requiring a query against
msdb
to obtain backup history.
Restoring the Database
You can restore a database with the
RESTORE DATABASE
command. You can see an example of this command in action
in Listing 30-4. It restores the
OrderEntryDB
database at a new destination (the
MOVE
option controls this), and it
applies differential and transaction log backups after that.