Del via

Case of Transaction Log backups not getting restored

  • Artikel

I got a call from a DBA late night waking me up from deep slumber. They were restoring a critical database from backup and were getting the below error -

 

Msg 3013, Level 16, State 1, Line 2

RESTORE LOG is terminating abnormally.

Msg 3441, Level 17, State 1, Line 2

During startup of warm standby database 'TestDB' (database ID 5), its standby file

  ('Q:\mssql\backup\TestDB\TestDB_20150419043800.trn') was inaccessible to the RESTORE statement.

The operating system error was '32(The process cannot access the file because it is being used by another process.)'.

Diagnose the operating system error, correct the problem, and retry startup.

 

If you look carefully at the error message – its saying that the standby file which it is looking for is “TestDB_20150419043800.trn”.

Strange!...This is the same backup file we are trying to restore using the below command -

restore log TestDB from disk = 'Q:\mssql\backup\TestDB\TestDB _20150419043800.trn' with recovery

How can this happen?

 

I suspected that this could be a problem with the restoration command used to put the database in standby. I asked the DBA, if he had the command which was used to put the database in standby mode. Unfortunately, the answer was a No. How could I confirm.

Ok, no problem. I asked him to send me the Default traces since I knew we track restore statements in the default trace file.

Hoping to find something useful, I opened the default trace and guess what I found –

 

Audit Backup/Restore Event       

Microsoft SQL Server Management Studio - Query             

2 - Restore        

restore log TestDB from disk = 'Q:\mssql\backup\TestDB\TestDB_20150419043800.trn' with standby = 'Q:\mssql\backup\TestDB\TestDB_20150419043800.trn'

 

Looks like the DBA had restored the previous transaction log file with standby but by mistake specified the same transaction log backup file in the standby clause. It did not give an error but effectively what happened was that the .trn file was having the standby information.

Thinking that there was some corruption with the transaction log backup file, he had gone ahead and replaced the log backup file (which now had the standby information) with the original transaction log backup file. By doing so, we lost the standby information and we could not restore further backups nor recover the database since it was looking for the standby file which was no longer present.

In this case, the only option to get out of this situation was to restore the database from Full backup and apply the log backups.

 

Moral of the story – Database restore sequences are very sensitive, be very careful.

Disclaimer: The information in this weblog is provided "AS IS" with no warranties, and confers no rights. This weblog does not represent the thoughts, intentions, plans or strategies of my employer. It is solely my opinion. Inappropriate comments will be deleted at the authors discretion. All code samples are provided "AS IS" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular.