Каков правильный способ перемещения базы данных с одного диска на другой в SQL Server 2005?

Ответ 1

Отсоединить базу данных:

use master
go 
sp_detach_db 'mydb'

Переместите файлы базы данных (показано Xcopy через xp_cmdshell):

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:

sp_attach_db 'mydb', 'D:\Data\mydb.mdf', 'E:\Log\mydb_log.ldf'

Подробнее об этом Статья Microsoft KB.

Ответ 2

Другой способ - отсоединить файлы базы данных (database- > tasks- > detach), перенести их на новый диск и затем снова подключить. Но способ, описанный Jay S, является самым простым.

Ответ 3

Чтобы быть абсолютно безопасным, я бы сделал следующее:

• Резервное копирование базы данных в файл BAK.
• Отнесите текущую базу данных в автономном режиме или удалите ее, если хотите.
• Восстановить базу данных и изменить расположение файлов MDF и LDF.

Сценарий:

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

Примечания

Первый script предоставит вам текущие имена и пути, которые вам понадобятся во втором script. Второй script восстанавливает базу данных обратно до имени, которое вы хотите, но вы можете изменить место хранения. В приведенном выше примере он перемещает файлы MDF и LDF на диск D:.

Ответ 4

Я бы предпочел не включать xp_cmdshell в свой экземпляр SQL Server, поэтому я написал функцию для этого, используя 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!";
}

Вы можете использовать его так:

Move-Database -database "MyDatabase" -newPath "D:\SqlData";

Я также считаю, что этот метод немного более устойчив, чем другие - что, если ваша база данных разделена на многие файлы или у вас есть странное соглашение об именах для журналов, например?

Ответ 5

Мне нужно было перемещать несколько баз данных на одном сервере, поэтому я немного расширил принятое решение, чтобы избежать копирования и вставки или повторного набора команд. Это позволяет перемещать файлы данных в одном запуске script, только изменяя имя базы данных. Обратите внимание, что предполагается, что расширенные команды включены; если нет, используйте 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

Ответ 6

Вам также необходимо убедиться, что пользователь, под которым работает SQL Server, имеет доступ к папке. Для SQL2014 пользовательский процесс по умолчанию - "NT Service\MSSQL $SQL2014".