Tag Archive: MSSQL

So, having moved my Exchange databases and logs to the new D: drive on my server, the next on my list was my MS SQL databases. NOTE: I’m using MSSQL 2005, so whilst I know this will work for that, if you have a different product, check for the product you’re using.

As with my Exchange maintenance, I’d already created a new directory structure on my D: drive, with identical permissions to the folders containing the original databases.

Before starting this process, I stopped all of the services that were using SQL to ensure the databases were not being accessed when I tried to move them. I also made sure I had a good backup before attempting the process. You don’t need to stop MSSQL to do this, and in fact if you do, you won’t be able to access your DBs to detach/attach them.

I then started up the SQL Server Management Studio, located the DB I wanted to move, right clicked on it and selected ‘Detach’.

At this point, the DB will be detached, and will be removed from the list of DBs.

At this point, go to the folder(s) containing the mdf (Database) and ldf (Log) files of the database you have just detached, and move them to the new folders you created earlier. In my case, the mdf file was moved to D:\MSSQL\DB, and the ldf file was moved to D:\MSSQL\Logs.

The next step was to go back to SQL Server Management Studio, and right click on the ‘Databases’ folder. Select the attach option and a new dialog will open.

Click the ‘Add’ button, and you’ll then get a file browser, that will let you find your mdf file in it’s new location. Select your mdf file, and hit ‘OK’. At this point you’ll probably get a warning that the log file can’t be found, and you’ll have the option to locate it. Locate your ldf file in its new location and hit ‘Ok’ again and your DB should now re-attach.

I then repeated the process for my other DBs and then restarted all my services and web sites and tested.

The detach/attach method is not the only way to move DBs around, and there’s a good resource here: Moving SQL Databases, however I found it to be a pain free way to move my DBs to my new drive.

Now my Windows installation has been moved to its new hardware home, in order to improve perfomance, I decided to do the following:

1. Move my Exchange information stores to the new second drive.

2. Move my MSSQL databases to the new second drive.

3. Move my MySQL databases to the new second drive.

4. Move my IIS6 website content to the new second drive.

This should improve overall performance since these items will no longer reside on the same drive as the O/S, and more spindles means better read/write performance.

Since each one of these tasks is not that difficult, I thought I’d share with the world! So keep an eye open, they are coming soon.