Cannot restore backup - database in use 1

[steve1rm]

Hello,

I have created a backup of sql 2005, but when i go to restore that backup get the following error message below.

The way I have done my backup is full backup and overwrite existing backup. Everything else is at the default. This works ok.

When I restore I select the backup and under options overwrite existing database. Everything else is set to default.

Does anyone have any ideas about this.

Many thanks in advance,

Steve


TITLE: Microsoft SQL Server Management Studio
------------------------------

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

For help, click:

http://go.microsoft.com/fwlink?Prod...ceptionText&EvtID=Restore+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.Smo)

 

[mrdenny]

You have to kill any spids that are using the database before you can restore over the database.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[steve1rm]

Thanks for your reply

How do i stop all the spids? I have tried restarting the sql server. But the problem still happens.

Many thanks,

Steve

 

[Catadmin]

Kill <spid> is the command you use. Run an SP_Who2 to find out what spids are using your database first.

DBCC InputBuffer(<spid>) will tell you what the last command the SPID ran is. Concentrate on user login spids as you won't be able to stop system SPIDs.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mrdenny]

If people are connecting to the database as soon as you restart the SQL Server it begs the question; should this database be getting restored? Sounds like people are using it and expecting the data to be available.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Catadmin]

Also, there's a risk if people are updating the database when you kill them. Data could potentially get corrupted or just lost, especially if queries are passing the data back and forth through an application connection.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[SQLBill]

One other option and the one I like the best is to stop the SQL Server Agent service. That will keep any jobs from running. Then look at any outside queries being run and stop those.

But I agree with MrDenny. Once you stop the agent, then the only connections should be system ones and actual users. Check with the users to see what they are doing before you 'kill' their processes.

-SQLBill

Posting advice: FAQ481-4875