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:
- Take the database offline.
- 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.
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.