How To Copy A Standby Database in SQL Server

I ran across a SQL Server problem today where my Standby database was detached and would not re-attach. This problem originally arose because I wanted to make a development copy of log shipping standby database with restarting the log chain from a full backup.

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 Problem

I 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 Standby

This 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.

1. Take the standby DB offline (SQL)
   ALTER DATABASE MyStandbyDb SET OFFLINE;
2. 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 )
LOG ON
( NAME = 'MyLiveDb_log', --!!! Must match the Live & Standby name
FILENAME = 'C:\My\Data\Path\MyNewDevDb_log.ldf' ,
SIZE = 1024KB , FILEGROWTH = 10%) ;
3. (OPTIONAL) Apply TDE encryption to the target DB (SQL)
   ALTER DATABASE MyNewDevDb SET ENCRYPTION ON;
4. Backup the target DB (SQL)
   BACKUP DATABASE MyNewDevDb to disk = 'C:\My\Backup\Path\shell.bak' WITH FORMAT;
5. 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 ;
6. Take the target DB offline (SQL)
   ALTER DATABASE MyNewDevDb SET OFFLINE;
7. 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"
8. 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"
9. Take the target DB online (SQL)
   ALTER DATABASE MyNewDevDb SET ONLINE;
10. Restore the target DB for normal usage (SQL)
    RESTORE DATABASE MyNewDevDb WITH RECOVERY;
11. Take the standby DB back online (SQL)
    ALTER DATABASE MyStandbyDb SET ONLINE;
12. Resume log shipping!

Recovering a detached standby DB

If 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.