4

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

Improve this question
2
  • subject says 'move' but then your edit says 'copy the database' which is it? Commented Apr 14, 2010 at 14:59
  • as per the edit, i meant copy Commented Apr 15, 2010 at 8:25

2 Answers 2

Reset to default
1

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.

Improve this answer
9
  • cool, i'll test this in a bit but this sounds good! Commented Apr 13, 2010 at 10:09
  • yeah erm that didn't work due to the database in a restore/ read only mode Commented Apr 21, 2010 at 8:18
  • what didn't work? did the stop SQL & copy files approach not work? after you've attached the files it should then be a case of running "Restore database dbname with recovery" against the new copy to bring it to a usable state. Commented Apr 21, 2010 at 8:39
  • Let me re-run what I did and get the actual error message out. But basically, I stopped the service, copied the .mdf, .ldf and .ndf files over. Went into SMSS and restored from there but met the restore/read-only error in the process. Will report back in an hour or so with findings. Commented Apr 21, 2010 at 9:29
  • Make sure the .mdf, .ldf and .ndf files are writeable when you attach them, if you use cdrom to copy them over they mave have read-only bit set; also when you attach you can use any database name, if there is already a db there with the target name.You might need to do a "restore database xxx with recovery" after the attach. Commented Apr 21, 2010 at 11:03
0

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.

Improve this answer
1
  • I can't do this because I don't have active directory set up and I kinda hacked my local server to have the same user/pass as my primary DB. I can't do this for my other server (the analysis server). Commented Apr 21, 2010 at 8:26

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.