Recovering from a Database Failure

“Orphaned transaction log!”

The first thing you should always think of whenever a database has failed in SQL Server 2005 is whether you can recover the orphaned transaction log. The orphaned transaction log represents all the changes to the database since the last transaction log backup. If you can back up these changes and apply it in your database recovery procedure, you have lost no committed transactions in your SQL Server 2005 database solution. That’s a worthwhile pursuit!

Note 

In SQL Server 2005 Books Online, the orphaned transaction log is referred to as the tail of the log or tail-log.

But…it all stems from an appropriately planned backup strategy, in which there are a number of considerations and important choices to be made. So let’s press on.

Planning a Database Backup Strategy

Planning a database backup strategy should revolve around what you’re trying to protect against and the maximum amount of downtime your organization can afford for a given SQL Server 2005 database solution. Now that might sound really obvious, but you’d be surprised how many IT shops don’t go through the process of analyzing or realizing the need for initially analyzing such requirements.

Otherwise, you have a myriad of other considerations:

• How long the backup process will take

• How long the backup process should take

• The size of your database

• The available storage capacity for your database backups

• Whether there any regulatory requirements for the retention of backup sets

• The stability/reliability of your backup media

• The volatility of the data within your SQL Server 2005 database solution

• How much data you can afford to lose in the case of a database failure

Don’t forget that you are also potentially recovering from accidental or malicious data deletion. This is a critical point that likewise is commonly overlooked.

Don’t Forget the SQL Server Agent!

Only the other week, during the Cricket World Cup, I was asked to go to a television station here in Sydney, Australia, for some consulting work. They thought they had configured an appropriate backup strategy, but when a disaster occurred (in this case, the accidental deletion of data), they discovered that they could not recover the data. Can you guess why?

In February 2007 they had rebooted SQL Server 2005 for some maintenance, but they did not have the SQL Server Agent configured to start automatically. And no one had bothered to check to make sure it was running after the reboot. And no one had set up the SQL Server 2005 solution to notify anyone that backups were occurring. And so on….

Obviously, there are a number of issues here, but the moral is that a data recovery plan includes a number of important elements. And it is important to ensure that all these elements are configured correctly.

-Victor Isakov

Database Recovery Model

Before diving into database backup strategies, it is important to understand the various database recovery models available within SQL Server 2005 because they will have a direct impact on your backup strategies and what you can recover in the case of data loss.

SQL Server 2005 has three recovery models that a database can use. The database recovery model fundamentally controls how much is written to the database’s transaction log and when it is cleared, which in turn affects the backup and restore operations. Choosing an appropriate recovery model for a database is critical for the implementation of your disaster recovery plan.

The syntax for changing a database’s recovery model is as follows:

 ALTER DATABASE <database_name> SET RECOVERY [ FULL | BULK_LOGGED | SIMPLE ]

FULL Recovery Model

The FULL recovery model fully logs all operations within the database to the transaction log, including the following:

• All DML operations:

  • INSERT statements

  • UPDATE statements

  • DELETE statements

• All bulk operations, including the following:

  • BCP operations

  • BULK INSERT statements

  • SELECT INTO statements

  • WRITETEXT statements

  • UPDATETEXT statements

  • CREATE INDEX statements

Note 

Nonlogged operations have not existed in SQL Server since SQL Server 2000.

By fully logs, we effectively mean that every row modified in a table or index is reflected in the transaction log. There is no dependency between the transaction log files and the data-base’s data files.

When using the FULL recovery model, consider the following:

• The FULL recovery model supports point-in-time recovery.

• The FULL recovery model supports log marks.

• There is no dependency between the transaction log and the database data files during the transaction log backup under the FULL recovery model.

• The transaction log will potentially be larger under the FULL recovery model.

• Restoring from transaction log backups under the FULL recovery model is quicker because indexes do not have to be rebuilt.

Note 

The FULL recovery model effectively provides the least chance of data loss.

BULK_LOGGED Recovery Model

The BULK_LOGGED recovery model minimally logs the bulk operations discussed for the FULL recovery model. This means it records only the fact that an index was created or a bulk insert was performed, for example, and the extents that were affected. When a transaction log is backed up, SQL Server 2005 additionally has to back up these extents that it has identified as being modified.

When using the BULK_LOGGED recovery model, consider the following:

• The BULK_LOGGED recovery model supports point-in-time recovery.

• The BULK_LOGGED recovery model supports log marks.

• The database data files have to be available during the transaction log backups under the BULK_LOGGED recovery model.

• The transaction log will potentially not consume as much space as the FULL recovery model.

• The time taken to restore a transaction log is similar to the FULL recovery model because SQL Server 2005 just has to overwrite the modified extents and not redo the operations.

Warning 

Because the transaction log backup depends on the database data files, there is a risk of data loss if you lose the database data files between the minimally logged operation and the transaction log backup.

SIMPLE Recovery Model

The SIMPLE recovery model automatically truncates the transaction log periodically whenever the SQL Server 2005 database engine runs the checkpoint process. Simple!

When using the SIMPLE recovery model, consider the following:

• You cannot perform transaction log backups when in the SIMPLE recovery model.

