As a SQL Server DBA, you may have encountered the error message related to exclusive access when trying to restore a database. In this blog post, we will explore various options available to fix this issue and discuss the most common approach used to refresh a development or QA server from a production server.

The Error Message

Let’s start by reproducing the error message:

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

When executing the above code, you may encounter an error indicating that exclusive access to the database cannot be obtained. This error occurs when there are active connections to the database you are trying to restore.

Identifying Active Connections

To identify who is using the database that we are trying to restore, we can use the sys.dm_exec_sessions system view:

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')

This query will provide information about the active sessions connected to the database. By analyzing the results, you can determine which connections need to be terminated or disconnected before proceeding with the restore operation.

Default Database for Login

Another factor that can cause the exclusive access error is if the database you are trying to restore is set as the default database for the login you are using. To verify the default database for a login, you can run the following query:

SELECT loginname, dbname
FROM sys.syslogins
WHERE dbname = 'SQLAuthority'

If the database is set as the default for the login, you can change the default database or use a different login to perform the restore operation.

Options to Obtain Exclusive Access

If you encounter the exclusive access error, there are several options available to obtain exclusive access:

1. Take the database offline: By taking the database offline, all connections to the database will be dropped, allowing you to perform the restore operation. However, keep in mind that this will affect any other processes or applications relying on the database.
2. Detach the database: Detaching the database will make the data and transaction log files available on the same location. This allows you to restore the database using the same files. If you need to restore the database to different files, you will need to delete the existing files before detaching.
3. Drop the database: Dropping the database permanently removes it from the server, including all its files and data. This option should be used with caution as it cannot be undone without a previous backup.

Each of these options has its own implications and should be chosen based on the specific requirements and constraints of your environment.

Refreshing Development/QA Server from Production Server

Now, let’s discuss the most common approach used to refresh a development or QA server from a production server. The preferred method is to take a backup of the production database and restore it on the development/QA server. This ensures that the data and schema are consistent between the two environments.

Here is a high-level overview of the process:

1. Perform a full backup of the production database.
2. Transfer the backup file to the development/QA server.
3. Restore the backup file on the development/QA server, ensuring that exclusive access is obtained.

By following this approach, you can ensure that the development/QA environment accurately reflects the production environment, allowing for effective testing and development.

In conclusion, when encountering the exclusive access error during a database restore, it is important to identify and terminate any active connections to the database. Additionally, understanding the available options to obtain exclusive access can help you overcome this issue. Finally, when refreshing a development/QA server from a production server, taking a backup and restoring it on the target server is the most common and recommended approach.