How to move sql server move database files?
I have a database and want to move the .mdf and .ldf files to another location. But I do not want to stop the MSSQLSERVER service, and I do not want to export to another server. How can I do this?
Here are the steps to move SQL Server System Database Files - Part 1
- Step 1 - Check File Location of System Databases. First, let's see the current location of these files: ...
- Step 2 - Update System Database File Location. ...
- Step 3 - Physically Move System Database Files. ...
- Step 4 - Check Database Mail and Service Broker.
You don't 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;