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
[IMAGE] Step 1: Back up the active portion of the transaction log
[IMAGE] Step 2: Identify what needs to be restored
[IMAGE] Step 3: Validate that backups have the correct contents
[IMAGE] Step 4: Know your restore options
[IMAGE] Step 5: Take advantage of point-in-time recovery
[IMAGE][IMAGE] Back up the active portion of the transaction log[IMAGE] Return to Table of Contents
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 transactio...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
ns 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:
This creates another transaction log backup that will then be used in your restore process.
[IMAGE][IMAGE] Identify what needs to be restored[IMAGE] Return to Table of Contents
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.
[IMAGE][IMAGE] Validate that backups have the correct contents[IMAGE] Return to Table of 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'
[IMAGE][IMAGE] Know your restore options[IMAGE] Return to Table of Contents
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.
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.
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.
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.
[IMAGE][IMAGE] Take advantage of point-in-time recovery[IMAGE] Return to Table of Contents
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.
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.
STOPBEFOREMARK
This option allows you to recover all committed transactions that occurred prior to transaction mark 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
