This error might have seen by DBAs and they know how to fix it. This blog goes in little details about what are the various options available. Here is the simple reproduction of the error message related to exclusive access.
USE master GO CREATE DATABASE SQLAuthority GO BACKUP DATABASE SQLAuthority TO DISK = 'SQLAuth.bak' GO -- at this point open another query window and connect to SQLAuthority RESTORE DATABASE SQLAuthority FROM DISK = 'SQLAuth.bak' GO
Here are the few things which we can check.
- Use sys.dm_exec_sessions to identify who is using the database which we are restoring.
SELECT session_id ,host_name ,program_name ,client_interface_name ,login_name ,STATUS ,is_user_process FROM sys.dm_exec_sessions WHERE database_id = db_id('SQLAuthority')
- The database that we are trying to restore should not be set as the default database for the login that we are using. We can verify the default database for a login by running the query-
SELECT loginname ,dbname FROM sys.syslogins WHERE dbname = 'SQLAuthority'
- If you want, you can take the database offline, which would drop all the connection. Then you can perform restore.
- We can also detach the database. By detaching, the data and transaction log files of the database will be available on the same location. If we have to restore the same database at same files when we need to delete the files.
- Another option would be to drop the database. Once dropped, its files and data are deleted from the disk on the server. When a database is dropped, it is permanently removed and cannot be retrieved without using a previous backup.
Which is the most common option you use to refresh development/QA server from production server?
Reference: Pinal Dave (http://blog.SQLAuthority.com)
6 Comments. Leave new
Putting database in single user mode with immediate rollback option….
What is someone else gets into database after you change to single user an run restore command?
Hey Pinal,
Idea is to put database in single user mode and start database restore within the same batch – Chances are very less that someone grasp the connection, If some one does, identify the user and ask him to close the connection. In another case, you can forcefully close the connection, it should be okay for non-prod servers.
Thank you for all your awesome blogs !!
Thanks Anil.
yea. that exactly how i run the script. all on the same batch. and it works fine.
Thanks for the confirmation Judah.