SQL Server moving System Databases Part 1 – msdb and model

I can remember a few distinct unpleasant memories while fishing.  In one, I was fishing out on the Rainbow Flowage with my Dad, oldest son, and nephew.  Since there wasn’t much room in the boat, I decided not to bring my muskie tackle box.  I brought my pole with a single lure on it to do a bit of muskie fishing, but our targeted species that day was walleye, bass, and panfish.

About an hour into the trip I swung my pole hard to push out another cast and the lure simply popped off the line.  The sound of line snapping is never a welcome one for a fisherman.  Not only did I lose a lure worth around $20, but I was also finished muskie fishing for the trip, since I hadn’t brought any other lures.  The lesson in the story is that your gear is only as strong as its weakest link.  In my case, the line snapped, but it also could have been the leader breaking that ended my day early.

I recently ran out of C: drive space on my test environment database server.  The drive is small at only 33 GB, so there just wasn’t much space to free up.  I took a look and noticed the system databases, except for tempdb, were all residing on the C: drive.  While there wasn’t much space between them, I still thought moving them would be the best way to free up a bit of space on the drive.

I looked at the database properties in SQL Server Management Studio (SSMS) to find the current location of the system database files.

20170106-move-system-dbs-old-file-location

I found them to be in the default folder for SQL Server 2008 installations.  I navigated to the drive to confirm they were there and see what else was in there.

20170106-move-system-dbs-old-files-in-folder

You can see the total used space for the three system databases is around 186 MB.  Again, this is a very small amount of space, but it is the only space I know that I can free up.

The next step is to move the files’ locations in SQL Server’s records.  I do this with an ALTER DATABASE command for each file.

20170106-move-system-dbs-update-sql-locations

With this complete, SQL Server will now look for the files in that location the next time the service is started.  So I need to stop the service.  I did it through the SQL Server configuration manager, but you can do it from the Windows Services screen as well.

20170106-move-system-dbs-stop-service

Next I need to go onto the server and physically move the files from their current folder to the new location that I specified.

20170106-move-system-dbs-new-log-files-in-folders

20170106-move-system-dbs-new-data-files-in-folders

This I handled with a simple cut and paste in Windows Explorer.  The last step is to start the service back up.  Everything came up fine and I am now able to see the files are in the new locations.

20170106-move-system-dbs-new-file-location

In the next post I will tackle moving the master database, which has a few wrinkles that make it much harder to move.

Advertisements

One thought on “SQL Server moving System Databases Part 1 – msdb and model

  1. Pingback: SQL Server moving system databases Part 2 – master | The Fisherman DBA

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s