Moving Database Files to Another Location

Although moving database files around is simple, it is a still big deal.  When you do this, you are moving the entire database contents to another location.  If you have the space, a backup beforehand wouldn’t be a bad idea.  This is the script that I use to make this happen.

/*
 
    --PROCESS FOR MOVING DATABASE FILES TO ANOTHER DRIVE
    --http://support.microsoft.com/kb/224071
 
    --Press Ctrl+Shift+m to fill in variables
 
*/
 
--  GET CURRENT LOCATION OF FILES
USE 
GO
    sp_helpfile
GO
 
--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==
 
        --  RUN BACKUP OF DATABASE BEFORE PROCEDING
 
--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==
 
SET NOEXEC ON   -- THIS IS TO PREVENT ENTIRE SCRIPT FROM RUNNING AT ONCE
 
--  ###################################################################
--  Fill in variables by pressing Ctrl+Shift+m
--  ###################################################################
 
--  Step 1
--  THIS STEP PUTs DB IN SINGLE USER MODE TO DROP CONNECTIONS BEFORE DETACHING
--  BE SURE TO RUN ENTIRE STEP 1 SECTION (BETWEEN HASHTAGS) AT ONE TIME
--  ###################################################################
USE [master]
GO
ALTER DATABASE [] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N''
GO
--  ##################################################################
 
--  Step 2
--  MOVE FILES TO NEW LOCATION AND RUN NEXT SECTION
--  ###################################################################
 
USE [master]
GO
    sp_attach_db '','',''
GO
 
--  ###################################################################
 
SET NOEXEC OFF
--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==
 
        --  RERUN FIRST STEP TO CONFIRM FILES ARE IN NEW LOCATION
 
--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==

You can also use SQL Server Management Studio to do this by:

• Right-click DB name
• Tasks > Detach
• Move files
• Right-click “Databases” top-level folder
• Select “Attach”
• Click “Add”
• Choose new path of MDF file
• Choose (or correct) new path of LDF file
• Click OK

If you only moved the log file, then selecting the MDF file will automatically be pointing to the old location of the log file.  You will have to correct that line item in the GUI.

• Remember to delete your old MDF/LDF files to free up the space… AFTER you confirm that the new files are being used
• Change ownership of database back to whomever it was (sa?)