• The transaction log can still fill up when using the SIMPLE recovery model.

Note 

The SIMPLE recovery model provides the highest potential for data loss.

Backing Up Databases

Irrespective of whether you have implemented some form of a high-availability solution, you still need to implement an appropriate database backup strategy. SQL Server 2005 allows you to back up your database to the following destinations:

• Disk drives

• Network shares

• Tapes

Database users can still access databases while they are being backed up. SQL Server has always had this online backup capability. Given appropriate hardware, the database users should not notice any degradation in the performance of your database solution.

Types of Database Backups

SQL Server 2005 offers a number of different types of backups, some of which are fairly standard in backup technology.

Full Database Backups

A full database backup basically contains everything SQL Server 2005 needs to restore the database, including the following:

• The name, location, and size of all the database files

• All the data pages within the database

• Any modifications made to the database between the start and the completion of the database backup process

Advantages include the following:

• Simple strategy to back up and restore from

• No dependency between backup sets

Disadvantages include the following:

• Size of backup

• Time required for backup

• No point-in-time recovery

• No transaction log management

Tip 

Don’t forget to implement some sort of transaction log management strategy if you are using only a full backup strategy.

Full backup strategies are typically used in scenarios where the database is small or the maintenance window is large enough to accommodate a full database backup.

Table 3.1 shows an example of a full backup strategy using full database backups.

Table 3.1: Example of a Full Backup Strategy
Open table as spreadsheet

Backup	Sat	Sun	Mon	Tue	Wed	Thu	Fri
Full	10 P.M.	10 P.M.	10 P.M.	10 P.M.	10 P.M.	10 P.M.	10 P.M.
Transactional
Differential

Note 

A full database backup is always required for differential and incremental backups as a starting point.

Transaction Log Backups

Transaction log backups contain just the transaction log activity since the last transaction log backup. (As discussed, there is a slight variation with what is physically stored depending on whether the database is using the FULL or BULK_LOGGED recovery model.) Transaction log backups effectively form an incremental backup strategy.

Advantages include the following:

• Transaction logs are automatically truncated at the end of the transaction log backup process.

  Tip 

  For more volatile databases, you would tend to have more frequent transaction log backups to manage the transaction log’s size.

• Typically smaller backup sets.

• Typically faster backups.

• Point-in-time recovery.

• Stop at mark supported.

• Can be used to back up all data modification up to the failure of the database’s data files (the orphaned log, or tail-log, we referred to earlier).

Disadvantages include the following:

• Dependency on full database backup.

• Dependency between the cumulative transaction log backups.

  Warning 

  If you lose one transaction log backup in a sequence of transaction log backups, you will not be able to fully restore your SQL Server 2005 database.

• More complex restore process than full backup strategy.

• The restore process takes longer than for a full database backup because the transaction logs have to be “replayed.”

Incremental backup strategies are typically used in scenarios where a smaller maintenance window is available and/or functionality such as point-in-time recovery is required. They are also commonly used as a mechanism for managing the database’s transaction log.

Table 3.2 shows an example of an incremental backup strategy using full database backups in conjunction with transaction log backups.

Table 3.2: Example of an Incremental Backup Strategy
Open table as spreadsheet

Backup	Sat	Sun	Mon	Tue	Wed	Thu	Fri
Full							10 P.M.
Transactional	2 A.M.	2 A.M.	2 A.M.	2 A.M.	2 A.M.	2 A.M.	2 A.M.
	4 A.M.	4 A.M.	4 A.M.	4 A.M.	4 A.M.	4 A.M.	4 A.M.
	6 A.M.	6 A.M.	6 A.M.	6 A.M.	6 A.M.	6 A.M.	6 A.M.
	8 A.M.	8 A.M.	8 A.M.	8 A.M.	8 A.M.	8 A.M.	8 A.M.
	10 A.M.	10 A.M.	10 A.M.	10 A.M.	10 A.M.	10 A.M.	10 A.M.
	12 A.M.	12 A.M.	12 A.M.	12 A.M.	12 A.M.	12 A.M.	12 A.M.
	2 P.M.	2 P.M.	2 P.M.	2 P.M.	2 P.M.	2 P.M.	2 P.M.
	4 P.M.	4 P.M.	4 P.M.	4 P.M.	4 P.M.	4 P.M.	4 P.M.
	6 P.M.	6 P.M.	6 P.M.	6 P.M.	6 P.M.	6 P.M.	6 P.M.
	8 P.M.	8 P.M.	8 P.M.	8 P.M.	8 P.M.	8 P.M.	8 P.M.
	10 P.M.	10 P.M.	10 P.M.	10 P.M.	10 P.M.	10 P.M.	10 P.M.
	12 P.M.	12 P.M.	12 P.M.	12 P.M.	12 P.M.	12 P.M.	12 P.M.
Differential

Tail-Log Backups

It is important to remember to back up the tail-log, as mentioned initially, to minimize the amount of data loss in a disaster recovery situation. So again, whenever a SQL Server 2005 database fails, you should first examine whether the tail-log is available. If it is, back it up! Then you can panic or do whatever you were planning to do.

