Three recovery models for backing up your SQL Server
Published: 01 May 2003 16:53 BST
Backup the database
To back up a database, you can use the BACKUP command. (As an alternative, you could use SQL Enterprise Manager) As always, it's a good idea to know the syntax of the command first. The BACKUP command has many options, but the basic syntax of the command is: BACKUP DATABASE { database_name } TO < backup_device > |
A backup_device can be a disk or tape -- or it might be a logical backup device representing a disk file, a tape, or a named pipe.
If you're looking to do a quick, one-time backup, use a disk file like so: BACKUP DATABASE Northwind TO DISK = "c:\backup\Northwind.bak"
Alternatively, if you wish to back up to another server, UNC names can be used: BACKUP DATABASE Northwind TO DISK = "\\FILESERVER\Shared\Backup\Northwind.bak"
For regularly scheduled backups, a logical backup device should be used. A logical backup device can store several database backups and can reside on disk, tape, or a named pipe. If you're using a tape device, the tape drive must reside on the same physical server. Named pipes can take advantage of third-party backup software.
To create a logical backup device, use the sp_addumpdevice system stored procedure.
Again, SQL Enterprise Manager can be used to create the backup device. The command line syntax is shown here, in Listing A. Listing B offers an example of creating a logical backup device on disk.
Using the previously created backup device, the Northwind database could be backed up using this command: BACKUP DATABASE Northwind TO DiskBackup
Large, frequent backups
At this point, I've demonstrated how to backup an entire database. However, this only allows you to recover data up to the time the backup was completed. If the database changes frequently and is large, frequent full database backups could be impractical due to time and space constraints. There could be significant data loss in the event of a database failure.
There are two ways of improving recoverability in such cases, both of which require a full database backup. Both methods also require the database recovery model to be either FULL or BULK_LOGGED.
The first method is with a differential database backup, which captures and stores only data that has changed since the full backup. With its smaller file and concise information, it's very fast when it comes to data recovery.
This example creates a differential backup on a logical backup device called DiffBackupDevice: BACKUP DATABASE Northwind TO DiffBackupDevice WITH DIFFERENTIAL
The second method for improving recoverability is with transaction log backups, where recovery can be done to a specific point in time.
You may be asking how this is possible. Remember that the purpose of a transaction log is to record all transactions that occur within a database. A transaction log is what allows COMMIT and ROLLBACK to work correctly. To achieve this functionality, a before and after picture of the data must be recorded along with the type of operation, the beginning of the transaction, etc.
Previous
Did you find this article useful?
142 out of 307 people found this useful
Digg
Slashdot
Del.ici.ous
Stumble
Reddit
Full Talkback thread
3 comments
-
Hello I am quite new to SQL server and when I try... Adrian Scott -
USE the following, the above will not work.
S... Mahesh Seshadri S -
SELECT DATABASEPROPERTYEX('tempdb','recovery') Torus
