Sql Server 更改现有数据库的数据和日志路径
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6856467/
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
Sql Server change data and log path of existing database
提问by Soham Dasgupta
I am having a SQL Server 2008 installation with almost 15 databases running on it. Now due to scarcity of space I would like to move the data path to another drive. What is the best practice for this. Please explain in details if including any SQL commands as I'm relatively new to SQL Server administration.
我有一个 SQL Server 2008 安装，上面运行着将近 15 个数据库。现在由于空间不足，我想将数据路径移动到另一个驱动器。这方面的最佳做法是什么。如果包含任何 SQL 命令，请详细解释，因为我对 SQL Server 管理比较陌生。
Note - I have already changed the path in SQL server properties from SQL Management Studio 2008, to the new path. But I would also like the existing databases to reside in the new path.
注意 -我已经将 SQL Server 属性中的路径从 SQL Management Studio 2008 更改为新路径。但我也希望现有数据库驻留在新路径中。
回答by Alex_L
First, detach database:
首先，分离数据库：
USE master;
GO
-- Important! We need to drop the existing connections.
ALTER DATABASE DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
EXEC sp_detach_db @dbname = N'DBName';
GO
Next step - copy files .mdf and .ldf of this database files to new location
下一步 - 将此数据库文件的 .mdf 和 .ldf 文件复制到新位置
And then attaching the database:
然后附加数据库：
USE master;
EXEC sp_attach_db @dbname = N'dbName',
@filename1 = N'', --path do .mdf
@filename2 = N''; --path to .ldf
GO
If you don't want to attach and detach all databases one-by-one, you can generate SQL script to attach and detach all databases you need (execept system, of course), using curosr that searches in sys.databases dynamic management view. But don't forget to copy the database files.
如果您不想一一附加和分离所有数据库，您可以使用在 sys.databases 动态管理视图中搜索的 curosr 生成 SQL 脚本来附加和分离您需要的所有数据库（当然系统除外） . 但不要忘记复制数据库文件。
回答by gbn
One way is to detach and attach.
一种方法是分离和连接。
As for commands/steps, see the MSDN article "How to: Move a Database Using Detach and Attach (Transact-SQL)"
至于命令/步骤，请参阅 MSDN 文章“How to: Move a Database Using Detach and Attach (Transact-SQL)”
