What is the quickest way to restore a DB

Lately I have been finding myself restoring a DB to my local instance of SQL-server a lot more. I wanted to speed up the process by writing a script and then just executing it in order to restore over some X DB on my local. However, every time that I run this script I get the error saying the DB is still being used. Now this could happen because I have a query open, or because a web application is still using it, but instead of me going to track down what is using my db and stopping it. I would much rather have my script take care of this since this is not going to be affecting anyone but me.

Is there a better way to do this than querying for the processes that are using the DB and killing them? Currently I was thinking of doing the following

 select 
 spid,status,loginame=SUBSTRING(loginame,1,12),
 hostname=SUBSTRING(hostname,1,12),
 dbname = SUBSTRING(db_NAME(dbid),1,10) 
 from sys.sysprocesses where DBid = 17

and then running the results back through a dynamic sql with the KILL command.

You can set the database in SINGLE_USER mode. If you also specify ROLLBACK IMMEDIATE it won’t wait for open transactions to finish but will just roll them back.

ALTER DATABASE [yourdatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

do your actions

ALTER DATABASE [yourdatabase] SET MULTI_USER

What is the quickest way to restore a DB

Enable Instant File Initialization. It will tremendously speed up your restore process.

Is there a better way to do this than querying for the processes that are using the DB and killing them?

Below code will help you :

--- change the db_name as per your databaseName
alter database db_name
set single_user with rollback immediate
waitfor delay '00:00:05' --- if the zombie spids are still comming back, increase it
alter database db_name
set multi_user with rollback immediate

-- or In TEXT mode from SSMS
-- below is sometimes not reliable as the webservers/applicaiton might be able to connect and break your restore script.

 select 'kill',spid from master..sysprocesses where db_name(dbid)='db_NAME_GOES_HERE'

I would like to post this as answer. You can use various methods to make sure you are able to knock out any connecton to database when you are trying to restore but there are repercussions when you use command

ALTER DATABASE [yourdatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

What if a explicit DML command which is updating numerous rows is running in your database. What is many such commnands are running. You would end up with many SPID’s rolling back and eventually will leave you waiting for them to complete. Remember a rollback can take thrice as time as the transaction to complete. So choose steps carefully.

Instaed I would like you to first inform all users and application users that you are going to restore the database. So that they would either shutdown application or do not perform any transaction through application. After you have made sure connection is minimal you can put database in single user mode like below. You dont need to specifically put it into multi user mode because after restore finishes it will be in multi user mode.

use master
go
ALTER DATABASE [yourdatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
go
restore database db_name from disk='location'
go

EDIT: Restore process also depends on amount of transaction which needs to be rolled forward and rolled back. If Log file have to many VLF’s restore process would take time. Restore will be faster if disk from which restore process is reading data is not facing I/O contention. More details in this microsoft Link http://technet.microsoft.com/en-gb/library/ms190954(v=sql.105).aspx