Three recovery models for backing up your SQL Server
Published: 01 May 2003 16:53 BST
With SQL Server 2000, there are myriad choices for backing up your database. Whether your database is small and changes infrequently or very large and changes rapidly, there is a backup strategy to meet your needs. Let's take a look at some of the basic backup strategies that will work for a variety of environments.
This article assumes that you are authorised to run backups for your database. That is, you are either the system administrator or in the db_owner or db_backupadministrator roles. Also, it assumes you have the operating system privileges to utilise the necessary resources required for a backup, for example, access to disk or tape drives.
Where to start
Before you begin backing up a SQL Server database, you need to know which recovery model the database is using. There are three different recovery models: FULL, BULK_LOGGED, and SIMPLE.
The FULL recovery model gives you the most recovery flexibility. It's the default recovery option for new databases. This model allows you to restore just part of a database or do a complete recovery. Assuming the transactions logs haven't been damaged, you can also recover up to the last committed transaction prior to a failure. This method uses the most transaction log space of all the recovery models and it causes a slight hit to SQL Server performance.
The BULK_LOGGED recovery model has fewer recovery options than the FULL model, but it doesn't have as severe a performance hit on bulk operations. It uses less log space on certain bulk operations because it records only the operations' results. With this model, however, you can't restore to a specific mark in the database, nor can you restore just parts of the database.
The SIMPLE recovery model is the easiest of the three to implement and it uses the least amount of storage space. However, recovery is limited to when the database was last backed up.
To find out which recovery model your database is using, run the following command, which should return either FULL, BULK_LOGGED, or SIMPLE: SELECT dbpropertyex("database", "recovery")
To change the recovery option for a database, run this command: ALTER DATABASE database name SET RECOVERY {FULL | SIMPLE | BULK_LOGGED}
In addition to data, SQL Server backups also contain the database schema and database metadata (e.g., database files, file groups, and their locations). SQL Server allows users to remain on the database while the backup occurs, so any transactions committed during the backup are recorded as part of the backup.
Previous
Did you find this article useful?
142 out of 307 people found this useful
- Share this article:
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
