database - ubicacion - ruta de base de datos sql server

¿Cuál es la forma correcta de mover una base de datos de una unidad a otra en SQL Server 2005?

Question

No estoy buscando reubicar la base de datos a otro servidor por completo, solo muevo los archivos de datos y el archivo de registro a otra unidad con más espacio. He visto instrucciones contradictorias sobre cómo hacer esto, así que estoy buscando la forma correcta recomendada de hacerlo.

Answer 1

Separar la base de datos:

use master
go
sp_detach_db ''mydb''

Mueva los archivos de la base de datos (se muestra Xcopy a través de 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;

Reinstalar la base de datos:

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

Hay más detalles en este artículo de Microsoft KB .

Answer 2

Necesitaba mover varias bases de datos dentro del mismo servidor, así que amplié un poco la solución aceptada para evitar copiar y pegar o volver a escribir los comandos. Esto permite mover archivos de datos en una ejecución de script, solo cambiando el nombre de la base de datos. Tenga en cuenta que esto supone que los comandos avanzados están habilitados; Si no, usa sp_configure . Se asume que los datos y los archivos de registro están en el mismo directorio.

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

Answer 3

Otra forma es separar los archivos de la base de datos (base de datos-> tareas-> separar), moverlos a la nueva unidad y luego adjuntarlos nuevamente. Pero el modo descrito por Jay S es el más simple.

Answer 4

Para estar absolutamente seguro, haría lo siguiente:

1. Copia de seguridad de la base de datos a un archivo BAK.
2. Desconecte la base de datos actual o elimínela si lo desea.
3. Restaure la base de datos y cambie la ubicación de los archivos MDF y LDF.

Ejemplo de scripts:

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

Notas

La primera secuencia de comandos le proporcionará los nombres y rutas actuales que necesitará en la segunda secuencia de comandos. El segundo script restaura la base de datos al nombre que desea que tenga, pero puede cambiar el lugar donde desea almacenar. En el ejemplo anterior, mueve los archivos MDF y LDF a la unidad D :.

Answer 5

Prefiero no habilitar xp_cmdshell en mi instancia de SQL Server, así que escribí una función para hacer esto usando Powershell en su lugar; Fue especialmente útil cuando tuve que mover una gran cantidad de bases de datos.

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!";
}

Puedes usarlo así:

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

También creo que este método es un poco más robusto que los otros: ¿qué sucede si su base de datos se divide en muchos archivos o si tiene una convención de nombres extraña para los registros, por ejemplo?

Answer 6

También debe asegurarse de que el usuario bajo el que se ejecuta el proceso de SQL Server tenga acceso a la carpeta. Para SQL2014, el proceso de usuario predeterminado es "NT Service / MSSQL $ SQL2014".