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.
 
 
Search WWH ::




Custom Search