I couldn’t find a complete solution online so I thought I’d share my solution here. A tip ‘o the hat to Paul Randal for his “Hack attach a damaged database” post which got me going in the right direction.
The ProblemI have a log shipping setup whereby the reporting database is kept in Standby mode. I wanted to make an up-to-date copy for a developer to use. So I (foolishly as it happens) tried to simply detach the standby database and make a copy of it.
When I finished copying it and tried to re-attach it I got the error “Cannot attach a database that was being restored.” Uh-oh, this database is on the end of a very long log shipping chain and, although I haven’t really lost anything, rebuilding the chain is going to cost me a lot of time.
Plus, I really need to make new development copies of the standby database quickly and often. This is not a one time process.
How To Copy A Log Shipping StandbyThis may not be ‘the right way’ but it works and forget about detaching the DB that is definitely the wrong way to go, although you can recover from it using a variant of this process.
- Take the standby DB offline (SQL)
ALTER DATABASE MyStandbyDb SET OFFLINE;
- Create a ‘shell’ DB that will become the new target DB (SQL)
NOTE: that the internal ‘name’ values must match the Standby DB
CREATE DATABASE MyNewDevDb ON PRIMARY
( NAME = 'MyLiveDb', --!!! Must match the Live & Standby name
FILENAME = 'C:\My\Data\Path\MyNewDevDb.mdf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB )
( NAME = 'MyLiveDb_log', --!!! Must match the Live & Standby name
FILENAME = 'C:\My\Data\Path\MyNewDevDb_log.ldf' ,
SIZE = 1024KB , FILEGROWTH = 10%) ;
- (OPTIONAL) Apply TDE encryption to the target DB (SQL)
ALTER DATABASE MyNewDevDb SET ENCRYPTION ON;
- Backup the target DB (SQL)
BACKUP DATABASE MyNewDevDb to disk = 'C:\My\Backup\Path\shell.bak' WITH FORMAT;
- Restore the target backup in standby mode (SQL)
NOTE: The shell DB must match the state of the DB we want to replace it with
RESTORE DATABASE MyNewDevDb
FROM DISK = N'C:\My\Backup\Path\shell.bak'
WITH FILE = 1
,MOVE 'MyLiveDb' TO 'C:\My\Data\Path\MyNewDevDb.mdf'
,MOVE 'MyLiveDb_log' TO 'C:\My\Data\Path\MyNewDevDb_log.ldf'
,STANDBY = 'C:\My\Backup\Path\ROLLBACK_UNDO_MyNewDevDb.BAK'
,NOUNLOAD , STATS = 10 ;
- Take the target DB offline (SQL)
ALTER DATABASE MyNewDevDb SET OFFLINE;
- Rename (or delete) the target data and log files (CMD)
RENAME "C:\My\Data\Path\MyNewDevDb.mdf" "DeleteThis.mdf"
RENAME "C:\My\Data\Path\MyNewDevDb_log.ldf" "DeleteThis_log.ldf"
- Copy standby DB’s data and log files, using target’s filenames (CMD)
COPY "C:\My\Data\Path\MyStandbyDb.mdf" "C:\My\Data\Path\MyNewDevDb.mdf"
COPY "C:\My\Data\Path\MyStandbyDb_log.ldf" "C:\My\Data\Path\MyNewDevDb_log.ldf"
- Take the target DB online (SQL)
ALTER DATABASE MyNewDevDb SET ONLINE;
- Restore the target DB for normal usage (SQL)
RESTORE DATABASE MyNewDevDb WITH RECOVERY;
- Take the standby DB back online (SQL)
ALTER DATABASE MyStandbyDb SET ONLINE;
- Resume log shipping!
Recovering a detached standby DBIf you need to attach a detached SQL Server standby DB you can do so use a slight variation on the steps above. Simply omit steps 1, 8 and 11 and treat the database you want to attach as the target in the other steps. Voila! :-)
I hope this helps someone. Let me know if you find any issues with the steps.
Nice to see many posts on Big Data.ReplyDelete
I recently came to know about Pastream. I am trying to make some reports in Tableau on Parstream.
It seems there is no separate ODBC driver. They are using Postgre SQL odbc driver. Any idea?
Please share your thoughts.
Sorry Gowtham, no idea I'm afraid. Never had an opportunity to put ParStream to the test (yet…).ReplyDelete
Thank you for this. This is a thorny little problem and your solution worked great. Keep up the good work.ReplyDelete
Thanks, your post did job for me.ReplyDelete
All the best from Poland.
Thanks, very useful.ReplyDelete
Step 11 though should say the following I thing:
ALTER DATABASE MyStandbyDb SET ONLINE;
Fixed! Thanks. :)Delete
Depending on the Windows account used to run SQL Server there can be problems when attempting step 9 (i.e. to bring the new Dev DB back online). You get error code 5, which means "access denied". This is because it can be that the SQL Server user does not have read/write privileges for the new mdf and ldf files. This is caused by step 8. The copy command just gives the target files default security settings. Not sure what the best solution is here, but I've used xcopy /OReplyDelete
If you want to run this process repeatedly, you need to take a copy of the standby file from the source database before performing step 10. Step 10 will delete this file. Then following step 10 simply copy or move the standby file back into place and your source database is still in good shape.ReplyDelete
Has anyone had any issues with the .tuf undo file when running through this procedure? When I RESTORE the target DB for normal usage RESTORE DATABASE MyNewDevDb WITH RECOVERY; then the StandBy DB's .tuf file is deleted which prevents me completeing Step 11 i.e. Taking the Standby DB back online ALTER DATABASE MyStandbyDb SET ONLINE; as I receive an error message stating that the .tuf file is missing. I tried backing up the .tuf file in a separate directory and then pasting back afterwards which works but it's problematic. Thanks in advanceReplyDelete