Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Restore failed for Server. (Microsoft.SqlServer.Smo) Expand / Collapse
Author
Message
Posted Thursday, February 21, 2013 1:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 1, 2013 7:42 AM
Points: 3, Visits: 27
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?
Post #1422776
Posted Friday, February 22, 2013 9:57 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, December 4, 2014 11:04 AM
Points: 739, Visits: 3,809
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
Post #1423141
Posted Friday, February 22, 2013 10:49 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 6:43 PM
Points: 2,838, Visits: 8,570
" ... 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 !



Post #1423167
Posted Monday, February 25, 2013 8:59 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 8:15 AM
Points: 224, Visits: 892
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'
Post #1423646
Posted Tuesday, February 26, 2013 6:46 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 8:54 AM
Points: 414, Visits: 390
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
Post #1424063
Posted Tuesday, February 26, 2013 6:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 1, 2013 7:42 AM
Points: 3, Visits: 27
This may seem silly but where do I find the Activity Monitor? I am very new to SQL Server.
Post #1424066
Posted Tuesday, February 26, 2013 8:49 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, December 12, 2014 7:00 AM
Points: 1,866, Visits: 1,339
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.
Post #1424119
Posted Monday, May 26, 2014 1:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 26, 2014 1:17 AM
Points: 1, Visits: 0
thank you, it helped!!
Post #1574460
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse