将 SQL Server 2008 数据库文件移动到新的文件夹位置 [英] Move SQL Server 2008 database files to a new folder location

问题描述

逻辑名称

• my_Data
• my_Log

路径:

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

文件名:

• my.MDF
• my_1.LDF

将这些文件移动到新位置的 sql 脚本是什么:D:\DATA

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.

推荐答案

您忘记提及数据库的名称(是我的"吗?).

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'
);

现在您必须手动将文件从当前位置移动到 D:\Data\(如果您在 MODIFY FILE 命令中更改了它们，请记住手动重命名它们)……然后您可以使数据库重新联机:

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;

这假设 SQL Server 服务帐户对 D:\Data\ 文件夹具有足够的权限.否则，您将在 SET ONLINE 命令中收到错误.

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.

这篇关于将 SQL Server 2008 数据库文件移动到新的文件夹位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！