|
|
|
|
|
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 This boils down to the following command:Backup to an explicit disk device and begin a new backup set.
Backup to an undefined network disk device and overwrite the existing backups backup database pubs file = ‘pubsdata_1’ to disk = ‘\\myserver\dbbackups\pubs.bak’ with init Backup to a tape device and leave the tape in the drive exec sp_addumpdevice 'tape', 'tapebackup', '\\.\tape0' backup database pubs file = ‘pubsdata_1’ to tapebackup with nounload Backup multiple filegroups 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 pubs filegroup = ‘authordata’, filegroup = ‘authorindexes’, filegroup = ‘authorimages’ to tapebackup with noformat, nounload Transaction log backup Transaction log backups are one of yoru biggest insurance policies. Backups are planned in order to minimize data loss in the event of a failure in hardware or the database. The ideal situation would be to able a complete copy of the database at each instant. Due to time constraints this is not possible. This is where transaction log backups fit in. The first thing to understand is that no data is ever written directly to disk in SQL Server. This is for fault tolerance and recoverability reasons. Every modification to the database is written to the transaction log. Periodically, the checkpoint process will scan the transaction log and write any of the committed transactions in the log to disk.. These transactions are then marked as being committed. Transaction log backups allow you to backup all of the committed transactions. Between the full database backup and the transaction log, you can gain a complete picture of the database. Also, the transaction log is much smaller than the actual database so it can be backed up more frequently. Once a transaction log has been backed up, the inactive portion of the transaction log is removed. This is the block of data modifications that have been stored in the backup file, so they are no longer needed. This is also how you manage the size of your transaction logs.
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 |
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.