Sql-server – MS SQL Server Restore: Methods Without Exclusive Access

backuprestoresql serversql-server-2016

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.

Best Answer

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.

Related Question