Issue when overwritting the Existing SQL Server database(Restore Issue)

Hi All,

Most of the cases ,I have seen this restore issue..this might be occur's ,when overwriting the existing database from  production backup file to dev/stage environment..Errors details as shown below.

 

above error means, database using by another porccess so that might be  running or sleeping connections.

we need to check by usinng below query ,what are the connections are opened on particular database .

 

Select * from sys.sysprocesses where db_name(dbid) like 'dbname'

 

by using particular query we can find out ,what are the SPID's opened on particular database .output is as shown below,if connections are opened .

 

when  you are trying to restore database  ,SPID 56 connected to that particular database .that's why unable to overwrite the existing database.

 KIll that 56 SPID by using below query.

KILL 56

then try to restore the database again.it would be success.

2nd method :

first Keep database in single user mode by using below query .

Alter database dbname set Single_User with rollback immediate

then resotre the database .

changed database to multi user mode.

Alter database dbname set multi_User


Thanks
Asr