1

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?

  • Unfortunately, in current circumstances, imho, it will be not possible to restore a database to a state more recent than that four month old backup. There is only one hope that that backup is not initialized every day and it contains more than one backup set – Alexandr Volok Jan 17 at 20:05
  • 1
    Can you execute: “restore headeronly from disk=‘path-to-backup’”. How many rows it returns? – Alexandr Volok Jan 17 at 20:08
2

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.

  • OK, I understand that. My concern is what happened with the mdf and ldf files that were in D:\db folder - it's empty now? Are they deleted? Can they be somehow recoved? – Petar Slavov Jan 17 at 20:14
  • The restore command will delete the database before it creates the one to restore into (and for that you specified the files to be in the Program Files folder - see the MOVE options for the RESTORE DATABASE command. So, the "old" database files are gone (assuming the files you referred to are those that were used by the database that was overwritten by the RESTORE DATABASE command, of course). – Tibor Karaszi Jan 17 at 20:44
  • Gone means deleted, if so they can be recovered with a recovery tool? I'm running one now, but it's taking too long to scan the drive. I'll wait and report on the results. – Petar Slavov Jan 17 at 21:24
1

I understand that you are not a DBA but like most of us we end up working on projects outside our domain. With that being said; caution should be your number one friend when working in unfamiliar terrorities. Backing up should be your next best friend in such situations. Anyways the harm is already done here and to be honest with you; it will be impossible to get back all the deleted objects if you do not have the correct backup set/chain. You mentioned you are using a third party database repair tool. I know of one if thats not what you already using called Stellar Database Recovery Toolkit (https://www.stellarinfo.com/sql-recovery.php?gclid=EAIaIQobChMIzci5z4_W4AIVUNbACh1N8ARVEAAYAiAAEgIwCvD_BwE). This can repair corrupted databases and even recovery deleted database objects. The best part is the free trial version has all the features except saving the repaired file. So this is the trick, use the trial and see if the results of recovery is what you looking for. If it is, then viola purchase a full edition with guarantee that you will have your database recovered.

Best of luck and let me know how it goes!

0

You can use the script below to get the backup report.It will help you to locate all the backup. Can you share the result of the query

SELECT msdb.dbo.backupset.database_name,msdb.dbo.backupset.backup_start_date,msdb.dbo.backupset.backup_finish_date,
    msdb.dbo.backupset.expiration_date,
    CASE msdb..backupset.type
    WHEN 'D' THEN 'Database'
    WHEN 'L' THEN 'Log'
    WHEN 'I' THEN 'Differential'
    END AS
    backup_type,msdb.dbo.backupset.backup_size,msdb.dbo.backupmediafamily.logical_device_name,
    msdb.dbo.backupmediafamily.physical_device_name,msdb.dbo.backupset.name ASbackupset_name,
    msdb.dbo.backupset.description
    FROM msdb.dbo.backupmediafamily
    INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id =
    msdb.dbo.backupset.media_set_id
    WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date,102)) >=cast(GETDATE()-150 AS date)
  • The result set is 230 rows, there are other database there that have been backed up daily. But the last result for mydb is from the same date as the backup that I restored. – Petar Slavov Jan 17 at 21:32
  • If you don't have any log or differential backups after the full backup then as Tibor said you need to restore the full backup with RECOVERY option. Are there any Log or Diff backups? – user1716729 Jan 17 at 21:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.