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?