May 19, 2009 at 12:16 pm
Receiving the error:
Msg 1824, Level 16, State 1, Line 1
Cannot attach a database that was being restored.
I have seen this posted numerous times but I have not seen a solution for my problem.
My ultimate goal is to:
1 - detach a log-shipped database (databaseA) which is in a STANDBY mode
2 - copy (not move) the MDF and LDF's for databaseA to a new location
3 - reattach databaseA using the original MDF and LDF's in their original location retaining the STANDBY mode
4 - Create a new database (databaseB) from the copied MDF and LDF files using the 'CREATE DATABASE databaseB on (filename...) FOR ATTACH' syntax retaining the STANDBY mode.
I am trying to test my procedures using a sample database before attempting this on my Logshipped production environment.
BOL seems to confirm that attaching a read-only database is possible (unless I am misunderstanding the documentation):
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/29ddac46-7a0f-4151-bd94-75c1908c89f8.htm
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/d0de0639-bc54-464e-98b1-6af22a27eb86.htm
I run into problems when I try to attach the original database:
Msg 1824, Level 16, State 1, Line 1
Cannot attach a database that was being restored.
Same problem occurs if I try to ATTACH the new database databaseB.
For my test
1 - created databaseA from a backup
RESTORE DATABASE [databaseA]
FROM DISK = N'E:\SourceDB.bak'
WITH FILE = 1, MOVE N'SourceDB' TO N'E:\databaseA.mdf',
MOVE N'SourceDB_log' TO N'E:\databaseA_1.ldf',
STANDBY = N'E:\ROLLBACK_UNDO_databaseA.BAK', NOUNLOAD, STATS = 10
2 - detached databaseA
EXEC master.dbo.sp_detach_db @dbname = N'databaseA'
3 - copied the MDF and LDF files off to a secondary location.
4 - tried to reattach the original databaseA database
CREATE DATABASE [databaseA] ON
( FILENAME = N'E:\databaseA.mdf' ),
( FILENAME = N'E:\databaseA_1.ldf' )
FOR ATTACH
At this point I get MSG 1824 above.
Any help here would be greatly appreciated:-D
May 19, 2009 at 1:25 pm
following the steps below should allow you to attach your database but may not be sufficient for what you need it for as there may be data loss.
1) create new database with same name.
2) stop sql server, and copy your log shipped db files over the files of the newly created database
3) start sql server and alter database to set emergency and single user
4) run dbcc checkdb with REPAIR_ALLOW_DATA_LOSS
5) alter database set multi user
May 19, 2009 at 2:35 pm
Thank you for the response but I can not afford data loss.
Also, I do not think that this would leave the DB in STANDBY mode which would be my end goal.
It may be useful to understand the BIG picture for what I am trying to accomplish....
I have a 200Gig DB located on one end of the world (site1) and we log ship that database to a NOREOVERY database on the other side of the world (site2) for DR puposes only.
Part 1...
Our application is getting to the point where we need to off load some reporting to a dedicated reporting database.
We would like to spin up a second log shipped copy of the site1 database. To do this we can do a large db backup copy across the world which will take a long time and we would not be able to do this until the w/e and will require a lot of baby sitting. We are trying to circumvent this by trying to put the log shipped site2 db into a STANDBY mode and perform the procedures above to create our second logshiped copy. We would then be able to put the original log shipped copy back into NORECOVERY, leave the second log shipped copy in STANDBY (read-only) and log ship to both db instances keeping them in synch with production on 15 minute intervals.
Confusion I know:Wow:
Part 2
Once we have accomplished the reporting requirement.
We need the ability to create an up to date QA/Development database on siteB at any given moment...give or take an hour or two.
This is where the long term need for these procedures comes into play.
Again, I was hoping to use the STANDBY log shipped copy to create a fourth database by using the detach/reattach procedures and then fully recovering the fourth database for QA/Development purposes.
site1
-----
databaseA (live production)
...............>
farfaraway
...............>
site2
----
databaseB (log shipped NORECOVERY)
databaseC (log shipped READ-ONLY)
databaseD (point in time fully recovered)
not prety but it kind of explains things.
thank you
May 19, 2009 at 4:48 pm
Eric Butler (5/19/2009)
I have seen this posted numerous times but I have not seen a solution for my problem.My ultimate goal is to:
1 - detach a log-shipped database (databaseA) which is in a STANDBY mode
2 - copy (not move) the MDF and LDF's for databaseA to a new location
3 - reattach databaseA using the original MDF and LDF's in their original location retaining the STANDBY mode
4 - Create a new database (databaseB) from the copied MDF and LDF files using the 'CREATE DATABASE databaseB on (filename...) FOR ATTACH' syntax retaining the STANDBY mode.
In my experience, cannot be done. A recovering/standby database cannot be attached.
In fact, in SQL 2008 a database that's in the restoring state can't be detached (though one in standby can)
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
May 19, 2009 at 6:13 pm
Thank you for the response Gail.
Yes, I found that it will allow you to detach a read-only (STANDBY) database. Kind of strange that you would not be able to reattach it
I can find references in BOL that lead me to believe that it is possible to attach a READ-ONLY database.
Example, under CREATE DATABASE ...
Therefore, when you attach a read-only database whose log is unavailable, you must provide the log files or files in the FOR ATTACH clause.
You can find this under the following BOL link:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/29ddac46-7a0f-4151-bd94-75c1908c89f8.htm
There are similar read-only references in the following link as well:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/d0de0639-bc54-464e-98b1-6af22a27eb86.htm
Are they refering to something other than STANDBY mode or is there another way to put a database in read-only mode?
Thanks for your help Gail
May 19, 2009 at 7:25 pm
You can attach a read-only database, but a database in standby mode is a different sort of read-only because it has an "undo file" associated with it.
I think it's that undo file that is preventing you from being able to re-attach the database. I don't know of any way of telling the attach commands how to re-associate an undo file with the database being attached, but unless that is done the restore part of the log-shipping isn't going to know it can roll-forward the transactions in that undo file before it starts restoring the next log file.
I've never looked inside the system tables to work out where the location and name of the undo file are stored. Even if you find out where the undo file details are stored the validation of the database when you attach it might prevent any hacking of the system tables to get them back to the same state they were when the database was detached.
May 20, 2009 at 12:36 am
Eric Butler (5/19/2009)
I can find references in BOL that lead me to believe that it is possible to attach a READ-ONLY database.Example, under CREATE DATABASE ...
Therefore, when you attach a read-only database whose log is unavailable, you must provide the log files or files in the FOR ATTACH clause.
Are they refering to something other than STANDBY mode or is there another way to put a database in read-only mode?
They are indeed referring to something other than Standby.
ALTER DATABASE .... SET READ_ONLY
If it referred to a DB in standby, it would state Standby.
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
May 20, 2009 at 12:52 am
Glenn Dorling (5/19/2009)
I think it's that undo file that is preventing you from being able to re-attach the database. I don't know of any way of telling the attach commands how to re-associate an undo file with the database being attached, but unless that is done the restore part of the log-shipping isn't going to know it can roll-forward the transactions in that undo file before it starts restoring the next log file.
It has to do with the DB's state. A readonly database is online and recovered. A standby or recovering database has not had recovery run on it (roll forward, roll back)
The same thing happens with a DV in the RECOVERY_PENDING state (often caused by damaged/missing tran log) and can happen with a DB that's SUSPECT.
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
May 20, 2009 at 1:05 am
Yes, Gila you are correct...
Here Read_Only means database which is not participate into Logshipping.
After restore when you put any database into READ_ONLY mode. Than you will able to detach and attach the same.
One more thing when you detach the READ_ONLY database after the attach DB will be in READ mode.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
May 20, 2009 at 5:40 am
Thank you very much Gail.
I appreciate your help.
I will have to devise another solution.
June 23, 2010 at 8:14 am
Hello, Erik!
That's possible and I simulate in a LAB using a Oficial Database.
Follow this procedure and you can recovery your database.
1. Create a new Database with the same name and Collation
2. Stop SQL Server
3. Copy the Standby Datafile and Logfile over the Database files created in blank
4. Start SQL Server and your Database will be recovered
5. Run a DBCC CheckDB without any parameters just to check the integrity of Database
That's it.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply