sql server move database files to new drive

 sql server move database files to new drive

 

When you need to move SQL Server database files to a new location, there are a few approaches you can take. Let me outline a couple of methods for you:

1. Detach and Attach Method:

   • Detach the Database: First, detach the database using the sp_detach_db stored procedure or through SQL Server Management Studio (SSMS).
   • Move Files: Physically move the detached database files (both data and log files) to the desired location.
   • Attach the Database: Finally, attach the database using the sp_attach_db stored procedure or SSMS. Make sure to specify the new file paths during attachment¹.

2. ALTER DATABASE Method:

   • Take Database Offline: Use the following command to take the database offline:
     SQL

     USE master;
     ALTER DATABASE TestDB SET OFFLINE;
     

     AI-generated code. Review and use carefully. More info on FAQ.
   • Move Files: Physically move the data and log files to the new locations.
   • Modify File Paths: Alter the database to use the new paths for its files:
     SQL

     USE master;
     ALTER DATABASE TestDB MODIFY FILE (NAME = TestDB, FILENAME = 'C:\\MSSQL\\UserDBData\\TestDB.mdf');
     ALTER DATABASE TestDB MODIFY FILE (NAME = TestDB_log, FILENAME = 'C:\\MSSQL\\UserDBLog\\TestDB_log.ldf');
     

     AI-generated code. Review and use carefully. More info on FAQ.
   • Bring Database Online: Finally, bring the database back online