Another task you’ll need to be familiar with is moving your databases’ data files and log files.  I usually have to do this right after I have a new server built, and the system’s administrator installs SQL for me.  They mean well, they really do, but they aren’t expected to know the performance hit you get when you put all your data files and log files in one directory.

So when I see the box for the first time I usually see All my system databases in the default folder, and some times that default folder is even on the OS drive.  Bad mojo.  So I start out by moving those over to separate drives.  I usually follow the following design.

• Temp gets it’s own drive (hopefully built on RAID 0 or some kind of striping to make that database as quick as it can be. Later remind me to talk about splitting the temp database across multiple files for a little more speed and lower disc contention.
• The rest of the data files get their own drive, although in higher end systems I use multiple drives and put databases with higher IO requirements on disks by themselves.
• Log files get their own drive, again, for higher IO requirements, I may use multiple log discs, but I don’t split the log files into separate files, it doesn’t do anything extra for your speed.

There are a couple ways you can move your data files and log files from one location to another.  Looking at my local development machine I see that I have several databases on my OS drive, as well as my system databases.  I’ll show you how to move those databases using the GUI, using sp_detach_db and sp_attach_db, and using ALTER DATABASE.  In a later article I’ll cover how to move your system databases… those take a bit more work to move… and if you’re moving them on a cluster, there’s even more work… but I’ll leave that until a later article.

Before you begin any of these steps, make sure you’re comfortable with them on a development version of your database.  Don’t jump straight to production before giving this a shot.

Moving Database Files Using the GUI

Let’s move my AdventureWorks Database first.

Right click on the AdventureWorks, and choose Tasks->Detach

You’ll be presented with a Detach Database Dialog box, leave the presets alone, and hit OK.

Your database has been detached from the server.  If you want to see that it has been removed, right click on the databases folder at the top of your Object Explorer, right click and choose Refresh.

AdventureWorks will no longer appear in the list.

Now, open Windows Explorer, and go to where the database is stored.  In my case it’s stored in C:Program FilesMicrosoft SQL ServerMSSQL10_50.TWOK8R2MSSQLDATA

I’m going to move both of the files over to my E drive.  So I highlight Adventureworks_Data.mdf and Adventureworks_log.ldf and hit Ctrl+X.

I move over to my E drive, to the folder where all my other databases are installed, and hit Ctrl+V.  After the move is complete, I go back to SSMS.

Right Click on the Databases folder, and choose Attach.

You’ll get the Attach Databases Dialog.

Click the Add button, And then navigate to the folder you moved the MDF file into.  Select AdventureWorks_Data.mdf (or your database file name) and then hit OK.  In my case I moved both  files to the same folder, and SSMS was assumed the file was in the same folder, so we don’t have to change the log file location.  If you had moved the log file, you would have to change the folder for the log file, otherwise when you hit OK, the operation would fail.

But in our case, we’re good.  Hit OK, and your database has been moved and re-attached to the server.

Right click on the Databases folder at the top of SSMS, and choose Refresh.  You should see AdventureWorks in your list once more.

Using sp_detach_db and sp_attach_db to Move your Databases

We’re going to take basically the same steps with the AdventureWorksDW database.  Except this time, we’ll use T-SQL.  Simple call the sp_detach_db, and pass the database’s name.

 1: sp_detach_db @dbname= 'AdventureWorksDW'

Move the data file (and the log file if you wish).

Call sp_attach_db, pass the database name, and a list of the files (both the data file and log file).

 1: sp_attach_db @dbname= 'AdventureWorksDW'

 2:     , @filename1= 'E:MSSQL10_50.TWOK8R2MSSQLDATAAdventureWorksDW_Data.mdf'

 3:     , @filename2= 'E:MSSQL10_50.TWOK8R2MSSQLDATAAdventureWorksDW_Log.ldf'

 4:

Your database has been moved.  One quick note:  According to the books on line,

This feature will be removed in a future version of SQL.  You might want to learn to use the CREATE DATABASE…FOR ATTACH instead.  I’ll cover that in a later post too.  For now, I want to show you the method I use most often

Using ALTER DATABSE to Move your Databases

This method gets you to the same place, but instead of detach – re-attach, you change the location in your config, then take the database offline, move the file, then bring the database back online.

To alter the database config (for non system databases) use the ALTER DATABASE command.  I’m going to move my AdventureWorksDW2008R2 database over to the E drive.  Before you begin, you’ll need to know the filenames according to SQL.  Use the following command to see what they are set to for the database in question.

 1: USE AdventureWorksDW2008R2

 2: GO

 3: SELECT

 4:       a.name

 5:   , a.[FileName]

 6: FROM dbo.sysfiles a

 7:

 8: /*--Results:

 9: name FileName

 10: AdventureWorksDW2008R2_Data C:Program FilesMicrosoft SQL ServerMSSQL10_50.TWOK8R2MSSQLDATAAdventureWorksDW2008R2_Data.mdf

 11: AdventureWorksDW2008R2_Log C:Program FilesMicrosoft SQL ServerMSSQL10_50.TWOK8R2MSSQLDATAAdventureWorksDW2008R2_Log.LDF

 12: */

 13:

 14:

 15:

Now you know the name SQL uses to refer to the database file, you can call ALTER DATABASE, and tell it how you want to MODIFY the FILE.  You only have to pass the Name and new file name you wish to use.  In my case I’m moving to the E drive.

 1: ALTER DATABASE AdventureWorksDW2008R2

 2: MODIFY FILE (

 3:       NAME = 'AdventureWorksDW2008R2_Data'

 4:     , FILENAME='E:MSSQL10_50.TWOK8R2MSSQLDATAAdventureWorksDW2008R2_Data.mdf')

 5: /*--Message:

 6: The file "AdventureWorksDW2008R2_Data" has been modified in the system catalog. The new path will be used the next time the database is started.

 7: */

 8:

Like the message says, this won’t update until we restart that database.  So I’m going to take the database offline.  You could either right click on your database, and choose Tasks->Take Offline.

Or, since we’re using T-SQL here, you could call

 1: USE master

 2: GO

 3: ALTER DATABASE AdventureWorksDW2008R2 SET OFFLINE

Your Database is now offline.  Move the Data file only (unless you ran the ALTER DATABASE for the log file as well) to the new location.  After your file has been moved, run this command to bring that database back online.

 1: USE master

 2: GO

 3: ALTER DATABASE     AdventureWorksDW2008R2 SET ONLINE

And your database is back online.  Again, if you want to see the database back in your object explorer, right click on Databases, and choose refresh.

So now you know 3 ways to move your database files.

And knowing is half the hassle.

If you’re being hassled by something in preparation for the 70-432, let me know.  Let’s see if I can help you out.  That’s what I’m here for.