File Move while DB in Recovery - log shipping

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

File Move while DB in Recovery - log shipping

Author

Topic

dcunningham
Starting Member

25 Posts

Posted - 2008-04-10 : 18:26:33

I have a warm standby (secondary server) receiving log shipping files.

The database has 5 files all in the primary filegroup. Two of the files need to be moved from one hard drive to another. Whats the best way / process to accomplish the move and re-establish the log shipping recovery status?

Thanks

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-04-10 : 22:42:44

Move files on source server? If you like to match moving on secondary server, have to reconfigure log shipping since needs different restore option.

dcunningham
Starting Member

25 Posts

Posted - 2008-04-11 : 12:41:17

Just an fyi,

1) On the secondary server, stopped the three log shipping jobs.
2) Set the database with recovery
3) Detached the database
4) Moved the files from one drive to the other.
5) Renamed the old files just in case I had to back out of the process.
6) Re-attached the database - altereed file locations to the new locations
7) Tried to set the database back into a restore mode. Well, the database was fully recovered. So no way to put it into a restore state.
8) Had to overwrite the database by doing a restore from a full backup file with no recovery.
9) start up the three jobs on secondary server to re-commence log shipping.

Hope this helps someone else with this issue or task.

sporoy
Starting Member

1 Post

Posted - 2011-06-16 : 06:10:05

We accomplished to move files for secondary database while in recovery w/o retoring a full backup for SQL Server 2005.

First issued an ALTER database command for the secondary database:

ALTER DATABASE TestX
MODIFY FILE (NAME = LogicalName, FILENAME = 'NewPath')

Stop the SQL service, move the file manually.

Then, start SQL service, Log Shipping continues from on the point it was before service stopped.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources