How to move SQL to another partition

Jonathan Wilkins
Jonathan Wilkins used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dr. KlahnPrincipal Software Engineer

Commented:
Have you considered shrinking D: upwards, then expanding C: into the freed space?

Author

Commented:
I am running raid 5
Dr. KlahnPrincipal Software Engineer

Commented:
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 to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
how do you do that?
Dr. KlahnPrincipal Software Engineer

Commented:
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.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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.

Author

Commented:
what partition manager do you prefer?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial