Home / server / Questions / 130973
Accepted
Nai
Asked: 2010-04-10 07:17:59 +0800 CST

Move a Database that is in restore mode

  • 772

I have a database which is the recipient of a logshipping process.

The database is in 'restoring'. Is it possible to move this database?

EDIT Just to clarify what I meant by move. I mean to make a copy of the database and transfer it to another server without breaking the logshipping process.

The reason for this is to run analysis queries against the database. I have tried using Copy Database Wizard but got stuck in the process. You can read about that problem here

backup sql-server-2008 database-administration copy

2 Answers

  1. Best Answer

    Never actually tried it, but this describes the method I would think would work. It depends on it NOT being in Standby mode (i.e. if you can connect to the recipient right now with read access, you'll need to fiddle with it to change it to Norecovery mode)

    This is also assuming that "move" means move to another drive/directory on the same server. If you're talking another server, then No.

    But to be honest, I would use this an exercise to test and update those documents that describe how to rebuild the standby server and re-restore from a full backup.

    EDIT BASED ON YOUR EDIT : Yes, you could stop SQL, copy the MDF and LDF to another server and attach them, restart SQL and log shipping should not be affected

    But if you have an on-going need to do this, you could change the log shipping to set the database to standby, which would allow you to do your analysis queries against the active log-shipping recipient database, as long as they are read-only queries, and you deal with the fact that log-shipping will be suspended while you are connected to the database.

    • 1

  2. Yes and no. By definition 'making a copy of the database' is not moving it. I think what you ultimately want to do is setup a second log shipping destination from the principal server. You can use log shipping to send the logs to two different places. You would set this up just as you originally set up your current instance.

    • 0