Other Free Encyclopedias » Online Encyclopedia » Encyclopedia - Featured Articles » Featured Articles: 0 Car Finance - What Does 0% Car Finance Mean to Black Tv Stands

Backup Database Sql Server - Backups for a SQL Server Database

recovery tape databases hard

A major requirement for managing a computer production system is the need to perform backups and restores on the system’s databases — the main reason for doing so is to have available an avenue of recovery when a disaster occurs. All SQL Server databases should be backed up on a regular basis; the process of restoring from backup should be tested also. SQL Server databases are built to allow several different kinds of backup, any and all of which can be combined into a disaster recovery plan customized for a site and its various types of data.

SQL Server databases can be processed via several different types of backup and recovery. Depending on the nature of the data stored, its volatility, and the need for the data, these types can be combined and parsed out over many databases in different ways, in order to build a robust strategy for disaster recovery.

A backup can be complete (the entire contents of the database is saved to other media), or differential (only the data that has changed since the last backup is saved).

Backup restores can be done to the original server containing the database, or directed to another server. A restore/recovery can be full, bulk-logged, or simple.

A full recovery is the safest mode in which a production system can operate. All the data of the database is restored to the point of failure, with, in addition, the recovery of the total backup of the transaction logs, which will ensure the maximum chance of a safe recovery.

Bulk-logged recovery is based on minimum logging, especially for any bulk-imports of data (for which only allocation and de-allocation of space is logged). Recovery will definitely be done to the point-in-time of the backup, but a recovery to point-of-failure is not guaranteed.

A simple recovery eliminates the use of any transaction logs to recover the database — only the data is restored to the point-in-time of the backup. The transaction log, therefore, is not involved in the backup, and easy to maintain — but, this model of recovery places severe limitations on recoverability.

In general, all SYSTEM databases (which includes Master, TEMPDB, and MSDB) are based on the simple recovery model. By default, upon creation, all user databases are set to full recovery model. Of course, full recovery is not possible until at least one complete backup has been completed (which should be the last step of the creation of any new user database). The recovery model of any SQL database can be changed after the database has been created.

It is possible to partially restore a SQL Server database — the database must contain more than one filegroup, because the partial restore is done a filegroup at a time. One, two, several, or many filegroups can be restored, up to the total number of filegroups in the database. Some of the options available within this partial restore of a SQL Server database from backup are:
- Truncation of the inactive portion of the transaction log
- Selection of files and filegroup for backup and restore
- File differential, which restores files and filegroups in differential mode

A SQL Server database can be backed up to either tape media or to a hard disk — backups to both types of target media can be initiated via a Transact-SQL batch command, or through the interactive SQL Server Enterprise Manager.

When a SQL Server database is backed up to a hard disk, the backup file may be initialized as new, or the backup file may be appended to a backup file already on the hard disk.

When a SQL Server database is backed up to a tape drive, the tape is formatted to the Microsoft Tape Format (MTF) — any tape formatted in this way can later hold other SQL Server backups also formatted to MTF.

When a tape is filled while doing a backup, a request for a new tape is initiated. With a hard disk backup, if the hard disk is completely filled, the backup operation is terminated in error.

If the SQL Database Server shuts down unexpectedly during backup, the operation will restart from the point at which the operation was interrupted — the same is true for a recovery.

Backup Data Services - Backup Data Services - Key To Data Protection [next] [back] Backup Software Xp - Backup Software for Windows XP - Built in Backup Software in XP, Other Backup Software for Windows XP - Best Ways to Backup Windows XP

User Comments

Your email address will be altered so spam harvesting bots can't read it easily.
Hide my email completely instead?

Cancel or