February 21, 2013 at 1:26 pm
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?
February 22, 2013 at 9:57 am
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 🙂
February 22, 2013 at 10:49 am
" ... Exclusive access could not be obtained because the database is in use ..."
Someone has an open connection to the database.
You can use Activity Monitor or sp_who2 to identify it. Sometimes I find out I am the culprit because I forget I had a query window open !
February 25, 2013 at 8:59 am
Absolutely, activity monitor has a drodown db filter box so select the one you are wanting to restore and then you can see what is happening. Obviously dont go killing processes unless you're sure!
Ditto re the query window 🙂
'Only he who wanders finds new paths'
February 26, 2013 at 6:46 am
Great tip on the Activity Monitor, I did not realize you could use that.
If it is just the test environment, I use this script to kill connections prior to a restore.
DECLARE @spid varchar(10)
SELECT @spid = spid
FROM master.sys.sysprocesses
WHERE
dbid IN (DB_ID('Adventerworks), DB_ID('Northwind'), DB_ID('CCL'))
WHILE @@ROWCOUNT <> 0
BEGIN
EXEC('KILL ' + @spid)
SELECT @spid = spid
FROM master.sys.sysprocesses
WHERE
dbid IN (DB_ID('Adventerworks), DB_ID('Northwind'), DB_ID('CCL'))
AND spid > @spid
END
February 26, 2013 at 6:55 am
This may seem silly but where do I find the Activity Monitor? I am very new to SQL Server.
February 26, 2013 at 8:49 am
Activity Monitor can be found in the Object Explorer under the Management folder.
You can close all the connections prior to your restore, but more than likely SQL Agent is reconnecting immediately after you kill it.
Try restoring your database via T-SQL:
Use Master
Alter Database [database_name]
SET SINGLE_USER With ROLLBACK IMMEDIATE
RESTORE DATABASE [database_name] FROM DISK = 'E:\backup\zreports.bak' --location of .bak file
WITH REPLACE
GO
Good luck!
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
May 26, 2014 at 1:18 am
thank you, it helped!!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply