Chapter 2: SQL ServerRestore Procedures <<previous|next>> :What's the best approach for restoring MSDB on another server?
SQL Server Backup and Recovery
How to restore from a transaction log in SQL Server
By 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 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
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
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.
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.
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.
There are several options to use when restoring backups, including transaction log backups. The following are some of those options:
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
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
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'
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'
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.
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.
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'
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
- Tip: Restoring a database from another SQL Server
- Step-by-Step Guide: How to properly back up a SQL Server
- Learning Center: Top 10 SQL Server tips of 2005
12 Jan 2006
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.