June 13, 2011 at 11:55 am
I had configured log shipping in my server. now my customer is asking to move the ldf file from one drive to other drive in the DR server on which the DB is in standby mode.Can anyone guide me how to move the path for a DB which is in standby mode???? its urgent
June 13, 2011 at 12:49 pm
If the secondary is in standby, set it to norecovery (change the option in the logshipping set up.)
run one more backup, copy, restore.
Disable those jobs.
On the secondary use the 'alter database....modify file' command to change the metadata on file locations.
Stop SQL - copy the file - start SQL
restart logshipping jobs, set to standby again if you wish.
Delete old file
---------------------------------------------------------------------
June 13, 2011 at 2:23 pm
other than stopping sql service and starting it do we have any other alternate for that like bringing DB to offline instead of stopping the entire service????
June 13, 2011 at 2:49 pm
when using alter database modify file to move files, offline\online is the normal way to achieve this, however a database in recovery mode cannot be set offline.
---------------------------------------------------------------------
June 13, 2011 at 2:52 pm
Hi Prem,
From SQL 2005 and above versions there is no need for you to restart the SQL Services when you want to move the user database file locations. Please follow the below steps. I had done this many times on the servers.
1. Take database into offline mode.
2. Run the alter database command to point the files to the new location
3. Move the files from old location to new location.
4. Bring the database online.
I recommend before performing the above steps. Please note down the logical names and physical file locations of the database files.
[font="Verdana"]Thanks
Chandra Mohan[/font]
June 13, 2011 at 2:55 pm
http://msdn.microsoft.com/en-us/library/bb522682(v=SQL.100).aspx
---------------------------------------------------------------------
June 13, 2011 at 6:26 pm
Hi chandra mohan
your options will work for a DB which is online mode but not for a DB which is in restoring mode. So let me know how to change the path of a DB which is in restoring mode
June 14, 2011 at 4:53 am
err.... as I described above? I thought this was urgent?
---------------------------------------------------------------------
June 14, 2011 at 4:59 am
We can't bring a DB from restoring mode to offline mode but we can bring it if it is in a stand by mode. so i will try it in that way. my customer has postponed the request thats why i couldn't implement yesterday
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply