ALTER DATABASE option for moving user database files was added in SQL Server 2005. This option involves the following steps:
1. Take the database offline.
2. Manually move the file(s) to the new location.
3. Run the
ALTER DATABASE command to set the
FILENAME property to the new file location.
4. Bring the database online.
The following example uses the
ALTER DATABASE command to move the log file for the
AdventureWorks2012 database to the root of the
ALTER DATABASE AdventureWorks2012 MODIFY FILE (NAME = AdventureWorks2012_Log, FILENAME = 'C:\AdventureWorks2012_log.ldf')
Use caution when specifying the
FILENAME parameter to move a database log file. If the
FILENAME setting ...