Three recovery models for SQL Server database backups

server| Backup | recovery | data | database

There are countless ways to back up a database in SQL Server 2000. No matter how big your database is or how frequently you change it, you have a backup strategy that meets your needs. Let's look at a few basic backup strategies that work in different environments.

This article assumes that you have permission to back up the database. In other words, you are either a system administrator, or a db_owner or a backupadministrator. Also, we assume that your operating system provides the right to access the resources needed for backup, such as access to a disk or a tape drive.

Where do I start?

Before you start backing up a SQL Server database, you need to know which recovery model the database uses. There are three different types of recovery models: full, bulk_logged, and simple.

The full recovery model provides you with the greatest resiliency. This recovery model is used by default for the new database. With this model, you can restore part of the database or restore it completely. Assuming that the transaction (transactions log) has not been compromised, you can also recover the last submitted (committed) transaction before the failure. In all recovery models, this model uses the most transaction space and has a slight impact on SQL Server performance.

The bulk_logged recovery model has fewer recovery options than the full model, but it does not significantly affect performance when batch operations (BULK operation) are performed. When doing some batch operations, it uses less recording space because it only needs to record the results of the operation. With this model, however, you cannot restore a particular tag in the database, and you cannot simply restore a portion of the database.

The simple recovery model is the easiest to implement in the three models, and it occupies the smallest amount of storage space. However, you can only restore the database at the end of the backup time.

To find the recovery model for your database, you can run the following command, which should return one of the three values of full, bulk_logged, and simple:

SELECT Dbpropertyex ("Database", "recovery")

To change the database recovery options, run the following command:

ALTER Database Database name SET RECOVERY {full | Simple | Bulk_Logged}

In addition to data, SQL Server backups include database outlines (schema) and database metadata (that is, database files, filegroups, and their locations). SQL Server allows the user to still use the database at backup time, so transactions that occur during the backup are also recorded in the backup.

Backing up a database

To back up the database, you can run the backup command. (You can also use SQL Enterprise Manager.) It's always a good idea to know its syntax before executing a command. The backup command has many options, and its basic syntax is:

BACKUP DATABASE {database_name}

to < backup_device > |

Backup_device can be either a disk or a tape-or it can be a logical backup device that is represented by a disk file, tape, or named pipe.

If you want to make a quick, one-time backup, use the disk file as follows:

BACKUP DATABASE Northwind to DISK = "C:\backup\Northwind.bak"

If you want to back up your database to another server, you can use the UNC name:

BACKUP DATABASE Northwind to DISK = "\\FILESERVER\Shared\Backup\Northwind.bak"

If you want to have a regular, scheduled backup, you need to use a logical backup device. A logical backup device can hold several database backups and reside on disk, tape, or named pipes. If you use a tape device, the tape drive must be 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 to save the process. SQL Enterprise Manager can also be used to create backup devices. The command line syntax is shown in listing a.

Listing B shows an example of creating a logical backup device on disk.

When the backup device is created, the Northwind database can be backed up with the following command:

BACKUP DATABASE Northwind to DiskBackup

Backup of large database with frequent changes

Now, I've shown how to back up the entire database. However, it only allows you to restore the data saved by the database at the end of the backup. If the database is large and changes frequently, it is impractical to make a full database backup frequently because of time and space constraints. When a database fails, a large amount of data loss can result.

In this case, there are two ways to improve recoverability, all two of which require a full database backup. Both of these methods require that the database recovery model be full or bulk_logged.

The first method takes a differential database backup, which captures and saves only data that has changed since the full database backup. Because of its small file size and concise information, it is very fast to recover data.

The following example creates a differential backup on a logical backup device named Diffbackupdevice:

BACKUP DATABASE Northwind to diffbackupdevice with differential

The second way to improve recoverability is to leverage transaction backups, which can be completed at a specific point in time.

You may ask how this is possible. Remember that the purpose of the transaction is to record all transactions that occur in the database. Transactions allow commit and rollback to work correctly. To achieve this function, the value of the data before and after the change must be recorded along with the type of operation, the start of the transaction (time), and so on.

Backup skills

Use the techniques listed below to make sure you don't forget critical steps during a weekly database backup.

Back up the primary database once a week. If you create, modify, or stop a database, add a new SQL Server message, add or stop connecting to a server, or add a recording device, make a manual backup.

Back up the msdb database once a day. It is generally very small, but important because it contains all of the SQL Server work, operations, and scheduling tasks.

It is only necessary to back up the model database when you modify it.

Use the SQL Server Agent to schedule your backup work.

If you have a ready-made resource in your production (production) environment, back up the production database to a local disk or a network server (with the same switch). Then, copy the backup file/device to the tape. In the case of many hardware failures (especially in a raid system), the disk is often intact (inact). If the backup file is on disk, the speed of recovery is much higher.

The backup development and test database should at least use the simple recovery model.

In addition to scheduled scheduled backups, back up the user database after an nonlogged batch operation (such as a batch copy), creating an index, or changing the recovery model.

If you are using the simple recovery model, remember to back up your database after the truncated (truncate) transaction.

Record your recovery steps in the document. At a minimum, you should probably record these steps and note the location of all the important files.

All of this information is saved in the transaction before the truncated record, that is, before all submitted (committed) transactions are emptied from the record. In the simple recovery model, records are truncated during a checkpoint period (when the SQL Server memory buffer is written to disk), it occurs automatically, but can also be performed manually. This is why the simple recovery model does not support Point-in-time (Point-in-time) restores. Under the full and bulk_logged recovery model, transactions are truncated when transactions are backed up, unless you explicitly point out that they are not truncated.

To back up transactions, use the BACKUP LOG command. The basic syntax is very similar to the backup command:

BACKUP LOG {Database} to

Here's how to back up the transaction to an example on a logical device named Logbackupdevice:

BACKUP TRANSACTION Northwind to Logbackupdevice

If you do not want to truncate the transaction, use the NO_TRUNCATE option as follows:

BACKUP TRANSACTION Northwind to Logbackupdevice with No_truncate

It's just basic knowledge.

Although I've just outlined the basics of database recovery in this article, you can still use these techniques to find the right direction. So, to avoid unnecessary panic (loss of data), you have to back up the main database every week and back up msdb daily.