0

I've got a team recovering an MS SQL Server database. This database is live and currently working with a live website.

An issue occurred (data was deleted) where we needed to restore a backup copy of the data without overriding the current database, in order to retrieve the missing data.

Currently, our website is down with the message:

Cannot open database "{database_name}" requested by the login. The login failed. Login failed for user '{user_name}'

This is because the restoration process requires Exclusive Access the to live database and renders all other access denied.

I haven't worked in database backup/recovery in years and don't remember, and can't find relevant information.

  • Is this normal, as in, is this how every restore is done?
  • Is the restore causing this Exclusive Access because of how the backup was taken or setup?
  • Are there backup/restore methods which could be used which will not required this Exclusive Access, and what are they?

I may have more questions but this is it for now. I need to learn and understand what is possible and compare that with what my DBA Team is doing and why.

Thank you for all your help.

  • Using the built in tools require exclusive access for restores and that way to ensure you are restoring to a know point in time. If you are trying to restore an oops delete, restoring the database with another name and file set should allow you to recover the deleted data and reinsert it to the production database without needing exclusive access in the production version. If you are restoring a backup to your existing db with the same name and files you are restoring the entire thing to that point in time. Won’t help now but look at LiteSpeed by Quest that can do object restores. – Aaron Nov 3 '17 at 2:05
  • If they are restoring over the existing production db I hope the did a backup of it before hand as the point in time restore will put the entire db back to that point in time – Aaron Nov 3 '17 at 2:07
  • Thanks @Aaron I just found out the DBAs did in fact restore the live DB instead of making a copy as I requested. #NotHappy – RoLYroLLs Nov 3 '17 at 2:07
  • Ugh good luck. Hope you are able to find a backup from today before the rollback – Aaron Nov 3 '17 at 3:15
  • Thanks @Aaron looks like there wasn’t and data was lost. Smh. Please add an answer to describe how restore works (can work) so I can give you credit. – RoLYroLLs Nov 3 '17 at 12:40
1

As far as I know there is no way with SQL Server to keep the database online while it’s restoring. Restores are a point in time version of the database and without having exclusive access to perform the restore it can’t guarantee the state of the data if data was to get changed mid restore.

In the case of oops deletes and not a corrupted database. If you have the space to do a full restore you can restore the needed database with a different name and file name(s) which will allow you to query the needed data and reinsert or update the live database.

There is a product called LiteSpeed from Quest that I use (not connected to them) to perform backups and compression that will allow you to read the backup files without having to do a real restore. You will then be able to query the tables in the backup and store the results into temp tables in another database on the server. This option requires that you have backed up your database with the software so some advanced DR planning is required.

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.