Recovering a SQL Server database

Question

In a series of unfortunate mistakes, I lost 4 months of data and all the rapid database modifications. I’m not a database administrator, I’m a programmer who also designs and happens to maintain a database.

Initially I just wanted to only compare some database objects to another database. I did that using OpenDBDiff tool. I selected the objects that I wanted to update and hit ‘Update selected’. It failed for some reason, but I didn’t pay any attention to it. So I generated a script to execute directly into SSMS. I again failed to notice that the script included all objects, and not just the selected ones. I realized too late, by then, some of the destination's database objects i.e. tables, views, functions, etc. were already dropped.

And then it got worse, I know we have daily backup and decided to restore from there. It turns out that for some reason, this database, on that server has its last backup 4 months ago and didn't run afterwards successfully. I decided that I’d restore from its transaction log to the current date. I still don’t know if that’s even possible, but I decided to do it on a live database. I did not take a new backup even at this point, I was afraid that this will destroy the transaction log. So I ran a command like this:

USE [master]
BACKUP LOG [mydb] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\mydb_LogBackup_2019-01-17_13-49-00.bak' WITH NOFORMAT, NOINIT,  NAME = N'mydb_LogBackup_2019-01-17_13-49-00', NOSKIP, NOREWIND, NOUNLOAD,  NORECOVERY ,  STATS = 5
RESTORE DATABASE [mydb] FROM  DISK = N'D:\Backups\mydb_2018_08_30_19_01_01\mydb.bak' WITH  FILE = 1,  MOVE N'exact_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mydb.mdf',  MOVE N'exact_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mydb.ldf',  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [mydb] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\mydb_LogBackup_2019-01-17_13-49-00.bak' WITH  NOUNLOAD,  STATS = 5,  STOPAT = N'2019-01-17T13:00:00'
GO

The log backup failed. The database is now in restoring state. Before all this, mydb.mdf and mydb.ldf files where located in the D:\db folder, now they are not there. Instead they are in C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\. I tried to attach them to my local server, it fails because they are in restoring state too. I download and managed to open the with a SQL Server database repair tool, but the only hold the data from 4 months ago.

I’m currently scanning the computer for deleted files to search for the original .mdf and .ldf files, but I don’t have much hope on this because the quick scan didn’t show anything and I’m running a deep scan now.

Questions

• How can I take the database out of that (restoring…) state and what will happen if I do so? This is why I decided to ask before I do anything else that might be destructive.

• My goal now is to restore the database in the most recent possible state, so how can I achieve that?

Answer 1

The last successful restore was the RESTORE DATABASE. Since you specified NORECOVERY, the database is, as expected, in restoring state. If you don't have any more backups to restore and just want to get out of the restoring state, you just do:

RESTORE DATABASE mydb WITH RECOVERY

That's it. You find the database as it were when that full backup you restored was produced.

As for log backups, you need an unbroken chain of log backups. You can't skip a log backup, like you most probably tried to do. You likely got an error message saying that the log backup was too recent to restore, citing LSN numbers and that it requires some earlier log backup with some earlier LSN number. There's no way around the "unbroken chain of log backups" requirement. If you can't find those log backups, or a more recent full backup, then you are probably stuck with a 4 month old database.