I tried to simulate this on my test machine which had log shipping configured. Below are the steps which I followed -
1. Deleted the TUF file which was available in the secondary server.
2. The delete operation was successful.
3. Checked log shipping status and found that the health is ‘Good’
4. Both primary and secondary databases are synced and both have got same set of data. Row by row,Col by Col.
Note - Ideally deleting the TUF file should also cause issues to log shipping secondary restores, however my simulation did not faced that behavior.
All looks good, and you might be wondering that deleting a TUF file is easy and it’s not going to hurt me much!!!
Now, let’s assume that we lost our primary database server due to Memory burn(Short circuit) and we are in need of the Secondary database.
The RTO and RPO matrix is quite okay and we are allowed to bring the secondary database up within 30 minutes. Walk in the park right? We just have to bring the database up, the users/jobs/other objects are already taken care and just the database needs to be up.
Let’s write this simple 6 word TSQL to bring our database up.
RESTORE DATABASE [Test] WITH RECOVERY
Test is my test database which is available in
the secondary server and its primary copy was the one which was residing
on the server which just went for a trip(Memory burn!)As soon as we execute this command with a big smile assuming that the database will be up, we will get this message -
Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally. Msg 3441, Level 17, State 1, Line 1 During startup of warm standby database ‘Test’ (database ID 6), its standby file (‘C:\Program Files\Microsoft SQL Server\MSSQL11.SERVER2012B\MSSQL\DATA\Test_20120112191506.tuf’) was inaccessible to the RESTORE statement. The operating system error was ’2(The system cannot find the file specified.)’. Diagnose the operating system error, correct the problem, and retry startup.
What does it mean – It simply means that you have done a good job by deleting the TUF file and now please bring it back.
TUF file is required for the Stand by database to recover and we will not be able to bring the database up without the same.
As the simulation was in a very controlled environment, I brought back the TUF file and ran the restore command once again.
RESTORE DATABASE [Test] WITH RECOVERY
RESTORE DATABASE successfully processed 0 pages in 0.908 seconds (0.000 MB/sec).The database was recovered and was accepting new connections.
Conclusion – TUF file is a very important part of recovery of a stand by database and we have to educate server ops team or anyone who is responsible for cleaning up files and make sure that this is un-touched.
Do you have any ways to recover a stand by database in log shipping secondary without TUF file.If Yes,then please share your experience in the comments section of this post.
Thanks for reading.
