How to restore transaction log sql server 2005

ljhodgett used Ask the Experts™

I'm trying to implement a backup strategy by taking a full backup every evening at midnight then doing a transaction log backup every hour. I'm using the following code: -

--full backup
 TO  DISK = '\\Sql1\testdb\testdb.Bak'

--transaction log backup
BACKUP LOG testdb --database name
 TO  DISK = '\\Sql1\testdb\testdb.trn'

Both scripts run successfully but when I go to restore the database I get the error shown in the image. This occurs when I add both files to the "Specify the source and location of backup sets to restore"

What am I doing wrong please?

Many Thanks

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

The error message says that your transactional log backup (testdb.trn) is split across two or more media files in different locations and hence it needs all the transactional log backups in addition to E:\testdb\testdb.trn

I hope your transactional log backup is taken to two or more files instead of the single file specified above.

Specify those other testdb.trn files to restore.

Hope this helps
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:

FROM DISK = 'D: \BackupFiles\TestDatabaseFullBackup.bak'
MOVE 'PreviousDatabase' TO 'D:\DataFiles \TestDatabase.mdf',
MOVE 'PreviousDatabase_log' TO 'D:\DataFiles \TestDatabase_Log.ldf',

The code specifies that the location of the full backup file is on your servers 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.

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

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

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

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

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.

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.

For more details, see this link:

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial