margo.taylor (2/21/2013)
I have been trying to restore a backup to my test server and keep getting the following error. I am using Microsoft SQL Server Management Studio.Restore failed for Server 'SERVER'. (Microsoft.SqlServer.Smo)
Additional information:
System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use.
(Microsoft.SqlServer.Smo)
Here are the steps I have been told to follow:
- Start > Command Prompt > iisreset > exit (clears & restarts processes)
- Start > Computer Management > Services and Applications > Services
o IIS Admin Service > Stop all processes
? Make sure SDE processes are also stopped
o IIS Manager > Application Pools
? Stop all processes
o Default SMTP Virtual Server
? Stop
- MS SQL Server Management Studio
o Login
o DB
o Test_sde > Tables > dbo.SMSYSPROCESS (right click)
? Delete all entries
o Test_sde > Tasks > Restore > Database
? General
• From device > Add (drill down to file .bak file)
? Options
• Overwrite the existing database
o Restore the database file for MagicTSD_Data (drill down to MSSQL.1 > Data > Test_sde.mdf)
o Restore the dataset file for MagicTSD_Log (drill down to MSSQL.1 > Data > Test_sde_log.ldf)
FINISH
- Start > Command Prompt > iisreset > exit (clears & restarts processes)
I have tried everything I can think of. Do you have any suggestions?
Why are you using GUI and restarting IIS ? Instead use scripts ... that will be much faster and more customizable ...
--- kill all connections .. Replace the database_name with your actual database name
alter database database_name
set single_user with rollback immediate
waitfor dealy "00:00:05" -- wait for 5 secs
alter database database_name
set multi_user with rollback immediate
go
--- now restore the database .....
restore database database_name
from disk = 'backup location\file.bak'
with replace, recovery, stat = 10 --- since you are replacing the current database
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