1

The image below says it all. I need to get some data out of a back-up that's two weeks old, so I restored to a new db called ETLFix. However, when I started the restore my production DB, ETL went into restore as well.

I suspect this because I restored ETLFix from a two week old .bak and an notification went off saying that the back-up chain is broken for ETL. Another way to say it is, ELT thinks it's in restore because it learned an old .bak was read.

How to I get my ETL production db out of restore, and keep this from happening in the future?

BothDbsInRestore

3

Try this statement:

RESTORE DATABASE ETL WITH RECOVERY

Since this is your prod environment, be very careful in doing your due diligence to be sure you're doing no harm that cannot easily be fixed.

Also, check the Options tab to see if "Take a tail-log backup before restore" is checked. If it's checked, then un-check it. Starting in 2016 (I think), if you're restoring over an existing database, then SSMS defaults to taking a tail log backup. In my quick test just now (restoring to a new name from a backup of an existing database), SSMS still defaults to take a tail-log backup prior to restore. Get in the habit of always checking the Options tab.

Hope that helps.

  • That is most likely what is happening. Very scary if you don't know how to handle it and why it happens. One can consider it a bug, that if you do a "test-restore" you make the production database inaccessible. There is a comment/alert on the first place in the restore dialog that there will be a tail log backup. IMO, when you type something on the name for the restored database, this option should be unchecked (similar to how the filenames are changed). – Tibor Karaszi Dec 3 at 7:39
1

Try running these two queries:

SELECT MAX(restore_date) AS restore_date FROM msdb..restorehistory WHERE destination_database_name = 'ETLFix'

SELECT [database_name], backup_start_date FROM msdb..backupset WHERE database_name = 'ETL' AND type = 'L' AND is_force_offline = 1

If the second query returns a row with a backup_start_date immediately before the restore_date value from the first query this indicates a Tail-Log Backup was performed, most likely caused by not unchecking the option in the GUI. This is what caused your database to go offline.

To bring it online, run:

RESTORE DATABASE ETL WITH RECOVERY

Going forward, you should try and use a script to restore your databases instead of the GUI to ensure you're setting the various settings to exactly what you want, or you can use the GUI, but script out the action first and review the script generated by SSMS to ensure it is performing the correct actions you want.

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.