SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Restore failed for Server. (Microsoft.SqlServer.Smo)


Restore failed for Server. (Microsoft.SqlServer.Smo)

Author
Message
margo.taylor
margo.taylor
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 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?
SQLQuest29
SQLQuest29
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1078 Visits: 4321
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 :-)
homebrew01
homebrew01
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4839 Visits: 9108
" ... 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 !



david.alcock
david.alcock
Mr or Mrs. 500
Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)

Group: General Forum Members
Points: 509 Visits: 1195
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'
Noetic DBA
Noetic DBA
SSC-Addicted
SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)

Group: General Forum Members
Points: 451 Visits: 420
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
margo.taylor
margo.taylor
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 27
This may seem silly but where do I find the Activity Monitor? I am very new to SQL Server.
Meet George Jetson
Meet George Jetson
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2948 Visits: 1395
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.
shabanaparveen
shabanaparveen
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 1
thank you, it helped!!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search