Sql – move 2008 sql database .mdf and .ldf files to a new folder location

sqlsql-server-2008

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.

Best Answer

While you could do this with a SQL Script, this is one of those cases where it's easier to use the management console.

Start the SQL Server 2008 Management Studio and connect to your server as a user with the sysadmin server role. Navigate to your database, right-click and select Tasks -> Detach..., click OK. Now you can move your database files to their new location. Finally, right-click the Databases folder and select Attach..., select the appropriate files and click OK. Your database will now be online using the files in their new location.

Related Topic