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

Community
  • 1
Nai
  • 743
  • 1
  • 6
  • 24

2 Answers2

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.

SqlACID
  • 2,166
  • 18
  • 18
  • cool, i'll test this in a bit but this sounds good! – Nai Apr 13 '10 at 10:09
  • yeah erm that didn't work due to the database in a restore/ read only mode – Nai Apr 21 '10 at 08: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. – Chris W Apr 21 '10 at 08: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. – Nai Apr 21 '10 at 09: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. – SqlACID Apr 21 '10 at 11:03
  • Bear with me but how do I make sure the files are writeable? – Nai Apr 21 '10 at 11:20
  • I get this error when trying to attach the databases: Cannot attach a database that was being restored. (Microsoft SQL Server, Error: 1824) – Nai Apr 21 '10 at 13:17
  • wow, looks like that process might be broken with sql 2005? this link http://www.jwc3.net/2008/12/sql-server-cannot-attach-database-that.html has a possible voodoo/solution. Hope that helps. – SqlACID Apr 21 '10 at 16:53
  • yeah thats my current solution -_- was hoping for another workaround. im actually using sqlserver 2008 – Nai Apr 21 '10 at 18:27
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.

sinping
  • 2,055
  • 14
  • 12
  • 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). – Nai Apr 21 '10 at 08:26