database 在 SQL Server 2005 中将数据库从一个驱动器移动到另一个驱动器的正确方法是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1119834/
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
What is the proper way to move a database from one drive to another in SQL Server 2005?
提问by Kilhoffer
I'm not looking to relocate the database to another server entirely, but just move the data file(s) and log file to another drive with more space. I've seen conflicting directions on how to do this, so I'm looking for the recommended proper way of doing it.
我不打算将数据库完全重新定位到另一台服务器，而只是将数据文件和日志文件移动到另一个具有更多空间的驱动器。我已经看到有关如何执行此操作的相互冲突的说明，因此我正在寻找推荐的正确方法。
回答by Rich
Detach the Database:
分离数据库：
use master
go
sp_detach_db 'mydb'
Move the Database files (Xcopy through xp_cmdshell shown):
移动数据库文件（显示通过 xp_cmdshell 的 Xcopy）：
DECLARE @SRCData nvarchar(1000)
SET @SRCData = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\mydb.mdf';
DECLARE @SRCLog nvarchar(1000)
SET @SRCLog = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\mydb_log.ldf';
DECLARE @FILEPATH nvarchar(1000);
DECLARE @LOGPATH nvarchar(1000);
SET @FILEPATH = N'xcopy /Y ' + @SRCData + N' D:\Data';
SET @LOGPATH = N'xcopy /Y ' + @SRCLog + N' E:\Log';
exec xp_cmdshell @FILEPATH;
exec xp_cmdshell @LOGPATH;
ReAttach Database:
重新附加数据库：
sp_attach_db 'mydb', 'D:\Data\mydb.mdf', 'E:\Log\mydb_log.ldf'
There's more detail at this Microsoft KB article.
此Microsoft KB 文章中有更多详细信息。
回答by Alex_L
Another way - detach database files (database->tasks->detach), move them to new drive and then attach again. But way described by Jay S is the simpliest.
另一种方法 - 分离数据库文件（数据库->任务->分离），将它们移动到新驱动器，然后再次附加。但是Jay S描述的方式是最简单的。
回答by Jay S
To be absolutely safe, I would do the following:
为了绝对安全，我会做以下事情：
- Backup the database to a BAK file.
- Take the current database offline, or delete it if you want to.
- Restore the database and change the location of the MDF and LDF files.
- 将数据库备份到 BAK 文件。
- 使当前数据库脱机，或者根据需要将其删除。
- 恢复数据库并更改 MDF 和 LDF 文件的位置。
Scripts sample:
脚本示例：
-- Get the file list from a backup file.
-- This will show you current logical names and paths in the BAK file
RESTORE FILELISTONLY FROM disk = N'C:\Backups\MyDatabaseName.bak'
-- Perform the restore of the database from the backup file.
-- Replace 'move' names (MDFLogicalName, LDFLogicalName) with those found in
-- the previous filelistonly command
restore database MyDatabaseName
from disk = N'C:\Backups\MyDatabaseName.bak'
with move 'MDFLogicalName' to 'D:\SQLData\MyDatabaseName.mdf',
move 'LDFLogicalName' to 'D:\SQLLogs\MyDatabaseName_log.ldf',
replace, stats=10;
Notes
笔记
The first script will get you the current names and paths that you'll need in the second script. The second script restores the database back to the name you want it to have, but you can change where you want to store. In the example above, it moves the MDF and LDF files to the D: drive.
第一个脚本将为您提供第二个脚本中所需的当前名称和路径。第二个脚本将数据库恢复为您希望它具有的名称，但您可以更改要存储的位置。在上面的示例中，它将 MDF 和 LDF 文件移动到 D: 驱动器。
回答by Steve Rukuts
I'd rather not enable xp_cmdshell on my SQL Server instance, so I wrote a function to do this using Powershell instead; it was especially useful when I had to move a large number of databases.
我不想在我的 SQL Server 实例上启用 xp_cmdshell，所以我写了一个函数来使用 Powershell 来做到这一点；当我不得不移动大量数据库时，它特别有用。
function Move-Database
{
param ($database, $newPath)
$paths = Invoke-SqlCmd "SELECT master_files.physical_name as Path
FROM sys.databases
JOIN sys.master_files ON master_files.database_id = databases.database_id
WHERE databases.name = '$database';";
$paths = $paths | % { $_.Path };
if (!$paths)
{
throw "Unknown database '$database'";
}
Write-Host "Setting $database to single-user mode...";
Invoke-SqlCmd "ALTER DATABASE [$database] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;";
Write-Host "Detaching $database";
Invoke-SqlCmd "EXEC sp_detach_db '$database';";
if (!(test-path $newPath))
{
[void](mkdir $newPath);
}
$clauses = @();
foreach ($oldFile in $paths)
{
$filename = [System.IO.Path]::GetFileName($oldFile);
$newFile = [System.IO.Path]::Combine($newPath, $filename);
$clauses += "(FILENAME = `"$newFile`")";
Write-Host "Moving $oldFile to $newFile";
mv $oldFile $newFile;
}
$clauses = $clauses -join ", ";
Write-Host "Re-attaching $database";
Invoke-SqlCmd "CREATE DATABASE [$database] ON $clauses FOR ATTACH;";
Write-Host "All done!";
}
You can use it like so:
你可以像这样使用它：
Move-Database -database "MyDatabase" -newPath "D:\SqlData";
I also think this method is a bit more robust than the others - what if your database is split into many files or you have a strange naming convention for logs for example?
我还认为这种方法比其他方法更健壮 - 例如，如果您的数据库被拆分为许多文件或者您有一个奇怪的日志命名约定怎么办？
回答by Kosmo
You also need to make sure the user under which the SQL Server process is running has access to the folder. For SQL2014, the default user process is "NT Service\MSSQL$SQL2014".
您还需要确保运行 SQL Server 进程的用户有权访问该文件夹。对于 SQL2014，默认用户进程是“NT Service\MSSQL$SQL2014”。
回答by Sergey Kanareykin
I needed to move multiple databases within same server, so I expanded the accepted solution a bit, to avoid copying and pasting or retyping commands. This allows moving data files in one script run, only changing the database name. Note this assumes that advanced commands are enabled; if not, use sp_configure. The data and log files are assumed to be in the same directory.
我需要在同一台服务器中移动多个数据库，因此我稍微扩展了已接受的解决方案，以避免复制和粘贴或重新键入命令。这允许在一次脚本运行中移动数据文件，只更改数据库名称。请注意，这假设启用了高级命令；如果没有，请使用sp_configure. 假定数据和日志文件位于同一目录中。
use master
DECLARE @DBName nvarchar(50)
SET @DBName = 'YOUR_DB_NAME'
DECLARE @RC int
EXEC @RC = sp_detach_db @DBName
DECLARE @NewPath nvarchar(1000)
SET @NewPath = 'E:\Data\Microsoft SQL Server\Data\';
DECLARE @OldPath nvarchar(1000)
SET @OldPath = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\';
DECLARE @DBFileName nvarchar(100)
SET @DBFileName = @DBName + '.mdf';
DECLARE @LogFileName nvarchar(100)
SET @LogFileName = @DBName + '_log.ldf';
DECLARE @SRCData nvarchar(1000)
SET @SRCData = @OldPath + @DBFileName;
DECLARE @SRCLog nvarchar(1000)
SET @SRCLog = @OldPath + @LogFileName;
DECLARE @DESTData nvarchar(1000)
SET @DESTData = @NewPath + @DBFileName;
DECLARE @DESTLog nvarchar(1000)
SET @DESTLog = @NewPath + @LogFileName;
DECLARE @FILEPATH nvarchar(1000);
DECLARE @LOGPATH nvarchar(1000);
SET @FILEPATH = N'xcopy /Y "' + @SRCData + N'" "' + @NewPath + '"';
SET @LOGPATH = N'xcopy /Y "' + @SRCLog + N'" "' + @NewPath + '"';
exec xp_cmdshell @FILEPATH;
exec xp_cmdshell @LOGPATH;
EXEC @RC = sp_attach_db @DBName, @DESTData, @DESTLog
go
