PerlStalker's SysAdmin Notes and Tools

Moving a Database to a New Drive in MS SQL Server 2000

Once upon a time, the Microsoft SQL server on my LAN was running out of hard drive space on the drive where the databases where stored. I needed to rescue my helpless data by moving it to a new, roomy drive. Here's how I did it.

Make a Complete Backup

Always make a complete backup before continuing. If you don't you could lose data. This is the computer equivalent of rescuing your helpless data from the tower only to feed it to the dragon.

Move the Database Files

Now comes the time to move your database files. These are the *.mdf and *.ndf files that are stored somewhere such as C:\Program Files\Microsoft SQL Server\MSSQL\Data. The files are named for your database. For example, master.mdf is the file for the database named master.

Stop database.
Move the files for the database you want to move to the new location.
Start database.

Note: I like to create a copy of the files somewhere else, if I have room. I've killed one too many things because I didn't have an extra backup somewhere.

Attach to the New Files

Now it's time to tell MSSQL where we hid the datafiles for the DBs we're moving. For each database that's being moved, we need to run the following SQL commands. Note: [database name] is the name of the database (without []'s).

EXEC sp_detach_db "[database name]", 'true'
EXEC sp_attach_db @dbname = N'[database name]',
                  @filename1 = N'X:\Data\[database name]_data.mdf',
                  @filename2 = N'X:\Data\[database name]_log.ldf'

Note: Those N's need to be there. I'm sure there's a reason for them but I have never found out what it is.

Restart and Test

Now you can restart MSSQL and test your databases.