将 SQL Server 2008 数据库文件移动到新的文件夹位置
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6584938/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Move SQL Server 2008 database files to a new folder location
提问by FiveTools
Logical Name
逻辑名称
my_Datamy_Log
my_Datamy_Log
Path:
小路：
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATAC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATAC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA
FileName:
文档名称：
my.MDFmy_1.LDF
my.MDFmy_1.LDF
What would be the sql script to move these files to a new location: D:\DATA
将这些文件移动到新位置的 sql 脚本是什么： D:\DATA
Database is live so I would need to close existing connections.
数据库是实时的，所以我需要关闭现有的连接。
回答by Aaron Bertrand
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:
现在，您必须手动将文件从当前位置移动到 D:\Data\（如果您在 MODIFY FILE 命令中更改了它们，请记住手动重命名它们）……然后您可以使数据库重新联机：
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.
这假定 SQL Server 服务帐户对 D:\Data\ 文件夹具有足够的权限。如果不是，您将在 SET ONLINE 命令中收到错误。
回答by Marco Hurtado
You can use Detach/Attach Option in SQL Server Management Studio.
您可以在 SQL Server Management Studio 中使用分离/附加选项。
Check this: Move a Database Using Detach and Attach
检查这个：使用分离和附加移动数据库
回答by Lasse S. Jensen
To add the privileges needed to the files add and grant right to the following local user: SQLServerMSSQLUser$COMPUTERNAME$INSTANCENAME, where COMPUTERNAMEand INSTANCENAMEhas to be replaced with name of computer and MSSQL instance respectively.
要向文件添加所需的权限，请添加并授予以下本地用户的权限：SQLServerMSSQLUser$COMPUTERNAME$INSTANCENAME，其中COMPUTERNAME和INSTANCENAME必须分别替换为计算机和 MSSQL 实例的名称。
回答by Paolo
This is a complete procedure to transfer database and logins from an istance to a new one, scripting logins and relocating datafile and log files on the destination. Everything using metascripts.
这是一个完整的过程，将数据库和登录名从一个实例转移到一个新的实例，编写登录脚本并重新定位目标上的数据文件和日志文件。一切都使用元脚本。
Sorry for the off-site procedure but scripts are very long. You have to:
- Script logins with original SID and HASHED password
- Create script to backup database using metascripts
- Create script to restore database passing relocate parameters using again metascripts
- Run the generated scripts on source and destination instance.
See details and download scripts following the link above.
很抱歉非现场程序，但脚本很长。您必须：
- 使用原始 SID 和 HASHED 密码编写登录
脚本 - 使用元脚本创建备份数据库的
脚本 - 创建脚本以恢复数据库，再次使用元脚本传递重定位参数
- 在源和目标实例上运行生成的脚本。
按照上面的链接查看详细信息和下载脚本。
回答by Bull Fulbright
Some notes to complement the ALTER DATABASE process:
补充 ALTER DATABASE 过程的一些注意事项：
1) You can obtain a full list of databases with logical names and full paths of MDF and LDF files:
1) 您可以获得完整的数据库列表，其中包含 MDF 和 LDF 文件的逻辑名称和完整路径：
USE master SELECT name, physical_name FROM sys.master_files
2) You can move manually the files with CMD move command:
2) 您可以使用 CMD move 命令手动移动文件：
Move "Source" "Destination"
移动“源”“目标”
Example:
例子：
md "D:\MSSQLData"
Move "C:\test\SYSADMIT-DB.mdf" "D:\MSSQLData\SYSADMIT-DB_Data.mdf"
Move "C:\test\SYSADMIT-DB_log.ldf" "D:\MSSQLData\SYSADMIT-DB_log.ldf"
3) You should change the default database path for new databases creation. The default path is obtained from the Windows registry.
3) 您应该更改创建新数据库的默认数据库路径。默认路径是从 Windows 注册表中获取的。
You can also change with T-SQL, for example, to set default destination to: D:\MSSQLData
您还可以使用 T-SQL 进行更改，例如，将默认目标设置为：D:\MSSQLData
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'D:\MSSQLData'
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'D:\MSSQLData'
GO
Extracted from: http://www.sysadmit.com/2016/08/mover-base-de-datos-sql-server-a-otro-disco.html
摘自：http: //www.sysadmit.com/2016/08/mover-base-de-datos-sql-server-a-otro-disco.html
