Database detach problem

Just curious , why not using BACKUP\RESTORE commands?


"Mindaugas" wrote in message
news:%23Dp%230$UuKHA.3428atTK2MSFTNGP06dotphx.gbl...
> Hi group,
> I need to write a script which automatically restores latest backup to
> test DB.
> In case that test DB is in use I first have to detach it. I write these
> scripts:
>
> USE master;
> ALTER DATABASE TestDB
> SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
> GO
>
> USE master
> GO
> sp_detach_db 'TestDB'
> GO
>
> And here I get an error that "TestDB" is in use and cannot be detached.
> I think that the first script should resolve this problem, but why it is
> not?
>
> SQL Server 2008
>
> BR,
> Mindaugas

I cannot restore DB, when it is in use. If I try to restore TestDB from
GoodDB backup, I get the same error "Database is in use".

"Uri Dimant" wrote in message
news:u2dG8JVuKHA.5384atTK2MSFTNGP04dotphx.gbl...
> Just curious , why not using BACKUP\RESTORE commands?
>
>
> "Mindaugas" wrote in message
> news:%23Dp%230$UuKHA.3428atTK2MSFTNGP06dotphx.gbl...
>> Hi group,
>> I need to write a script which automatically restores latest backup to
>> test DB.
>> In case that test DB is in use I first have to detach it. I write these
>> scripts:
>>
>> USE master;
>> ALTER DATABASE TestDB
>> SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
>> GO
>>
>> USE master
>> GO
>> sp_detach_db 'TestDB'
>> GO
>>
>> And here I get an error that "TestDB" is in use and cannot be detached.
>> I think that the first script should resolve this problem, but why it is
>> not?
>>
>> SQL Server 2008
>>
>> BR,
>> Mindaugas
>
>

Restore it with a different name...


"Mindaugas" wrote in message
news:uChXERVuKHA.5008atTK2MSFTNGP05dotphx.gbl...
>I cannot restore DB, when it is in use. If I try to restore TestDB from
>GoodDB backup, I get the same error "Database is in use".
>
> "Uri Dimant" wrote in message
> news:u2dG8JVuKHA.5384atTK2MSFTNGP04dotphx.gbl...
>> Just curious , why not using BACKUP\RESTORE commands?
>>
>>
>> "Mindaugas" wrote in message
>> news:%23Dp%230$UuKHA.3428atTK2MSFTNGP06dotphx.gbl...
>>> Hi group,
>>> I need to write a script which automatically restores latest backup to
>>> test DB.
>>> In case that test DB is in use I first have to detach it. I write these
>>> scripts:
>>>
>>> USE master;
>>> ALTER DATABASE TestDB
>>> SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
>>> GO
>>>
>>> USE master
>>> GO
>>> sp_detach_db 'TestDB'
>>> GO
>>>
>>> And here I get an error that "TestDB" is in use and cannot be detached.
>>> I think that the first script should resolve this problem, but why it is
>>> not?
>>>
>>> SQL Server 2008
>>>
>>> BR,
>>> Mindaugas
>>
>>

I can't.
Question was, why I cannot detach DB if it is placed in single user mode?


"Uri Dimant" wrote in message
news:uqI8tkVuKHA.6124atTK2MSFTNGP04dotphx.gbl...
> Restore it with a different name...
>
>
> "Mindaugas" wrote in message
> news:uChXERVuKHA.5008atTK2MSFTNGP05dotphx.gbl...
>>I cannot restore DB, when it is in use. If I try to restore TestDB from
>>GoodDB backup, I get the same error "Database is in use".
>>
>> "Uri Dimant" wrote in message
>> news:u2dG8JVuKHA.5384atTK2MSFTNGP04dotphx.gbl...
>>> Just curious , why not using BACKUP\RESTORE commands?
>>>
>>>
>>> "Mindaugas" wrote in message
>>> news:%23Dp%230$UuKHA.3428atTK2MSFTNGP06dotphx.gbl...
>>>> Hi group,
>>>> I need to write a script which automatically restores latest backup to
>>>> test DB.
>>>> In case that test DB is in use I first have to detach it. I write these
>>>> scripts:
>>>>
>>>> USE master;
>>>> ALTER DATABASE TestDB
>>>> SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
>>>> GO
>>>>
>>>> USE master
>>>> GO
>>>> sp_detach_db 'TestDB'
>>>> GO
>>>>
>>>> And here I get an error that "TestDB" is in use and cannot be detached.
>>>> I think that the first script should resolve this problem, but why it
>>>> is not?
>>>>
>>>> SQL Server 2008
>>>>
>>>> BR,
>>>> Mindaugas
>>>
>>>
>
>

On Mon, 1 Mar 2010 17:00:36 +0200, "Mindaugas"
wrote:

>Hi group,
>I need to write a script which automatically restores latest backup to test
>DB.
>In case that test DB is in use I first have to detach it. I write these
>scripts:
>
>USE master;
>ALTER DATABASE TestDB
>SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
>GO
>
>USE master
>GO
>sp_detach_db 'TestDB'
>GO
>
>And here I get an error that "TestDB" is in use and cannot be detached.
>I think that the first script should resolve this problem, but why it is
>not?
>
>SQL Server 2008
>
>BR,
>Mindaugas
Hi

Once you change to master something else can step in to use the
TestDB. This can easily occur with web applications. If your
applications don't use a privileged login then you may want to try
setting to single user and then RESTRICTED_USER

John

A few things to check. What is the exact error message? Could it be that
your own login is getting in the way? I’ve been burned by that one a few
times. Default database pointing to TestDB for logins? What does Activity
Monitor show in terms of processes connected to TESTDB? You might try
issuing an alter command back to multi user just prior to your detach. I
know it sounds silly but give it a try anyway.

I am assuming you have system administrator rights or a member of the
sysadmin role.

"Mindaugas" wrote:

> I can't.
> Question was, why I cannot detach DB if it is placed in single user mode?
>
>
> "Uri Dimant" wrote in message
> news:uqI8tkVuKHA.6124atTK2MSFTNGP04dotphx.gbl...
> > Restore it with a different name...
> >
> >
> > "Mindaugas" wrote in message
> > news:uChXERVuKHA.5008atTK2MSFTNGP05dotphx.gbl...
> >>I cannot restore DB, when it is in use. If I try to restore TestDB from
> >>GoodDB backup, I get the same error "Database is in use".
> >>
> >> "Uri Dimant" wrote in message
> >> news:u2dG8JVuKHA.5384atTK2MSFTNGP04dotphx.gbl...
> >>> Just curious , why not using BACKUP\RESTORE commands?
> >>>
> >>>
> >>> "Mindaugas" wrote in message
> >>> news:%23Dp%230$UuKHA.3428atTK2MSFTNGP06dotphx.gbl...
> >>>> Hi group,
> >>>> I need to write a script which automatically restores latest backup to
> >>>> test DB.
> >>>> In case that test DB is in use I first have to detach it. I write these
> >>>> scripts:
> >>>>
> >>>> USE master;
> >>>> ALTER DATABASE TestDB
> >>>> SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
> >>>> GO
> >>>>
> >>>> USE master
> >>>> GO
> >>>> sp_detach_db 'TestDB'
> >>>> GO
> >>>>
> >>>> And here I get an error that "TestDB" is in use and cannot be detached.
> >>>> I think that the first script should resolve this problem, but why it
> >>>> is not?
> >>>>
> >>>> SQL Server 2008
> >>>>
> >>>> BR,
> >>>> Mindaugas
> >>>
> >>>
> >
> >
> .
>