Disaster Recovery

Restore your SQL Server database using transaction logs


Most DBAs dread hearing that they need to restore a database to a point in time, especially if the database is a production database. However, knowing how to do this is of the utmost importance for a DBA's skill set. I'll walk you through the steps of how to restore a SQL Server database to a point in time to recover a data table.

The scenario

A coworker calls you in a panic because he accidentally deleted some production data, and he wants you to restore the lost records.

If you are lucky, you have a data auditing system in place, and you can restore these records from an audit table. If you do not have a tool that will read a transaction log so that you can to undo transactions, you will likely need to restore the altered database to a certain point in time on the same or separate server than the server hosting the current database instance.

The restoration process

Note that, for the purpose of this article, I am assuming that your database recovery mode is set to FULL.

The first step in the process is to perform a tail-log backup. You want to perform this type of backup before a database restore to ensure that any records that have changed since the last backup are available to be included in the restore process.

Next you should locate where the database backup files are stored on the machine or the network. It may be a good idea to copy these files to your target server if you are going to be restoring the database on a different server. In the backup file location, find the very last full database backup that was completed (these files usually end with the extension .bak); you need to restore this full backup. The script below applies the full backup file to the NewDatabase database:

RESTORE DATABASE NewDatabase

FROM DISK = 'D: \BackupFiles\TestDatabaseFullBackup.bak'

WITH

MOVE 'PreviousDatabase' TO 'D:\DataFiles \TestDatabase.mdf',

MOVE 'PreviousDatabase_log' TO 'D:\DataFiles \TestDatabase_Log.ldf',

NORECOVERY

The code specifies that the location of the full backup file is on your server's D drive and that you are restoring the file to the database named NewDatabase. The statement moves the data file and the log file from the full backup to new files for my TestDatabase database. The last statement in the script, NORECOVERY, is very crucial. The NORECOVERY mode is one of three available options, which are outlined below.

  • NORECOVERY: Tells SQL Server that you are not finished restoring the database and that subsequent restore files will occur. While the database is in this state, the database is not yet available, so no connections are allowed.
  • RECOVERY: Tells SQL Server that you are finished restoring the database, and it is ready to be used. This is the default option, and it is by far the one that is used most often.
  • STANDBY: Tells SQL Server that the current database is not yet ready to be fully recovered and that subsequent log files can be applied to the restore. You can use this option so that connections are available to the restore database if necessary. However, future transaction logs can only be applied to the database if no current connections exist.

Once you restore the full backup using the NORECOVERY option, you can begin applying the transaction log backups or the differential backup.

Differential backup

A differential backup is a backup of any changes to the database that have occurred since the last full database backup. If you have multiple differential backups, you will only need to restore the very last one taken. In this situation, there are no differential backups, so you can move directly to the transaction log backups.

Transaction log backups A transaction log backup keeps track of all transactions that have occurred since the last transaction log backup; it also allows you to restore your database to a point in time before a database error occurred. Transaction log backups occur in sequence, creating a chain. When restoring a sequence of transaction log backups to a point in time, it is required that the transaction log files are restored in order.

When you use a database maintenance plan to create the transaction log backups, a time indicator is typically included in the transaction log file name. The script below applies three transaction log backups using the NORECOVERY option, and the last statement restores the database to availability to the time frame at the very end of the last transaction log file.

RESTORE LOG NewDatabase

FROM DISK = ''D: \BackupFiles\TestDatabase_TransactionLogBackup1.trn'

WITH NORECOVERY

RESTORE LOG NewDatabase

FROM DISK = ''D: \BackupFiles\ TestDatabase_TransactionLogBackup2.trn'

WITH NORECOVERY

RESTORE LOG NewDatabase

FROM DISK = ''D: \BackupFiles\ TestDatabase_TransactionLogBackup3.trn'

WITH NORECOVERY

RESTORE LOG NewDatabase

FROM DISK = ''D: \BackupFiles\ TestDatabase_TransactionLogBackup4.trn'

WITH RECOVERY

Restoring to a point in time In the example above, you restore the database to the end of the last transaction log. If you want to recover your database to a specific point in time before the end of the transaction log, you must use the STOPAT option. The script below restores the fourth transaction log in the log sequence to 4:01 PM -- just before the database mishap occurred.

RESTORE LOG NewDatabase

FROM DISK = ''D: \BackupFiles\ TestDatabase_TransactionLogBackup4.trn'

WITH STOPAT = N'6/28/2007 4:01:45 PM', RECOVERY

Now that you have the database restore to a point where you need it to be, it is time to decide how to help the developers in order to make their situation a little bit easier. My suggestion is to copy the table the developers need to a separate table on the server so that you or they can correct the data problem.

Be prepared

Restoring your database to a point in time is one of those things that you never want to have to use, but you need to be able to complete it if necessary. I took an overview approach as to how to restore your SQL Server database using transaction logs for a certain circumstance. It's important to note that companies use different approaches for backing up data, so it is very important to be thoroughly involved in your company's database backup process. Be sure to test restores and restore situations often so that you are ready when a disaster does occur.

Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. He is also Microsoft certified in SQL Server 2000 and SQL Server 2005. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.

-----------------------------------------------------------------------------------------

Get SQL tips in your inbox

TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!

About

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.

9 comments
roy_arnab22
roy_arnab22

Hi , I'm a new bee to SQL and trying to design a backup plan for my client. The DB is on sql 2000 and can any1 plz tell me how to set a proper backup plan. Currently I'm running a maintenance plan for daily full backup and scheduled deleting of the the backup after 5 days. I'm also maintaining the time stamp as per clients requirement. All the backups are running on local disk. I've tried to schedule also the differential backup as the client is asking for it but it's not running and I found there is no option to auto delete the backups after particular time interval. When Should I use Transaction Log backup and why? the DB size is arround 650 MB and growing. Can any1 help....

SivaBlogs
SivaBlogs

@roy_arnab22Schedule full backup for every week. and differential backup in Every day Night. and Transaction log backup for Every 1hr. this Plan may vary depends on business requirement.

Editor's Picks