could not restore because database was in use

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

childofthe1980s - 10 Jul 2008 01:08 GMT

Hello:

In SQL 2005 (version 9.0.1406), I could not restore a backup of a database
without having to stop and restart the SQL Server service. Beforehand, if I
tried to restore, I would get a message saying that the database was in use
even though it was not in use. Again, once I stopped and restarted SQL, I
could restore successfully with no errors.

Why was there so much trouble in restoring? Does a service pack need to be
in place? (I used the Studio "All Tasks"...Tasks...Restore...Database method
to restore and I was logged in as "sa").

SQL Programmer (it's just a name)

Reply to this Message

Roy Harvey (SQL Server MVP) - 10 Jul 2008 01:20 GMT

One very common reason that a database is "in use" when you go to run
a restore is that you are connected to it in Management Studio without
realizing it. There can be all sorts of connections in the background
that you don't even see. One way to see what is going on is to run
sp_who, or better yet sp_who2, and look for the database name.

Roy Harvey
Beacon Falls, CT

>Hello:
>
[quoted text clipped - 9 lines]
>
>SQL Programmer (it's just a name)

Reply to this Message

childofthe1980s - 10 Jul 2008 01:34 GMT

Thanks, Roy!!!

SQL Programmer (it's just a name)

> One very common reason that a database is "in use" when you go to run
> a restore is that you are connected to it in Management Studio without
[quoted text clipped - 18 lines]
> >
> >SQL Programmer (it's just a name)

Reply to this Message

Linchi Shea - 10 Jul 2008 02:44 GMT

I find sp_lock works better than sp_who because you can be in a situation
where sp_who says that the database is not in use, but you still can't
restore the database. All that takes to prevent from restoring the database
is a lock on the database.

Linchi

> Thanks, Roy!!!
>
[quoted text clipped - 22 lines]
> > >
> > >SQL Programmer (it's just a name)

Reply to this Message

TheSQLGuru - 10 Jul 2008 16:04 GMT

A more direct way might be to probe sys.dm_tran_locks where
resource_database_id = dbid of concern

Signature

Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

>I find sp_lock works better than sp_who because you can be in a situation
> where sp_who says that the database is not in use, but you still can't
[quoted text clipped - 36 lines]
>> > >
>> > >SQL Programmer (it's just a name)

Reply to this Message

Alex Kuznetsov - 10 Jul 2008 19:22 GMT

On Jul 9, 7:08 pm, childofthe1980s
<childofthe19...@discussions.microsoft.com> wrote:

> Hello:
>
[quoted text clipped - 9 lines]
>
> SQL Programmer (it's just a name)

ALTER DATABASE yourDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Reply to this Message