Move SQL Server 2008 database files to a new folder location

Question

Logical Name

• my_Data
• my_Log

Path:

• C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA
• C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA

FileName:

• my.MDF
• my_1.LDF

What would be the sql script to move these files to a new location: D:\DATA

Database is live so I would need to close existing connections.

Answer 1

You forgot to mention the name of your database (is it "my"?).

ALTER DATABASE my SET SINGLE_USER WITH ROLLBACK IMMEDIATE;  ALTER DATABASE my SET OFFLINE;  ALTER DATABASE my MODIFY FILE  (    Name = my_Data,    Filename = 'D:\DATA\my.MDF' );  ALTER DATABASE my MODIFY FILE  (    Name = my_Log,     Filename = 'D:\DATA\my_1.LDF' ); 

Now here you must manually move the files from their current location to D:\Data\ (and remember to rename them manually if you changed them in the MODIFY FILE command) ... then you can bring the database back online:

ALTER DATABASE my SET ONLINE;  ALTER DATABASE my SET MULTI_USER; 

This assumes that the SQL Server service account has sufficient privileges on the D:\Data\ folder. If not you will receive errors at the SET ONLINE command.