Home / server / Questions / 257738
Accepted
Steve Evans
Asked: 2011-04-10 09:18:54 +0800 CST

Restoring T-Log Backups at DR Site

  • 772

I have a scenario where I'm shipping the T-Log backups to our DR site and restoring them to our DR SQL server.

The issue I'm trying to work out is that currently to do a test of the DR site we have to bring those databases on-line, do our test, and then ship over full backups so we can start the t-log restores.

Is there some way I could bring those databases live, do our tests, and get back to restoring mode database without having to ship over a full database backup?

sql-server

2 Answers

  1. No. You cannot modify a database in standby, the very first write will diverge the database from its master and the whole process has to be re-seeded. One solution is to have your tests be non-destructive, ie. read only, so they can be run on the standby database.

    • 1
  2. Best Answer

    Theres two ways I can think of to do what you want.

    First method: Local recovery and rollback:

    1. Stop the SQL service on the log-shipped instance.
    2. Copy all the databases including the system databases to a safe spot - these are your roll-back files.
    3. Bring the instance back online and recover your databases.

    4. Once you are done, stop the instance and copy the roll-back files back back over the files and start back up and your log-shipping will resume.

      When copying the files used a copy utility to verify the files (copy /V etc) to ensure you're copies are viable.

    Second method: Rename

    1. Create a new database with the name you plan to use for your testing (this one will be overwritten at the file level with the copy of the log-shipped database).
    2. Stop the SQL service on the log-shipped instance.
    3. Delete the data and log file for the database created in step 1, make sure to track the file names for use in step 4.
    4. Copy (duplicate) the database files for the log-shipped database to the same folders and rename them to match the DB files you just deleted in step 3.
    5. Start the instance back up; The system will auto recover the database.
    6. Drop the database when testing has completed.
    • 1