Move a database using the ALTER DATABASE statement in SQL Server

Before the release of SQL Server 2005 the task of moving a SQL Server database has not been possible simply by using TSQL. Previously the options for moving a database were:

  • To backup the database and then restore it to a new location
  • To detach the database and then re-attach it in a new location

Is SQL Server 2005 we now have the ability to move a database using the ALTER DATABASE TSQL command. Before we can issue the command you must:

  1. Take the database offline.
  2. Move the file on your local file system, i.e. move MyTestDB.mdf and/or MyTestDB.ldf to another folder on your server

Now you can issue the ALTER DATABASE statement from within SQL Server Management Studio. The example below firstly moves the data file for a database called MyTestDB and then uses the statement again to move the transaction log.

-- move the database file ALTER DATABASE MyTestDB MODIFY FILE (Name = MyTestDB_Data) FILENAME = 'C:\NewDataLocation\MyTestDB.mdf') -- Now move the transaction log (note be sure to double -- check your new filepath for the transaction log as -- if the filename given is wrong, no error will be raised -- when executing the statement but when you bring the -- database back online a prompt will display stating that -- the transaction log cannot be found and a new transaction -- log will be created thus invalidating the old log. ALTER DATABASE MyTestDB MODIFY FILE (Name = MyTestDB_Log) FILENAME = C:\NewDataLocation\Logs\MyTestDB.ldf')

After running the ALTER DATABASE command SQL Server will then know where to find the database files. Once you have issued the command(s) you can then bring the database back online.