Moving the SQL Server Installation to a Different Drive

Following fishing regulations is very important.  We as a society are called to be responsible stewards of our natural resources, and that includes fish.  Overfishing, poaching, and spreading invasive species can all decimate a lake’s fish population, ruining it for everyone else.  I was disheartened to see a news article this week about a man caught with over 2,500 panfish in his freezer.  The legal limit is 50 per species, so he would have been allowed to possess 150 fish.  Hopefully the story of his guilt will dissuade other poachers, but given his rather light sentence, I doubt that will be the case.

I recently needed to install SQL Server Analysis Services (SSAS) on our test server to begin experimenting with it.  However, the C drive, where SQL Server was installed, had only a few hundred MBs of space left.  When installing SSAS on the existing instance of SQL Server, you are forced to use the same drive and I didn’t have enough space.  I decided to move the existing installation from the C drive to the D drive, which had plenty of available space.

There isn’t any way to move the existing installation, so I was forced to uninstall SQL Server on the C drive, then install it on the D drive.  Here are the steps I followed:

  1. Take a backup of all the databases, just in case.  This is always a good first step when making any significant changes to your environment.
  2. Run the Uninstall Wizard through Windows Control Panel to remove all SQL Server components.
  3. Reinstall SQL Server on the D drive.  I found I had to use an actual iso to do the install rather than the extracted contents of the iso.  When I tried to use the extracted contents I kept running into errors about missing msi files.
  4. Apply any service pack and patches to the installation so it is at least at the same version as the uninstalled instance.  If you skip this step you will not be able to restore/attach any of your existing databases to the new instance.
  5. At this point I expected to be able to move my existing master database file into the new default data folder, but I found my existing master database file had disappeared!  The uninstall must have deleted it.
  6. Instead, I started up SQL Server with the -m parameter in the SQL Server Configuration Manager’s SQL Server Advanced Properties.  This causes SQL Server to start up in single user mode, and only the master database comes online.
  7. Now restore the last backup of the master database:
    C:\> sqlcmd  
    1> RESTORE DATABASE master FROM DISK = 'Z:\SQLServerBackups\master.bak' WITH REPLACE;  
    2> GO
  8. When the restore is complete the service will stop.  Remove the -m parameter and start SQL back up.
  9. At this point everything came up as expected.  There were a few cleanup tasks to complete before I was finished:
  • Reconfigure Reporting Services.
    • During the install I had chosen to install but not configure so that I could plug into my existing SSRS databases.
  • Configure Powershell
    • The msdb.dbo.syssubsystems table contains information about Powershell that SQL Server uses when executing a PS script.  Mine was pointing to a subsystem dll and agent exe that were in the old installation location.  I updated this directly in the table with an UPDATE statement.

Once complete, SQL was ready to use, I had SSAS installed, and I opened up an additional 3 GB of hard drive space on the C drive, relieving the fear of crashing the OS.

Advertisements

SQL Server moving system databases Part 2 – master

I decided not to participate in the Rhinelander Hodag Muskie Challenge this past year.  While I enjoyed the experience in 2015, I didn’t feel compelled to try again quite yet.  Having caught no fish, it felt like a bit of a waste of money.  Instead of fishing for free and enjoying the peace and serenity of the lakes, instead it cost $250 and we were jammed into small lakes with several other boats trying to find some free water to fish.  I definitely see myself giving it another shot some time in the future, maybe even this year (2017).  The thrill of catching a big fish would only be magnified by thrill of hoping our catch was greater than all the other boats’ catches.  It’s just something that needs to be enjoyed in moderation, so it doesn’t become a waste of money when fishing is slow.

In my previous post, I demonstrated moving the msdb and model system databases.  This time I’m going to show how to move master, which is more complicated.  The master database in SQL Server holds all the system level information for SQL Server – all the logins, linked servers, endpoints and other system-wide configuration settings.  The master database also holds information about all the other databases and the locations of their files.  Because it is the “main” database, moving the files becomes more difficult – and dangerous.  If you lose the master database, you are going to have problems with basic requirements such as logging in.  Microsoft help states “SQL Server cannot start if the master database is unavailable”.

The first step in moving the database is to open SQL Server Configuration Manager and go to the Properties for SQL Server (MSSQLSERVER).

20170109 Move master database.PNG

Under the Advanced tab, you will see the current file locations specified in the Startup Parameters field.

20170109-move-master-db-current-location

The -d argument is the location of the data file and the -l argument is the location of the log file.  The -e is the location of the error log, in case you want to move that as well.

Update the file locations in the startup parameters to wherever you plan to move the files.

20170109-move-master-db-new-location

You will get a message that the changes were saved, but don’t go into effect until the service is restarted.

20170109-move-master-service-restart-required

Stop the service.

20170109-move-master-stop-service

Now move the files from their current location:

20170109-move-master-current-files-in-folder

To the new locations you specified in the startup parameters.

20170109-move-master-new-folder-location-for-data

20170109-move-master-new-folder-location-for-log

Lastly, start up the SQL Server Service, and you should be good to go!  You can verify the new file locations by running this query:

SELECT  name
      , physical_name AS CurrentLocation
      , state_desc
FROM    sys.master_files
WHERE   database_id = DB_ID('master');
GO

20170109 move master new location query.PNG

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.

Rebuilding System Databases

Sometimes in fishing you really want a do-over. Last weekend I was fishing a lake up in Onieda County for muskies (my favorite!). As we drifted over a shallow finger that extended into deep water, I hooked up with a solid muskie about 10 – 15 feet away from the boat. It appeared out of nowhere, and just grabbed the bait and held it. Not feeling a strong strike, I wasn’t even aware the fish was there until I felt some weight and looked over to see it. Then I made a series of mistakes that cost me a catch. First, I didn’t give a sharp hookset. I put a lot of weight against the fish by pulling hard, but did not drive the hooks in with a sharp tug. The second mistake was made a couple weeks before. I was using the lure, a shallowraider, when I caught my opening-day muskie, and I never re-sharpened the hooks. Due to the hard boney mouth of the muskie, it is incredibly important to keep your hooks razor sharp. Third, I didn’t stay in control of the fish. As he approached the boat straight on, I had nowhere to go. I also became distracted as I could feel him coming up for a jump. The fish leapt out of the water and shook his head viscously. When he landed back in the water, the lure popped out of his mouth and the fish was lost. I got a great look at him and he was definitely around 40 – 41″, a good sized muskie. Losing that fish will continue to gnaw at me until my next encounter with a muskie.
DBA’s can also really want a do-over. As I have been practicing my cutover for the server migration, I have made a lot of mistakes. My last one was to try and restore the Production server’s master database to the new server. This wouldn’t usually be a problem except the drive letters are different on the new server, as is the path of the system databases. By restoring the mater database, I ran into a situation where I was not able to get the service to come all the way back up. My only option is to start over fresh. I’m going to try to rebuild the system database. I use the instructions from Microsoft here.
First I Mount the SQL Server 2008 R2 installation media.
20150529 System Database Rebuild
Next I run this command in an elevanted command prompt.
20150529 System Database Rebuild Cmd
Upon completion there is no message in the command prompt.
20150529 System Database Rebuild Cmd Completed
Lastly, I am able to start the service.
20150529 System Database Rebuild Service Back Up
Use this only in extreme cases or in test environments as it will wipe out all the data in each of the system databases. The last step would be to restore each database from a backup, if possible.