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 selectTasks -> Detach...
, click OK. Now you can move your database files to their new location. Finally, right-click the Databases folder and selectAttach...
, select the appropriate files and click OK. Your database will now be online using the files in their new location.