The point is that it is too late to back up the tail-log if you have already initiated a database restore and SQL Server 2005 has overwritten a perfectly good tail-log. And it does happen!

The syntax to use if the database is still online, as in the case of accidental data deletion, is as follows:

 BACKUP LOG database_name TO <backup_device> WITH NORECOVERY

The syntax to use in the case of a damaged database (which is offline) is as follows:

 BACKUP LOG database_name TO <backup_device> [WITH { CONTINUE_AFTER_ERROR | NO_TRUNCATE }

Differential Database Backups

Differential backups contain just the extents that have been modified since the last full database backup. They effectively form a differential backup strategy.

Advantages include the following:

• Smaller backup sets compared to a full database backup

  Note 

  As more of your extents change in the database, the differential backups will grow in size until you again perform a full database backup.

• Typically faster backups

• No dependency between the differential backups.

• Restore process typically quicker than a transaction log restore because the individual operations do not need to be replayed

Note 

The size of your differential backups obviously depends on specific database modification patterns.

Disadvantages include the following:

• Dependency on full database backup

• No point-in-time recovery

• Stop at mark not supported

• More complex restore process than full backup strategy

• Restore process longer compared to a full backup strategy

• No transaction log management

Tip 

Don’t forget to implement some sort of transaction log management strategy if you are using only a differential backup strategy.

Differential backup strategies are typically used in scenarios where a quicker backup and restore are required, so it’s trying to find the middle ground between a full and an incremental backup strategy.

Table 3.3 shows an example of an incremental backup strategy using full database backups in conjunction with transaction log backups.

Table 3.3: Example of a Differential Backup Strategy
Open table as spreadsheet

Backup	Sat	Sun	Mon	Tue	Wed	Thu	Fri
Full	1 A.M.
Transactional
Differential	9 P.M.	9 P.M.	9 P.M.	9 P.M.	9 P.M.	9 P.M.	9 P.M.

A common question we get asked is, “What backup strategy should I use?” This is a typical “How long is a piece of string” type of question. Our most frequency answer is, “Have you considered a combination of all three?” In other words, get the benefit of all worlds so you can have fast backups, transaction log management, point-in-time recovery, faster restores, and so on. Table 3.4 shows such an example in which both differential and incremental backups are used in conjunction with a full database backup.

Table 3.4: Example of Incremental and Differential Backup Strategy
Open table as spreadsheet

Backup	Sat	Sun	Mon	Tue	Wed	Thu	Fri
Full							10 P.M.
Transactional			10 A.M.	10 A.M.	10 A.M.	10 A.M.	10 A.M.
			11 A.M.	11 A.M.	11 A.M.	11 A.M.	11 A.M.
			12 A.M.	12 A.M.	12 A.M.	12 A.M.	12 A.M.
			1 P.M.	1 P.M.	1 P.M.	1 P.M.	1 P.M.
			2 P.M.	2 P.M.	2 P.M.	2 P.M.	2 P.M.
			3 P.M.	3 P.M.	3 P.M.	3 P.M.	3 P.M.
			4 P.M.	4 P.M.	4 P.M.	4 P.M.	4 P.M.
			5 P.M.	5 P.M.	5 P.M.	5 P.M.	5 P.M.
Differential			6 P.M.	6 P.M.	6 P.M.	6 P.M.	6 P.M.

File/File Group Backups

File and/or file group backups have been available in different versions of SQL Server. It has always been important to understand how they operate to ensure that you can recover correctly from a database failure. As before, SQL Server 2005 allows you to back up individual files or file groups, but there have been some enhancements.

File/file group backup strategies are typically used in VLDB environments, and we mean very large, where the maintenance window to perform the database backup simply doesn’t exist. Consequently, your backup strategy will involve rotating the backups of the files or file group.

Importantly, you need to back up the transaction log of the database so that the restore process can synchronize the restored database files to the same point in time. Therefore, your database has to be using the FULL or BULK_LOGGED recovery model.

Note 

SQL Server 2005 is flexible in that read-only file groups can be backed up and restored although the database is using the SIMPLE recovery model.

Table 3.5 shows an example of a file backup strategy using full file backups in conjunction with transaction log backups. To keep things simple, assume that the VLDB consists of six database data files and each individual file takes six hours to back up.

Table 3.5: Example of a File Backup Strategy
Open table as spreadsheet

Backup	Sat	Sun	Mon	Tue	Wed	Thu	Fri
Full
Transactional			1 A.M.	1 A.M.	1 A.M.	1 A.M.	1 A.M.
Differential
File 1			7 P.M.
File 2				7 P.M.
File 3					7 P.M.
File 4						7 P.M.
File 5							7 P.M.

Note 

You can also restore individual files or file groups from a full database backup.

Partial Database Backups

A new feature of SQL Server 2005 gives you the ability to only partially back up your database yet still be able to restore it. This strategy works for databases that contain read-only file groups. In these cases, you can get away with just backing up your primary file group and all read-write file groups.

Tip 

For read-only databases, it is sufficient to back up the primary data file.

For larger databases that contain a lot of read-only reference data, you might want to structure your database accordingly so as to take advantage of this feature and reduce the amount of time it takes to perform backups, as well as reduce the size of your backups.

Data Recovery Using Database Snapshots

Another important concept to understand is how to use database snapshots to recover from accidentally or maliciously deleted data. Database snapshots are new in SQL Server 2005 and allow you to create a snapshot of your database at a particular point in time.

Note 

Only SQL Server 2005 Enterprise Edition supports database snapshots.

When you create a database snapshot, SQL Server 2005 uses sparse file technology to effectively maintain the database as it was at the time of the snapshot. So when a particular database page needs to be modified because of a DML operation, SQL Server 2005 copies the old version of the data page to the database snapshot file before allowing the data modification.

Note 

Only NTFS partitions support sparse files technology.

Although the main use for database snapshots is probably for reporting purposes, you can also use them as a means of recovering from accidental or malicious data modification. Reverting to a database snapshot is most likely easier in most cases than relying on point-in-time recovery or stopping at a particular mark in the transaction log.

Database snapshots are created as part of the CREATE DATABASE statement. The following example shows a database snapshot being created for the AdventureWorks database:

 USE master ; GO CREATE DATABASE AdventureWorks_20071502 ON ( NAME = 'AdventureWorks_Platypus', FILENAME = 'E:\Platypus\AdventureWorks_20071502.mdf') AS SNAPSHOT OF AdventureWorks ; GO

Warning 

You cannot revert to a database snapshot if you have experienced a database or hardware failure.

The BACKUP Statement

As you would expect, the BACKUP statement has quite a comprehensive set of options. Quite a few clauses are new to SQL Server 2005. It’s worth our while to go through and highlight the more important clauses in the BACKUP statement.

The syntax for the BACKUP statement is as follows:

 -- Backing Up a Whole Database BACKUP DATABASE { database_name | @database_name_var }   TO <backup_device> [ ,...n ]   [ <MIRROR TO clause> ] [ next-mirror-to ]   [ WITH { DIFFERENTIAL | <general_WITH_options> } [ ,...n ] ] [;]

The BACKUP DATABASE statement is used to back up the entire database. The MIRROR TO clause is a new feature in SQL Server 2005 that allows you to back up the database to more than one location simultaneously. The mirrored backup devices must be of the same type.

 -- Backing Up Specific Files or File groups BACKUP DATABASE { database_name | @database_name_var }  <file_or_filegroup> [ ,...n ]   TO <backup_device> [ ,...n ]   [ <MIRROR TO clause> ] [ next-mirror-to ]   [ WITH { DIFFERENTIAL | <general_WITH_options> } [ ,...n ] ] [;]

To back up a file or a file group from a database, specify the name of the file or file group after the database name in the BACKUP DATABASE statement:

 -- Creating a Partial Backup BACKUP DATABASE { database_name | @database_name_var }  READ_WRITE_FILEGROUPS [ , <read_only_filegroup> [ ,...n ] ]   TO <backup_device> [ ,...n ]   [ <MIRROR TO clause> ] [ next-mirror-to ]   [ WITH { DIFFERENTIAL | <general_WITH_options> } [ ,...n ] ] [;]

To back up a read/write file or file group in a database, specify the name of the file or file group after the database name in the BACKUP DATABASE statement:

 -- Backing Up the Transaction Log (full and bulk-logged recovery models) BACKUP LOG { database_name | @database_name_var }   TO <backup_device> [ ,...n ]   [ <MIRROR TO clause> ] [ next-mirror-to ]   [ WITH { <general_WITH_options> | <log-specific_optionspec> } [ ,...n ] ] [;]

The BACKUP LOG statement is used to back up the transaction log of a database. Again, use the MIRROR TO clause to back up the transaction log to more than one location simultaneously.

Tip 

Obviously, the transaction log backups are important, so you might be advised to mirror them to different locations to protect against media corruption.

 -- Truncating the Transaction Log (breaks the log chain) BACKUP LOG { database_name | @database_name_var }   WITH { NO_LOG | TRUNCATE_ONLY } [;]

If you are using a full database backup strategy (perhaps in conjunction with a differential backup strategy) and are performing transaction log backups, your transaction log will grow out of control until it is full or until the disk partition is full. To prevent this, you will have to manage the transaction log by truncating it periodically or after the full (or differential) database backup.

 <backup_device>::=  {    { logical_device_name | @logical_device_name_var }  | { DISK | TAPE } =      { 'physical_device_name' | @physical_device_name_var }  }

The backup device, as mentioned, can be either tape or disk. You can back up across the network by specifying a UNC path.

 <MIRROR TO clause>::=  MIRROR TO <backup_device> [ ,...n ] <file_or_filegroup>::=  {    FILE = { logical_file_name | @logical_file_name_var }  | FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }  } <read_only_filegroup>::= FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var } <general_WITH_options> [ ,...n ]::= --Backup Set Options       COPY_ONLY    DESCRIPTION = { 'text' | @text_variable }  | NAME = { backup_set_name | @backup_set_name_var }  | PASSWORD = { password | @password_variable }  | [ EXPIREDATE = { date | @date_var }         | RETAINDAYS = { days | @days_var } ]  | NO_LOG

