How to move SQL Server database files to a new drive

I've been moving a lot of databases around this week for a new customer, so I thought I'd provide an example for you.  See below to move BOTH your data and log files to a new location, for your SQL Server database.

     /* Where are the files now? */

     USE MAPSampleDB;

     EXEC sp_helpfile

     /* Take exclusive access to the database. */

     ALTER DATABASE MAPSampleDB

     SET OFFLINE WITH ROLLBACK IMMEDIATE;

     /* In Windows Explorer, copy/paste your files to new location. */

     /* Run both of these statements to move both files to a new location. */

     -- data file

     ALTER DATABASE MAPSampleDB

     MODIFY FILE (

            name = 'MAPbeta_SampleDB',

            filename = 'D:\MSSQL\Data\MAPbeta_SampleDB.mdf'

     );

      

     -- log file 

     ALTER DATABASE MAPSampleDB

     MODIFY FILE (

            name = 'MAPbeta_SampleDB_log',

            filename = 'E:\MSSQL\Log\MAPbeta_SampleDB_log.ldf'

     );

     /* Bring your database back online. */

     ALTER DATABASE MAPSampleDB

     SET ONLINE;

     /* Where are you files now?  */

     USE MAPSampleDB;

     EXEC sp_helpfile

If you've got more than one data file, just handle it just like the first one. It really is that simple. Take a look at this for more information on the FILE and FILEGROUP options for ALTER DATABASE:

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-file-and-filegroup-options