October 18, 2013 at 4:02 am
Hi Team ,
I have system database and user database file are present in G,H and W drive.The process is going to be - copy data from G to S, H to T, W to U. Rename G to X, H to Y and W to Z. Rename S to G, T to H and U to W. Reboot the servers. The original G, H and W will then be X, Y and Z. The old S will be the new G, old T will be H and old U will be W.
My question is that after doing this whether my SQL server will start or not
October 18, 2013 at 4:30 am
If I understand your requirement correctly, you want to move files to a new drive and rename the drive itself.
Assuming the following,
You cleanly shut down all instances, move the files to new drive, and rename the drives to the original drivename.
When the instance start SQL will look for the file the location specified in system catalog, the files will be available.
Technically this should work. But you should have all backups and roll back options planned well ahead.
October 19, 2013 at 4:04 am
Just an addition to joeroshan suggestion , ALTER DATABASE ... MODIFY FILE will be required before sql restart.
see http://technet.microsoft.com/en-us/magazine/gg452698.aspx
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 19, 2013 at 5:40 am
As long as SQL is down when you do this and your databases end up on drives of the same letter when you restart, this will work. (I have done it).
Just back everything up first
Alter database won't be required in this scenario.
---------------------------------------------------------------------
October 30, 2013 at 11:39 am
If you struggle getting things going, master and the error log paths are in the service applet. Once you get that to start with new paths, you can do the alter database...modify file.
October 31, 2013 at 7:26 am
I did this on one of our servers last year. Everything came back except the SQL Server Agent would not start. I had to change the path in the Registry to get that fixed.
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services
October 31, 2013 at 6:35 pm
Easiest method is
1) Shutdown your SQL instance
2) move all database files to new drive ( make sure you don't change order or the folder names or so within..)
3) Remove the old drive or rename it from the management console
4) restart your SQL Instance
This should bring up your SQL instance fine...
Regards.
November 4, 2013 at 9:50 pm
SELECT * FROM TBL
November 5, 2013 at 7:10 am
In the title of this thread it also says "system databases".
This does in fact work a little different and not all by simply "MOVE FILE".
It is described in BOL: Move System Databases
And also just a personal tip: think about stop using drive letters. Using mount points eases much of such deployment and maintenance issues
Andreas
---------------------------------------------------
MVP SQL Server
Microsoft Certified Master SQL Server 2008
Microsoft Certified Solutions Master Data Platform, SQL Server 2012
www.insidesql.org/blogs/andreaswolter
www.andreas-wolter.com
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply