Restoring transaction logs...

[bessebo]

I am testing trying to restore a transaction log backup in our test environment. When I attempt to perform the restore of the transaction log backup I get the following message:

"The backup set in file "filename" was created by BACKUP LOG and cannot be used for this restore operation."

I think it is because when I restored the full database backup I did not specify the NORECOVERY option. Is this possible? I am using SQL2000, SP3. If this is the problem is there a way to set the database in a NORECOVERY state after the fact or will I have to restore the full database backup again and specify the NORECOVERY option?

Or could it be another problem? I have just begun the restore again with the NORECOVEY option so I will find out whether that was the problem or not..

Thanks,
Bessebo

 

[ptheriault]

You must leave the database in NORECOVERY state to be able to apply logs. So yes this is your problem.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[bessebo]

Thanks Paul,
I am about to apply the transaction logs now that the database has been restored again with the NORECOVERY option.

Regards,
Bob

 

[bessebo]

Hi Paul,
That worked like a charm but it took a while because we perform our transaction log backups every 15 minutes so there were many backup sets in that one device. So there were 4 each hour and I restored up until noon from 6:00 in the morning. I did use the GUI instead of Query Analyzer. Since there were multiple backup sets included in the backup device since we do a backup log WITH NOINIT I had to restore them one by one. Any way to make this go faster?
I guess we could perform differential backups during the day and restore the differential backup and then the transaction log backups up to the point in time the issue occurred.
Any suggestions on making the restore of the transaction logs quicker?

Thanks,
Bessebo

 

[ptheriault]

There is a way through the GUI that you can apply all the logs up to a point in time with one restore operation. I haven't done it in awhile so I forgot how to do it. But I know it can be done.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[bessebo]

I did see the one point in time option there. I will have to test that one out...

Thanks,
Bessebo