• Call us: +1 (800) 622-0083
  • Moving database files without restarting the instances in sql server

/
    • When I was a baby DBA, I used to detach and attach but as I becomes strong, I do believe you dont have to stop the SQL Server service to move database files, but you do have to take the specific database offline. This is because you can't move files while they're being accessed and taking the database offline stops the files from being used by the SQL Server application.

      The process to move them is fairly simple. Detach/Attach was already described, but it is not nearly this complex.

      Change the file locations with an ALTER DATABASE command:

      USE master; --do this all from the master
      ALTER DATABASE foo
      MODIFY FILE (name='DB_Data1'
                   ,filename='X:\NewDBFile\DB_Data1.mdf'); --Filename is new location

      Note, you do not need to declare the old location in this command. Changing this path does not take effect immediately, but will be used the next time the database starts up.

      Set the database offline

      (I use WITH ROLLBACK IMMEDIATE to kick everyone out and rollback all currently open transactions)

      ALTER DATABASE foo SET OFFLINE WITH ROLLBACK IMMEDIATE;

      Move/Copy the files to the new location

      Just copy the files over using your favorite method (Click 'n Drag, XCopy, Copy-Item, Robocopy)

      Bring the database online

      ALTER DATABASE foo SET ONLINE;

      This method is good when you are co-hosting other databases of other applications and you dont want to have impact on them.