Search
Home
Articles
Backup
Books
Certification
FAQ
Products
Replication
Scripts
Seminars
Training
TSQL
MSDN Fourms
Philippine SSUG
Fort Worth SSUG
Oklahoma City SSDG
Resume
MHS Enterprises
BlowFrog Software
FilAm Software
AcrylicAcetate.com
Bargain Humidors
Western Humidor

Backup Overview 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

Backup transaction log to a tape device and retain the existing log

exec sp_addumpdevice 'tape', 'tapebackup', '\\.\tape0'

backup log pubs to tapebackup with no_truncate

Truncate transaction log

backup log pubs with truncate_only

Differential backup

A differential backup is a new method introduced in SQL Server 7.0. Differential backups can be one of the most powerful and time saving features you can implement in a backuip plan. A differential backup will create a baclup of all data that has changed since the last full database backup. This specifies the only restriction on a differential backup, a full database backup must be done first. Differential database backups will be very useful, especially for large databases, because they provide a more comprehensive intermediate portrait of the data than a transaction log backup. You can essentially look at a differential backup as the accumulation of all transaction log backups that could have been taken since the last full backup.

You would normally perform a full backup, differential backups on a more frequent basis, and transaction log backps between each differential backup. When restoring a backup plan like this, you would restore the last full backup, restore the last differential backup, restore any transactions log since the last differential backup. Since all of the transaction logs previous to the last differential are included in that differential backup, they do not need to be restored and are only useful if you want to return the database to a previous point in time.

A diffferential backup follows the same syntax as a full database backup with the exeception of adding an additional option.

backup database <database> to <device> with differential, <options>

Differential backup to an explicit disk device and begin a new backup set.

EXEC sp_addumpdevice 'disk', 'diskbackup', 'c:\mssql7\backup\diskbackup.bak'

backup database pubs to diskbackup with differenntial, format

Differential backup to an undefined network disk device and overwrite the existing backups

backup database pubs to disk = ‘\\myserver\dbbackups\pubs.bak’ with differential, init

Differential backup to a tape device and leave the tape in the drive

exec sp_addumpdevice 'tape', 'tapebackup', '\\.\tape0'

backup database pubs to tapebackup with differential, nounload

Differential backup multiple databases to a tape device, format the tape, and leave the tape in the drive for all but the last database

exec sp_addumpdevice 'tape', 'tapebackup', '\\.\tape0'

backup database master to tapebackup with differential, format, nounload

backup database pubs to tapebackup with differential, noformat, nounload

backup database msdb to tapebackup with differential, noformat, unload

Backup Overview 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

Michael R. Hotek

All content on this site, except where noted, represents an original work of Michael R. Hotek and is protected by applicable copyright laws. The SQL Server FAQ is the sole work of Neil Pike. No page, portion of a page, or download may be used for commercial purposes in whole or in part without the express, written permission of the applicable author.