Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Chaning the drive letter for system database and user databases Expand / Collapse
Author
Message
Posted Friday, October 18, 2013 4:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 23, 2014 9:40 AM
Points: 41, Visits: 161
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


Post #1506058
Posted Friday, October 18, 2013 4:30 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 3:10 AM
Points: 615, Visits: 1,259
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.


-- Roshan Joe
*******************************************
Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help
Custom cleanup script for backups
Post #1506065
Posted Saturday, October 19, 2013 4:04 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:21 AM
Points: 2,837, Visits: 3,954
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
Post #1506380
Posted Saturday, October 19, 2013 5:40 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:15 PM
Points: 5,976, Visits: 12,886
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.


---------------------------------------------------------------------

Post #1506385
Posted Wednesday, October 30, 2013 11:39 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 4:10 PM
Points: 33,095, Visits: 15,202
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.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1509897
Posted Thursday, October 31, 2013 7:26 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 8:50 AM
Points: 354, Visits: 345
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

Post #1510164
Posted Thursday, October 31, 2013 6:35 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 13, 2014 1:22 PM
Points: 20, Visits: 296
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.
Post #1510468
Posted Monday, November 4, 2013 9:50 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 6, 2013 10:00 PM
Points: 16, Visits: 7
SELECT * FROM TBL

Post #1511358
Posted Tuesday, November 5, 2013 7:10 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 2:59 PM
Points: 153, Visits: 973
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
Post #1511481
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse