Disaster Recovery Techniques for SQL Server
Abstract
The time to think about how to put out a fire is not after it has started, but before. The same principle applies to mitigating data loss in a SQL Server or any other database environment. A disaster recovery plan should be well-thought-out before any problems arise and ideally before installing the server. Knowing specifically what disasters need to be protected against is also important. Will the plan be used to recover from losses at the record, table, database or server level?
The tools available to configure recovery from data or service loss are extensive, but this article will focus on tools that are a part of the SQL Server application. The methods discussed are: database backups; log shipping; database mirroring; and database snapshots.
Sample
Before looking at these methods, it's important to understand the way information is stored and updated in SQL Server databases. Every database has at least one data and one log file. Changes are normally recorded to the log file before being recorded to the data file. This mechanism allows the log file to be used as a secondary backup mechanism. Records written to the data file are stored in preconfigured 8 kilobyte blocks called pages. All updates to the data file are done from RAM and are done on a page level. With that in mind, how can these four methods be used to recover from data loss?
Database Backups
Every user database should have a backup schedule that is automated. The Database Maintenance Plan Wizard makes this easy to do. There are a number of backup options available in SQL Server, but the main methods are Full, Differential and Log.
A Full backup strategy is preferred since each backup will have all the data needed to restore the database. When this cannot be done every day or for each backup session, a Differential strategy might have to be included. This backs up all data that has changed since the last Full backup. Since SQL Server changes data on a page level, the Differential backup will include all pages that have been modified, even if all the records in them have not changed.
Log backups can only be done if the database is configured to not delete log data after it is recorded to the data file. Changing the database recovery model to Full or Bulk-Logged accomplishes this. By default, log backups truncate the log file after completion. Like the Differential backup, a Log backup can only be done if a Full backup is completed first.
Log backups are critical to accomplish what is referred to as an "up to the point of failure recovery." This is possible even after a data file is lost, but only if the log file is still intact. SQL Server allows you to connect to and backup the information in the existing log file even though the data file is not available. This kind of restoration means that no data is lost. When possible, a backup of the existing log file is always the first step when restoring a database from backups. This is a more likely scenario when the log file uses a different disk drive from the data file (which should be done for all critical databases).
When all of these backup methods are used together, the normal restore process would follow these steps.
- Backup the log file if it is still intact (Tail-Log Backup)
- Restore the latest Full Backup
- Restore the latest Differential Backup
- Restore all Log backups made after the Differential in sequential order
If you decide to script the restore process instead of using Management Studio, it is important to specify the NORECOVERY parameter for all the restores except for the last one. If any of the Log backup media are damaged, the restore process will have to end with the log backups done before that point.
Sample Code for Data Base Restore
USE MASTER
GO
BACKUP LOG AdventureWorks
TO DISK = 'F:\BACKUP\AD.BAK'
WITH NO_TRUNCATE
GO
RESTORE DATABASE AdventureWorks
FROM DISK = 'F:\BACKUP\AD.BAK'
WITH FILE = 1, NORECOVERY
GO
RESTORE DATABASE AdventureWorks
FROM DISK = 'F:\BACKUP\AD.BAK'
WITH FILE = 2, NORECOVERY
GO
RESTORE LOG AdventureWorks
FROM DISK = 'F:\BACKUP\AD.BAK'
WITH FILE = 3, NORECOVERY
GO
RESTORE LOG AdventureWorks
FROM DISK = 'F:\BACKUP\AD.BAK'
WITH FILE = 4, RECOVERY
System databases should also be backed up regularly. The main ones to consider are Master and Msdb. The Distribution database is only a concern when replication is configured, but it should also be backed up as well.
The Master database contains all login accounts, SQL Server Links, and references to databases controlled by the server. Changes to any of these components would warrant a backup. It might be a good idea to include daily backups in the server schedule even if it does not change. The restoration process for the Master database is unique. It requires that the server be put in single-user mode before running the restore statement. To do this, stop the SQL Server service, go to the folder where SQL Server was installed and run the command sqlservr.exe -c -m. After the database backup is restored, the SQL Server service can be stopped and restarted to function normally. If any information is lost because of using an older backup, it will need to be restored manually or with scripts. Server connections to existing databases can be re-established by using the attach method from Management Studio or with the sp_attach_db stored procedure.
Related Courses
SQL Server 2005 Administration
Related White Papers
Troubleshooting SQL 2005: Opening the Database Administrator's Toolbox
