Attach and detach a standby database in SQL 2K5

7 Sep 2006 11:41 PM

jxhyao

Hi gurus,
If I restore a database DB_A into a standby mode (i.e. restore database
DB_A from disk= .. with standby = ...), I can run sp_detatch_db to
detach the DB_A, but I cannot attach it back because I got the
following error

Msg 1824, Level 16, State 1, Line 1
Cannot attach a database that was being restored.

but in SQL Server 2000, there is no such error, i.e. I can detach and
attach back.

Is there some magic configuration that I forgot to turn on? To me, if
you allow me to detach, it implicitly means I can attach it back.
Otherwise, simply do not allow me to detach at all.

Any help is appreciated ...

Jeff

8 Sep 2006 11:43 AM

Sreejith G

From your error it seems DB is still open for more recovery operation...
While restoring to standby 'explicitly' specify with 'recovery' option.
Please reply if this solution works... or else I will try out in my box.

--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]

Show quote

"jxh***@gmail.com" wrote:

> Hi gurus,
> If I restore a database DB_A into a standby mode (i.e. restore database
> DB_A from disk= .. with standby = ...), I can run sp_detatch_db to
> detach the DB_A, but I cannot attach it back because I got the
> following error
>
> Msg 1824, Level 16, State 1, Line 1
> Cannot attach a database that was being restored.
>
> but in SQL Server 2000, there is no such error, i.e. I can detach and
> attach back.
>
> Is there some magic configuration that I forgot to turn on? To me, if
> you allow me to detach, it implicitly means I can attach it back.
> Otherwise, simply do not allow me to detach at all.
>
> Any help is appreciated ...
>
> Jeff
>
>