Thursday, 8 March 2012

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.

9 comments:

  1. Nice to see many posts on Big Data.

    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.

    Best Regards
    Gowtham

    ReplyDelete
  2. Sorry Gowtham, no idea I'm afraid. Never had an opportunity to put ParStream to the test (yet…).

    ReplyDelete
  3. Thank you for this. This is a thorny little problem and your solution worked great. Keep up the good work.

    ReplyDelete
  4. Thanks, your post did job for me.
    All the best from Poland.

    Marcin

    ReplyDelete
  5. Thanks, very useful.
    Step 11 though should say the following I thing:

    ALTER DATABASE MyStandbyDb SET ONLINE;

    ReplyDelete
  6. 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 /O

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

Disqus for @joeharris76