Important Note
The sp_KillDatabaseUsers stored procedure uses the sys.dm_tran_locks Dynamic Management View (DMV) available in SQL Server 2005 and SQL Server 2008. In order for the user to query the Dynamic Management Views, a user requires specific permissions. To view the DMV the user must be granted VIEW SERVER STATE on the server, this can be done by running the following query as a sysadmin.
USE master
GO
GRANT VIEW SERVER STATE TO DBUSER;
You must also grant EXECUTE Permissions for the sp_KillDatabaseUsers stored procedure created in the MASTER database. This can be done by running the following query as a sysadmin:
USE master
Go
GRANT EXECUTE ON sp_KillDatabaseUsers TO DBUSER;
Once connected database users are identified then they are killed using the KILL command one by one, thereby disconnecting all the users in the database and finally the database is restored.
Detaching Databases Using SQL Server Management Studio to Kill Connected Users
A DBA can also KILL the users connected to a database by Detaching the Database. This can be done by right clicking Database | Tasks | Detach…. Check the Drop Connections check box and click OK to close the existing connections. In this process the database is put into single user mode and finally the database is taken offline.
Reattach Databases Using SQL Server Management Studio to get Exclusive access
A DBA needs to reattach the database by right clicking the Databases Node in the Object Explorer and selecting Attach…. from the popup. Click Add… and select the physical location of the database MDF file and click OK to attach the database. Reattaching the database puts the database in single user mode, and then you can restore the database. However there is still a chance that there can be more connections made that will not allow you to restore the database. This approach has more steps to perform and an experienced DBA is required to perform these tasks. Moreover this task will take more time to bring the database to a single user mode to restore the database.
ALTER DATABASE Command
The best approach to restore a database is to use the ALTER DATABASE Command to set the database to SINGLE USER Mode to roll back any open transactions. A DBA can use any one of the ALTER DATABASE commands to obtain exclusive access of the database to perform the database restoration.
USE MASTER
GO
ALTER DATABASE AdventureWorks SET SINGLE_USER WITH ROLLBACK IMMEDIATE
OR
ALTER DATABASE AdventureWorks SET SINGLE_USER WITH ROLLBACK AFTER 60
OR
ALTER DATABASE AdventureWorks SET SINGLE_USER WITH NO_WAIT
WITH ROLLBACK IMMEDIATE
If a DBA uses the WITH ROLLBACK IMMEDIATE option then all the open transactions within the database are rolled back immediately without waiting for them to complete. A DBA needs to know what kind of transactions are running on the database, as all the transactions will be rolled back immediately.
WITH ROLLBACK AFTER n (Seconds)
If DBA uses the WITH ROLLBACK AFTER n SECONDS option then all the open transactions are rolled back after waiting for n seconds. SQL Server will wait for a specified number of seconds before rolling back the open transactions within the SQL Server Database.
WITH NO_WAIT
If DBA uses the WITH NO_WAIT option then SQL Server will wait to set the database to a SINGLE USER Mode until all the open transactions have completed. SQL Server basically waits for a specified period of time and if the transactions are not completed the process will fail. This is one of the best approaches because it doesn’t rollback any transactions. However if there are long running transactions then it fails.
Restore Database Using TSQL
A DBA can restore a database by using the following TSQL and leveraging the ALTER DATABASE COMMAND
WITH IMMEDIATE ROLLBACK:
USE MASTER
GO
ALTER DATABASE AdventureWorks
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Go
RESTORE DATABASE AdventureWorks
FROM DISK =N’D:DatabaseBackupsAdventureWorks.BAK’
Go
Conclusion
A SQL Server Database needs to have no active connections before it can be restored. However a Database Administrator needs to make sure that there are no critical processes running which will take long time to rollback if they are killed.
]]>