The backup set options have a number of safety mechanisms that prevent a backup from being overwritten. The EXPIREDATE and RETAINDAYS options control when the backup set can be overwritten.

The PASSWORD option for the backup set is designed to prevent unauthorized RESTORE operations from the backup set.

Note 

The PASSWORD option does not prevent a backup set from being overwritten. Use the MEDIAPASSWORD option instead.

Warning 

The PASSWORD option is being deprecated and will not be supported in a future edition of SQL Server.

The COPY_ONLY option is a new feature in SQL Server 2005 that allows you to back up a database or a transaction log without affecting the regular backup/restore process:

• For a full database backup, the differential map used for differential backups is not modified.

• For an incremental backup, the transaction log is not truncated.

It’s as if the backup never took place.

Tip 

Make sure you use the COPY_ONLY option if you are planning to steal a copy of the database.

 --Media Set Options    { NOINIT | INIT }  | { NOSKIP | SKIP }  | { NOFORMAT | FORMAT }  | MEDIADESCRIPTION = { 'text' | @text_variable }  | MEDIANAME = { media_name | @media_name_variable }  | MEDIAPASSWORD = { mediapassword | @mediapassword_variable }  | BLOCKSIZE = { blocksize | @blocksize_variable }

The INIT option is used to overwrite (initialize) a backup set. The SKIP option is used to disable the expiration date safety check. The MEDIAPASSWORD option for the backup set is designed to prevent a backup set from being overwritten.

 --Data Transfer Options    BUFFERCOUNT = { buffercount | @buffercount_variable }  | MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable } --Error Management Options    { NO_CHECKSUM | CHECKSUM }  | { STOP_ON_ERROR | CONTINUE_AFTER_ERROR }

