Ygthkb

In a series of unfortunate mistakes, I managed to lose 4 months of data and rapid database modifications. I’m not a database administrator, I’m a programmer that also designs and maintains a database.
I initially 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 attention. So I generated a script to execute directly into SSMS. I again failed to notice that the script included all objects, not just the selected. I realized that too late, some of the destination's database tables, views, functions, etc. were dropped.

And then it got worse. I know we have daily backup and decided to restore for it. It turns out that for some reason, this database, on that server has its last backup from 4 months ago. I decided that I’ll restore from it and 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 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 FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLBackupmydb_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:Backupsmydb_2018_08_30_19_01_01mydb.bak' WITH  FILE = 1,  MOVE N'exact_Data' TO N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAmydb.mdf',  MOVE N'exact_Log' TO N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAmydb.ldf',  NORECOVERY,  NOUNLOAD,  STATS = 5

RESTORE LOG [mydb] FROM  DISK = N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLBackupmydb_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 FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATA. 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 one more unconvenient thing.




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

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.

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)

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!