Three recovery models for SQL Server data backup

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 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 to 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 severely 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 recovery options for the database, run the following command:

ALTER database name SET Recovery {full simple bulk_l ogged}

In addition to data, SQLThe server backup also includes the database outline (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.

Backup 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_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 a different 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 save process. SQL Enterprise Manager can also be used to create backup devices. The command line syntax is shown in listing a.





List B gives 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 demonstrated 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 data that has changed after a 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 the 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.





only if you modify it, it is necessary to back up the model database.





Use the SQL Server Agent to schedule your backup work.





If you have resources available in your production (production) environment, back up your production database to a local disk or 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.





Backup development and test databases use at least 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 with a 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.





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





BACKUP LOG {database} to





Below is an example of how to back up transactions to a logical device called 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





is just a basic knowledge





Although I have 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.