Friday, June 23, 2017

How to move SQL Server databaes 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: