Link to home
Start Free TrialLog in
Avatar of Jonathan Wilkins
Jonathan Wilkins

asked on

How to move SQL to another partition

Hello am needing help on how to move SQL to another partition?
My C: partition is filling up and I have plenty of space for it on my D: partition
I am running MSSQL10_50.SQLExpressR2
server 2008R2
Avatar of Dr. Klahn
Dr. Klahn

Have you considered shrinking D: upwards, then expanding C: into the freed space?
Avatar of Jonathan Wilkins

ASKER

I am running raid 5
I am running raid 5

Makes no difference for this purpose.  The partitions are independent of the underlying drive hardware and can be juggled as needed.
how do you do that?
First, make a full backup of the entire "drive" so that when things go south, it can be restored.  Do not attempt partition shuffling without a full backup; if you do, Finagle's Law guarantees that something will go wrong.

Get a copy of a partition manager that you like.  Practice with it on some systems that can be sacrificed and restored from backups if something goes wrong before trying the real job.  Some partition managers are bootable and run from CD or flash drive; some run from within Windows.  Both work about the same as far as final effects, though those which run from within Windows may require multiple reboots between operations.

E.g.:  A 1 TB drive with two partitions, C: and D:.  C: is 150 GB.  D is 600 GB.  The remainder is unused.

D: can be pushed up by at least 250 GB (the unused space at the end of the drive) .  It may be possible to push it further up if there is free space on the D: partition.  For the purposes of this example there is 80 GB free on D: and no more free space will ever be required on it - an unrealistic assumption if there ever was one.

Defragment and compact D: using the partition tool.  This will speed operations.

Use the partition tool to push D: up 330 GB so that the high end of that partition butts up against the end of the drive.  D: is now 330 GB larger.

Use the partition tool to move the bottom of D: up by 330 GB.  D: is now the same size as it was in the beginning.

There is now 330 GB free after the end of C: and before the start of D:

Use the partition tool to move the top of C: up by 330 GB so that it butts up against the bottom of the D: partition.  C: is now 330 GB larger.

This process may take several hours or longer (to much longer) if the partition being moved up is fragmented, full of small files, or just plain big.
Leave the SQL install and system dbs on C:.  Move the user database files to D:.

You can do that easily without any real risk of loss, since the file will exist in the old location and the new location at the same time.  When you're comfortable that the new db is working, you can delete the old files.

1) Determine the directory path you want to use for SQL data files on D:.  Create any new dirs you need.

2) Pick a db you want to move.  Assign its data file locations to the new drive. [Note: SQL is still running at this point.  This just changes the locations for the next time the db starts up.]  
You have to use the logical file names to change the locations.  You can get the logical file names from this command:

/* Note: You use SSMS, MS's gui SQL interface, to run these commands.  You can download it separately from SQL itself, I think it's a "SQL Tool" in Express. */

EXEC user_db_1.sys.sp_helpfile

Then create commands to move each file to the D: locations:

ALTER DATABASE user_db_1 MODIFY FILE ( NAME = [<logical_file_name1>], FILENAME = 'D:\path\to\files\user_db_1.mdf' )
ALTER DATABASE user_db_1 MODIFY FILE ( NAME = [<logical_file_name2>], FILENAME = 'D:\path\to\files\user_db_1_log.ldf' )

Verify that the new locs are in place:
SELECT * FROM master.sys.master_files WHERE database_id = DB_ID('user_db_1')

Prep is now done, time to move the files.

ALTER DATABASE user_db_1 SET OFFLINE WITH ROLLBACK AFTER 15 SECONDS;
--copy the db files from the C: drive to their new locations on the D: drive
ALTER DATABASE user_db_1 SET ONLINE;

Let the db run awhile.  When you're comfortable with the results, you can delete the files from the C: drive.
what partition manager do you prefer?
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.