The CHECKSUM option is a new feature in SQL Server 2005 that helps ensure the validity of the backup by doing the following:

• Verifying the page’s checksum or torn page before backing it up

• Generating a checksum for the backup set

The STOP_ON_ERROR and CONTINUE_AFTER_ERROR options control what happens during the backup process in the event of a backup checksum arising.

Note 

Checking and calculating checksums during backups can slow down the backup process.

 --Compatibility Options    RESTART --Monitoring Options    STATS [ = percentage ] --Tape Options    { REWIND | NOREWIND }  | { UNLOAD | NOUNLOAD } --Log-specific Options    { NORECOVERY | STANDBY = undo_file_name }  | NO_TRUNCATE

The NORECOVERY option is used to back up the tail-log, leaving the database in a RECOVERING state. The NO_TRUNCATE option is used to back up the transaction log, irrespective of the data-base’s availability. It does not truncate the transaction log.

Note 

The NO_TRUNCATE option is used to back up the tail-log of a damaged database.

Restoring Databases

Database restores are typically a straightforward process, because you are very much depen-dant on your initial backup strategy. If there are no backups, for example, or if they are corrupt, then there is nothing to restore. Simple.

Tip 

You should periodically test your database backup strategy by restoring the database to a separate SQL Server 2005 instance. This process will allow you to test the quality of your hardware, backup strategy, and restore processes.

SQL Server 2005 has made the restore process extremely easy. SQL Server 2005 will automatically perform the following during a restore:

1. Drop the old database if it exists.

2. Delete the old database files if they exist.

3. Create the database files.

4. Restore the database data.

5. Clean up the database as required.

Most DBAs will use the SQL Server Management Studio (SSMS) environment to automatically determine the latest database backup sets and will initiate a restore procedure through it.

Database Restore Strategies

Your database restore strategy will depend on why you have initiated a database restore. Different reasons require a different restore strategy and certainly will result in different pressure situations.

For example, if you are planning to restore a database because of an accidental deletion of data, you would use a strategy similar to this:

1. Stop any further DML operations in the database.

   Tip 

   You can stop operations by setting the database to read-only, single-user, or restricted-user mode.

2. Determine the point in time to which you plan to restore.

3. Back up the transaction log (assuming the database is not using SIMPLE recovery mode).

4. Determine whether there are any DML operations after the point in time determined from step 2 that will need to be applied post-restore, and devise a strategy for their application.

5. Locate the latest database backup.

6. Locate the latest differential backup, if it exists.

7. Locate the latest transaction log backups, if they exist.

8. Verify that all the located backups are valid and the database is restorable.

9. Back up the database.

   Note 

   Backing up the database is optional but is a great idea, no?

10. Drop the database, if required.

11. Restore the full database backup from step 5.

12. Restore the differential backup from step 6, if it exists.

13. Restore the transaction logs from step 7, if they exist.

14. Restore the transaction log from step 3, stopping at the time you identified in step 2.

15. Apply the DML operations identified in step 4.

On the other hand, if your database has failed, you would do the following:

1. Panic.

2. Calm down, and think about what course of action is required.

3. Attempt to back up the orphaned transaction log (tail-log).

4. Perform an RCA of what has caused the database failure.

5. Address the problem determined by the RCA in step 4.

6. Locate the latest database backup.

7. Locate the latest differential backup, if it exists.

8. Locate the latest transaction log backups, if they exist.

9. Verify that all the located backups are valid and the database is restorable.

10. Restore the full database backup from step 6.

11. Restore the differential backup from step 7, if it exists.

12. Restore the transaction logs from step 8, if they exist.

13. Restore the orphaned transaction log (tail-log) from step 3.

As you can see, this requires a well-structured and thought-out process. There is no point in quickly jumping in and starting to restore backup sets willy-nilly.

This also highlights the benefits of having a procedure manual, as we discussed earlier for restoring databases, especially in the inevitable high-pressure situation.

The RESTORE Statement

The RESTORE statement is relatively straightforward. Although most DBAs will use SSMS, as mentioned earlier, it is important to understand the various restore options available. So, for completeness sake, we’ll go through the RESTORE statements-but quickly.

Full Database Restore

A full database restore forms the basis of a restore strategy. It’s a fairly straightforward affair. The complete syntax to restore an entire database is as follows:

 RESTORE DATABASE { database_name | @database_name_var } [ FROM <backup_device> [ ,...n ] ] [ WITH    [ { CHECKSUM | NO_CHECKSUM } ]    [ [ , ] { STOP_ON_ERROR | CONTINUE_AFTER_ERROR } ]    [ [ , ] FILE = { backup_set_file_number | @backup_set_file_number } ]    [ [ , ] KEEP_REPLICATION ]    [ [ , ] MEDIANAME = { media_name | @media_name_variable } ]    [ [ , ] MEDIAPASSWORD = { mediapassword |                     @mediapassword_variable } ]    [ [ , ] MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name' ]                 [ ,...n ]    [ [ , ] PASSWORD = { password | @password_variable } ]    [ [ , ] ENABLE_BROKER ]    [ [ , ] ERROR_BROKER_CONVERSATIONS ]    [ [ , ] NEW_BROKER ]    [ [ , ] { RECOVERY | NORECOVERY | STANDBY =           {standby_file_name | @standby_file_name_var }    } ]    [ [ , ] REPLACE ]    [ [ , ] RESTART ]    [ [ , ] RESTRICTED_USER ]    [ [ , ] { REWIND | NOREWIND } ]    [ [ , ] { UNLOAD | NOUNLOAD } ]    [ [ , ] STATS [ = percentage ] ]    [ [ , ] { STOPAT = { date_time | @date_time_var }     |  STOPATMARK = { 'mark_name' | 'lsn:lsn_number' }               [ AFTER datetime ]     |  STOPBEFOREMARK = { 'mark_name' | 'lsn:lsn_number' }              [ AFTER datetime ]    } ] ] [;] <backup_device> ::= {    { logical_backup_device_name |             @logical_backup_device_name_var }    | { DISK | TAPE } = { 'physical_backup_device_name' |               @physical_backup_device_name_var } }

You use the CHECKSUM option when you want SQL Server 2005 to verify that backup checksums are correct. If a checksum error is detected, the restore process will stop.

Tip 

Use the CONTINUE_AFTER_ERROR option to force the restore process to continue although a checksum error has been detected.

You use the MOVE option to restore the database to potentially a different set of files that were used by the original database that was backed up. This option is typically used when you need to restore a database to a different SQL Server 2005 instance where the drives, directories, or filenames might be different.

You use the RECOVERY option when there are no more backup sets to be restored, so SQL Server 2005 “cleans up” the database and makes it available to database users.

You use the NORECOVERY option when there are additional backup sets to be restored, so SQL Server 2005 does not “clean up” the database in anticipation of the additional restores to be made.

Partial Database (Piecemeal) Restore

SQL Server 2005 has a new capability called piecemeal restores, which basically allow a database to be partially available as soon as the primary file group has been restored. So, database users can access that section of database while the remaining file groups are restored. The remaining file groups are offline until they are restored.

It’s a great feature that, with some careful planning, you can utilize to bring sections of your database that are used more often online quicker during a database restore process. It’s all in the planning of the file groups.

Note 

Online piecemeal restores are available in the Developer and Enterprise Edi-tions of SQL Server 2005.

The PARTIAL option starts the initial phase of such an online piecemeal restore. The complete syntax to restore a part of a database is as follows:

 RESTORE DATABASE { database_name | @database_name_var }   <files_or_filegroups> [ ,...n ]  [ FROM <backup_device> [ ,...n ] ]  [ WITH      PARTIAL    [ [ , ] { CHECKSUM | NO_CHECKSUM } ]    [ [ , ] { STOP_ON_ERROR | CONTINUE_AFTER_ERROR } ]    [ [ , ] FILE = { backup_set_file_number | @backup_set_file_number } ]    [ [ , ] MEDIANAME = { media_name | @media_name_variable } ]    [ [ , ] MEDIAPASSWORD = { mediapassword |                       @mediapassword_variable } ]    [ [ , ] MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name' ]                 [ ,...n ]    [ [ , ] PASSWORD = { password | @password_variable } ]    [ [ , ] NORECOVERY ]    [ [ , ] REPLACE ]    [ [ , ] RESTART ]    [ [ , ] RESTRICTED_USER ]    [ [ , ] { REWIND | NOREWIND } ]    [ [ , ] { UNLOAD | NOUNLOAD } ]    [ [ , ] STATS [=percentage ] ]    [ [ , ] { STOPAT = { date_time | @date_time_var }     |  STOPATMARK = { 'mark_name' | 'lsn:lsn_number' }               [ AFTER datetime ]     |  STOPBEFOREMARK = { 'mark_name' | 'lsn:lsn_number' }              [ AFTER datetime ]    } ] ] [;] <backup_device> ::= {    { logical_backup_device_name |             @logical_backup_device_name_var }    | { DISK | TAPE } = { 'physical_backup_device_name' |               @physical_backup_device_name_var } } <files_or_filegroups> ::= {    FILE = { logical_file_name_in_backup | @logical_file_name_in_backup_var }    |    FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }    |    READ_WRITE_FILEGROUPS }

File, File Group, or Page Restore

Restoring a file, file group, or page is different from the partial database restore discussed earlier. Don’t forget that after you restore a file, file group, or page, you need to restore the transaction logs to ensure that the data is in sync.

The complete syntax to restore specific files, file groups, or pages is as follows:

 RESTORE DATABASE { database_name | @database_name_var }      <file_or_filegroup_or_pages> [ ,...n ] [ FROM <backup_device> [ ,...n ] ] [ WITH    [ { CHECKSUM | NO_CHECKSUM } ]    [ [ , ] { STOP_ON_ERROR | CONTINUE_AFTER_ERROR } ]    [ [ , ] FILE = { backup_set_file_number | @backup_set_file_number } ]    [ [ , ] MEDIANAME = { media_name | @media_name_variable } ]    [ [ , ] MEDIAPASSWORD = { mediapassword |                       @mediapassword_variable } ]    [ [ , ] MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name' ]                 [ ,...n ]    [ [ , ] PASSWORD = { password | @password_variable } ]    [ [ , ] NORECOVERY ]    [ [ , ] REPLACE ]    [ [ , ] RESTART ]    [ [ , ] RESTRICTED_USER ]    [ [ , ] { REWIND | NOREWIND } ]    [ [ , ] { UNLOAD | NOUNLOAD } ]    [ [ , ] STATS [ =percentage ] ] ] [;] <backup_device> ::= {    { logical_backup_device_name |             @logical_backup_device_name_var }    | { DISK | TAPE } = { 'physical_backup_device_name' |               @physical_backup_device_name_var } } <file_or_filegroup_or_pages> ::= {    FILE = { logical_file_name_in_backup | @logical_file_name_in_backup_var }    | FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var } }       | PAGE = 'file:page [ ,...n ]' }

The ability to restore pages is a new feature in SQL Server 2005, and we will discuss that in more detail shortly.

Transaction Log Restore

When restoring the transaction log, you need to ensure that the entire chain of transaction logs has been located and verified.

The complete syntax to restore a transaction log is as follows:

 RESTORE LOG { database_name | @database_name_var }      [ <file_or_filegroup_or_pages> [ ,...n ] ] [ FROM <backup_device> [ ,...n ] ] [ WITH    [ { CHECKSUM | NO_CHECKSUM } ]    [ [ , ] { STOP_ON_ERROR | CONTINUE_AFTER_ERROR } ]    [ [ , ] FILE = { backup_set_file_number | @backup_set_file_number } ]    [ [ , ] KEEP_REPLICATION ]    [ [ , ] MEDIANAME = { media_name | @media_name_variable } ]    [ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable }      ]    [ [ , ] MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name' ]                 [ ,...n ]    [ [ , ] PASSWORD = { password | @password_variable } ]    [ [ , ] { RECOVERY | NORECOVERY | STANDBY =           {standby_file_name | @standby_file_name_var } }    ]    [ [ , ] REPLACE ]    [ [ , ] RESTART ]    [ [ , ] RESTRICTED_USER ]    [ [ , ] { REWIND | NOREWIND } ]    [ [ , ] { UNLOAD | NOUNLOAD } ]    [ [ , ] STATS [=percentage ] ]    [ [ , ] { STOPAT = { date_time | @date_time_var }     |  STOPATMARK = { 'mark_name' | 'lsn:lsn_number' }               [ AFTER datetime ]     |  STOPBEFOREMARK = { 'mark_name' | 'lsn:lsn_number' }              [ AFTER datetime ]    } ] ] [;]

The STOPAT option allows you to stop at a particular date/time. The STOPATMARK and STOPBEFOREMARK options allow you stop at or before a marked transaction or a log sequence number (LSN).

Note 

The STOPATMARK and STOPBEFOREMARK options are available only in SIMPLE and BULK_LOGGED recovery modes.

 <backup_device> ::= {    { logical_backup_device_name |             @logical_backup_device_name_var }    | { DISK | TAPE } = { 'physical_backup_device_name' |               @physical_backup_device_name_var } } <file_or_filegroup_or_pages> ::= {    FILE = { logical_file_name_in_backup | @logical_file_name_in_backup_var }    | FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var } }       | PAGE = 'file:page [ ,...n ]' }

Database Snapshot Restore

As discussed earlier, SQL Server 2005 has a new ability to revert a database to a particular point in time from a particular database snapshot. When reverting to a database snapshot, you need to perform the following actions:

1. Identify the database snapshot you plan to use.

2. Drop all other database snapshots.

3. Revert the database to a database snapshot.

4. Start the database.

The complete syntax to revert a database to a database snapshot is as follows:

 RESTORE DATABASE { database_name | @database_name_var } FROM DATABASE_SNAPSHOT = database_snapshot_name

The following example shows the AdventureWorks database being reverted to the database snapshot that was shown as an example earlier:

 USE master ; GO RESTORE DATABASE AdventureWorks FROM DATABASE_SNAPSHOT = AdventureWorks_20071502 ; GO

Since database snapshots are new to SQL Server 2005, Exercise 3.1 will show how to revert to a database snapshot.

Exercise 3.1: Reverting to Database Snapshots

In this exercise, you’ll examine how to revert to a database snapshot. You will create a database snapshot and then “accidentally” delete some data. You’ll then use the database snapshot to recover the deleted data.

This exercise assumes you are using the Developer or Enterprise Edition of SQL Server 2005 and that your computer is using the NTFS file system on the C: drive.

1. Open SQL Server Management Studio, and connect to your SQL Server 2005 instance using Windows authentication.

2. Click the New Query toolbar button to open a new query window.

3. Create a database snapshot by entering the following T-SQL code and executing it:

    USE master ; GO -- Create the database snapshot CREATE DATABASE AdventureWorks_Platypus ON ( NAME = 'AdventureWorks_Data', FILENAME = 'C:\Platypus.mdf') AS SNAPSHOT OF AdventureWorks ; GO

4. Right-click the Database Snapshots folder in Object Explorer, and click Refresh.

5. Expand the Database Snapshots folder. You should see the database snapshot, as shown here.

   

6. Simulate an accidental data modification by entering the following T-SQL code and executing it:

    USE AdventureWorks ; GO -- Modify everyone's surname UPDATE Person.Contact SET LastName = 'Isakov' ; GO -- 19972 rows should be affected.

7. Verify that the data has been modified by entering the following T-SQL code and executing it:

    USE AdventureWorks ; Go SELECT * FROM Person.Contact ; GO

8. Revert the database to the database snapshot by entering the following T-SQL code and executing it:

    USE master ; GO RESTORE DATABASE AdventureWorks FROM DATABASE_SNAPSHOT = AdventureWorks_Platypus ; GO

9. Verify that the data has been recovered by entering the following T-SQL code and executing it:

    USE AdventureWorks ; GO SELECT * FROM Person.Contact ; GO

10. Delete the database snapshot by entering the following T-SQL code and executing it:

     USE master ; GO DROP DATABASE AdventureWorks_Platypus ; GO

Configuring Logins

Restoring a user database does not automatically restore any external database dependencies, such as error messages or logins. So, for example, if you restore a database backup to a new SQL Server 2005 instance, any logins that are used by database users to access the database will not be automatically regenerated. In this instance, you will have to add and configure the “orphaned” logins.

You can use the CREATE LOGIN statement to create both SQL- and Windows-based logins. Windows-based logins are easy to transfer, but SQL-based logins are harder because you cannot read the password so as to re-create it.

But you do not need to do this. All you need to do is read the hashed value and create it as is on the second SQL Server 2005 instance using the HASHED option.

The following example shows a login being created with a hashed password:

 USE master ; GO CREATE LOGIN IrisKlesing WITH PASSWORD = 'daeh85evigtnac361noiram' HASHED ; GO

Note 

For more information about how to transfer logins and their passwords between instances of SQL Server 2005, read the “How to transfer the logins and the passwords between instances of SQL Server 2005” Knowledge Base article located at http://support.microsoft.com/kb/918992/.

Recovering Lost Data

Once data is lost, it is…lost. Consequently, you need a robust, reliable backup strategy no matter how you have configured your SQL Server 2005 instance, how reliable you think your hardware is, or what your choice is for a high-availability solution. It also highlights the potential need for a longer retention period for your database solution. We have seen a trend globally of governmental agencies coming in and stipulating all sorts of data retention requirements. In certain SQL Server 2005 solutions, paper trails are also required so as to be able to recover data that is lost. Invariably, it’s more about understanding the limitations of the technology and designing appropriate processes to overcome these limitations.

Maintaining Server and Database Scripts for Recoverability

It is important to maintain SQL Server and database scripts for recoverability reasons. This will enable you to apply any changes made to your SQL Server 2005 instance or databases after the latest backup. Additionally, it will enable you to troubleshoot a myriad of performance and other problems. This should be part of your change management strategy discussed earlier in the chapter.

Although SQL Server 2005 does not automatically maintain scripts when modifications are made to the SQL Server database engine and databases, the SSMS environment lets you easily script any action to a Transact-SQL file, as shown in Figure 3.11.

Figure 3.11: Scripting DBA changes

This makes it easier to maintain a change management system. We typically find that the implementation of such as system has more to do with changing the culture than any technical difficulty or lack of available tools.