Tip

How to restore from a transaction log in SQL Server

Greg Robidoux, Edgewood Solutions

 
Having a backup plan in place is half the battle in keeping your SQL Server up and running. The other half involves the restore process. You need to restore SQL Server after a failure (when you need to restore to a standby server) or when refreshing a

    Requires Free Membership to View

reporting or development environment. Each of these scenarios can involve restoring transaction log backups.

The challenge that transaction log backups introduce is that they require you to restore multiple files rather than just one large full backup file. In order to successfully restore the database, you must have all of the transaction log backups that were created and they must be restored in the order they were created. If any transaction log backups are corrupt, you cannot restore any transaction log backups after the corrupt one. They need to be restored in order, and you can't skip any of the files.

Here we will take a closer look at knowing what to restore and how to restore transaction log backups.

TABLE OF CONTENTS
   Step 1: Back up the active portion of the transaction log
   Step 2: Identify what needs to be restored
   Step 3: Validate that backups have the correct contents
   Step 4: Know your restore options
   Step 5: Take advantage of point-in-time recovery

 
Back up the active portion of the transaction log

In the event of a failure and you need to restore to another server, you should first back up the active portion of the transaction log to get the remaining transactions that have not yet been backed up to an existing transaction log backup. To create this last transaction log backup, use something similar to the following command:

BACKUP LOG Northwind TO DISK= 'C:\SQL\Backup\North.bak' WITH NO_TRUNCATE

This creates another transaction log backup that will then be used in your restore process.
 
Identify what needs to be restored

The next step is to determine which files need to be restored. Hopefully you already know which backups need to be restored. If not, you can query the SQL Server tables in the msdb; these tables will show you all the backups issued on your server, including backups created using Maintenance Plans, the wizard in Enterprise Manager, T-SQL commands and third-party tools that use the built-in SQL Server functions to issue backups.

The tables consist of the following in the msdb:

  • backupfile - Contains one row for each data or log file that is backed up
  • backupmediafamily - Contains one row for each media family
  • backupmediaset - Contains one row for each backup media set
  • backupset - Contains a row for each backup set

If you are doing a complete restore, you need to begin with a Full Backup and then include differentials and any transaction log backups after the differentials. The data in these tables shows the oldest backups first, so look for the latest Full Backup and any other backups that were issued after the Full Backup.
 
Validate that backups have the correct contents

In addition to the RESTORE command to restore backups, there are other RESTORE commands that let you see the contents of the backup files. These commands are RESTORE HEADERONLY and RESTORE FILELISTONLY.

RESTORE HEADERONLY

This command allows you to see the backup header information for all backup sets on a particular backup device. This command is useful when you have multiple files to work with or if you get a backup file from a server that you do not manage. To determine what is stored on the backup files, you can run this command in Query Analyzer.

RESTORE FILELISTONLY

This command allows you to see a list of the database and log files contained in the backup set, which filegroup they are in and also the size of the data and log files. The LogicalName and PhysicalName are key data elements that are used during the restore process.

RESTORE FILELISTONLY FROM DISK = 'C:\SQL\Backup\North.bak'
 
Know your restore options

There are several options to use when restoring backups, including transaction log backups. The following are some of those options:

NORECOVERY

This option allows you to restore additional backup files. You can use it when restoring Full, Differential or Transaction Log backups.

 RESTORE DATABASE NORTH FROM DISK = 'C:\SQL\Backup\North.bak' WITH NORECOVERY

RECOVERY

This is the default if no option is specified. This option would be used on the last restore issued. Once used, you cannot restore additional backups; to do so you must start the restore process all over again. This option can be used when restoring Full, Differential or Transaction Log backups.

 RESTORE LOG NORTH FROM DISK = 'C:\SQL\Backup\North_Log.bak' WITH RECOVERY

STANDBY

This option allows you to put the database in a read-only mode, but it still allows additional transaction logs to be restored. It can be used when restoring Full, Differential or Transaction Log backups.

 RESTORE LOG NORTH FROM DISK = 'C:\SQL\Backup\North_Log.bak' WITH STANDBY = 'c:\undo.ldf'

MOVE

When restoring databases to a different server, you will probably need to use the MOVE option unless the servers are set up the same way. Above I mentioned using the LogicalName and PhysicalName from the RESTORE FILELISTONLY command. The MOVE option lets you move the physical files to a different location on your server. You will need to use the MOVE option for all of your restores including Full, Differential and Transaction Log.

 RESTORE LOG NORTH FROM DISK = 'C:\SQL\Backup\North_Log.bak' WITH RECOVERY, MOVE 'Northwind_Data' TO 'c:\data\Northwind.mdf', MOVE 'Northwind_Log' TO 'c:\data\Northwind _log.ldf'

 
Take advantage of point-in-time recovery

In addition to doing a complete restore of the transaction log, SQL Server also has options to stop at a particular point in time or transaction mark. This way if you know when or where the database problem occurred, you can recover your database transactions to that particular point prior to the database problem you are trying to avoid. For example if someone deleted all records from a table, you might want to recover your database to the point in time prior to that operation.

STOPAT

This option allows you to recover all committed transactions that occurred up to Dec. 31, 2005; 12:00 AM.

 RESTORE LOG Northwind FROM DISK = 'C:\SQL\Backup\North_Log.bak' WITH RECOVERY, STOPAT = 'Dec 31, 2005 12:00 AM'

The next two commands allow you to restore your transactions using transaction marks, which must be named transactions used within your application. If you are not using named transactions, this option will not work.

STOPATMARK

With this option, you can recover all committed transactions that occurred up to transaction mark Invoice1024.

 RESTORE LOG Northwind FROM DISK = 'C:\SQL\Backup\North_Log.bak' WITH RECOVERY, STOPATMARK = 'Invoice1024'

STOPBEFOREMARK

This option allows you to recover all committed transactions that occurred prior to transaction mark Invoice1024.

 RESTORE LOG Northwind FROM DISK = 'C:\SQL\Backup\North_Log.bak' WITH RECOVERY, STOPBEFOREMARK = 'Invoice1024'

As you can see, several restore options may be used for all of your backup files and certain options are only available for transaction log backups. Take the time to learn about the different options and when you might use one over another. Also, make sure you know where your backups are being stored and try to use a naming convention that makes it easy to determine what the contents are in the backup files. The RESTORE HEADERONLY and RESTORE FILELISTONLY are great tools to help you figure this out, but using a good naming convention makes this step a lot faster and more reliable.

ABOUT THE AUTHOR
Greg Robidoux is the president and founder of Edgewood Solutions LLC, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and has delivered presentations at regional SQL Server users' groups and national SQL Server events. Robidoux, who also serves as the SearchSQLServer.com Backup and Recovery expert, welcomes your questions.


More information from SearchSQLServer.com

 



This was first published in January 2006

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.