Brian Cryer

 

Cry How to...

Move a database file

* Important *

Before attempting to move a database file please ensure that you have a good backup to go back to should things go wrong.

The procedure for moving a database file varies depending on whether you want to:


Move a database file

This procedure can be used for any database except 'tempdb' or 'master':

To move one or more database files for a given database:

  1. Consider backing up the database before you start, in case anything goes wrong.
  2. Get a list of all the filenames for files currently used in the database. Do this using:
    Use MyDatabase
    Go
    Exec sp_helpfile

    This step is essential - you can only successfully reattach the database by specifying each and every file.

  3. Detach the database. This prevents others from using it while the files are being moved. Do this using:
    Exec sp_detach_db 'MyDatabase'

    You will not be able to detach a database whilst it is in use.

  4. Move the database files to their new location, or rename them, as  appropriate to your needs.
  5. Reattach the database, explicitly specifying the full pathname of every file that constitutes the database. This includes any files that were not moved or renamed. For example:
    Exec sp_attach_db 'MyDatabase',
        'E:\MsSql7\NewHome\MyDatabase_Data.mdf',
        'E:\MsSql7\NewHome\MyDatabase_Log.ldf'

Notes:

  • 'sp_attach_db' can only be used with up to 16 files. If the database has more than 16 files then instead use 'Create Database' with the 'For Attach' clause.
  • If the detached database was enabled for replication and is attached to a DIFFERENT server then 'sp_removedbreplication' should be run to remove replication from the database.
  • Only members of the 'sysadmin' server role can execute 'sp_detach_db' and 'sp_attach_db'.

Move master database files

Procedure to move the master database:

  1. Take a full backup that you are confident you can recover from, incase anything goes wrong.
  2. Stop MS SQL Server.
  3. Move the files that constitute the master database to their new location and/or rename them, as appropriate.
  4. Change the start-up parameters for SQL Server to reflect the change.
  5. The easiest way to do this is to edit the parameters in the registry, under:
    For SQL Server 2005 For SQL Server 7
    HKEY_LOCAL_MACHINE
      \SOFTWARE
         \Microsoft
           \Microsoft SQL Server
             \MSSQL.1
               \MSSQLServer
                 \Parameters
    HKEY_LOCAL_MACHINE
      \SOFTWARE
        \Microsoft
          \MSSQLServer
            \MSSQLServer
              \Parameters

    Typical parameters are for SQL Server 2005:

    SQLArg0 -dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
    SQLArg1 -eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
    SQLArg2 -lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf

    or for SQL Server 7:

    SQLArg0 -dc:\MSSQL7\data\master.mdf
    SQLArg1 -ec:\MSSQL7\log\ERRORLOG
    SQLArg2 -lc:\MSSQL7\data\masterlog.ldf

  6. Restart MS SQL Server.
  7. Whilst it should be unnecessary, for confidence you may wish to check the database:
    dbcc checkdb (master) with no_infomsgs

Move temporary database files

To move a file that constitutes part of the temporary database use:

Alter database tempdb modify file (name=<logical-name>, filename='<full-pathname>')

For example, to move the log file for tempdb:

Alter database tempdb modify file (name=templog, filename='e:\mssql\data\templog.ldf')

or to move the data file for tempdb:

Alter database tempdb modify file (name=tempdev, filename='e:\mssql\data\tempdb.mdf')

This change will take affect when the MS SQL Server is next restarted.


These notes have been tested against SQL Server 2008, SQL Server 2005 Express and Server 7.



About the author: is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.