Using ALTER DATABASE
The 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 C:
drive.
ALTER DATABASE AdventureWorks2012 MODIFY FILE (NAME = AdventureWorks2012_Log, FILENAME = 'C:\AdventureWorks2012_log.ldf')
Caution
Use caution when specifying the FILENAME
parameter to move a database log file. If the FILENAME
setting ...
Get Microsoft® SQL Server 2012 Unleashed now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.