March 18, 2009 at 6:26 am
I have a standby database which I restore transaction logs to and use for reporting purposes (not log shipping). I needed a copy of this on a new reporting server and so decided to use detach/attach. SSMS allowed me to detach but when I try to reattach after copying the files I get
Msg 1824, Level 16, State 1, Line 1
Cannot attach a database that was being restored.
I've checked all of Microsofts documentation on detach/attach and nowhere does it say that you cant do this on a standby database. It mentions replication, mirroring and snapshots but not standby.
Why also would it let me detach a database which is not possible to restore?
I will get around this problem by taking a full backup of the live database and going through the restore procedure with full and tlog backups, however this will take a lot of time and is very frustrating.
I have read a lot of posts on this but none with solutions. Is there a way around this or is my redoing the whole full/tlog backups the only solution?
Best regards
Susan
March 18, 2009 at 9:22 am
Hello,
Alot of search and did not seen the workaround. All you need to do is create a new database with the same name as the one that you want to attach. Make sure that the Data and Log sizes match the original. Once the new database is created, take it offline. Copy the original Data and Log file over the new ones, and bring the database back online. This is the only way that I have discovered to force attaching a database that will not cooperate.
Try your Luck!
March 18, 2009 at 9:30 am
Susan Laing (3/18/2009)
Why also would it let me detach a database which is not possible to restore?
SQL 2008 doesn't allow it. That the earlier versions did was an omission. Any database that's in the restoring or standby state cannot be attached.
I will get around this problem by taking a full backup of the live database and going through the restore procedure with full and tlog backups, however this will take a lot of time and is very frustrating.
This is the best solution and it's probably the only one that will let you apply future logs
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply