Un procedimiento completo basado en T-SQL para migrar instancias de SQL Server — incluyendo inicios de sesión, bases de datos y reubicación de archivos — sin necesidad de herramientas externas.

Registro de cambios

• (05/04/2014) Compresión añadida para reducir el ancho de banda, el espacio y el tiempo de transferencia
• Valor de estadísticas reducido para bases de datos muy grandes
• Añadido parámetro de tipo de copia de seguridad: FULL, FULL_COPYONLY o DIFFERENTIAL
• Añadidos los parámetros Maxtransfersize y Buffercount para mejorar el rendimiento de las copias de seguridad (advertencia: requiere más memoria)
• Corregido un error para mostrar mensajes de error correctos de cualquier tipo

Introducción

Hay muchas formas de migrar bases de datos: backup-restore, detach-copy-attach, herramienta de copia de bases de datos SQL, entre otras. Este procedimiento utiliza el enfoque de backup-restore — más flexible que otros y requiriendo menos ancho de banda y espacio en disco. Cada DBA debe elegir la estrategia adecuada para cada escenario, pero este enfoque es uno de los más adaptables para instancias complejas con muchas bases de datos grandes.

Los pasos principales son:

1. Migrar los inicios de sesión de la instancia conservando el SID original y el hash de contraseña
2. Realizar copias de seguridad de las bases de datos y transferirlas al sitio de destino
3. Restaurar las bases de datos reubicando los archivos de datos en el nuevo sistema de archivos

Paso 1 — Migrar inicios de sesión con SID y hash de contraseña originales

Este paso transfiere los inicios de sesión desde la instancia de origen preservando tanto el hash de contraseña original como el SID original. La alineación del SID entre los inicios de sesión de la instancia y los usuarios de la base de datos es fundamental para evitar usuarios huérfanos tras la restauración. El procedimiento difiere según la versión de SQL Server.

Referencias oficiales de MS: KB246133 (SQL 2000) · KB918992 (SQL 2005, 2008, 2012)

Ejecute el siguiente script en la instancia de origen y guarde la salida completa como texto (ALT+T en SSMS antes de ejecutar), luego ejecute la salida en la instancia de destino:

USE [master]
GO

/****** Object: UserDefinedFunction [dbo].[fn_hexadecimal] *****/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fn_hexadecimal]
(
    @binvalue varbinary(256)
)
RETURNS VARCHAR(256)
AS
BEGIN
    DECLARE @charvalue varchar(256)
    DECLARE @i int
    DECLARE @length int
    DECLARE @hexstring char(16)
    SELECT @charvalue = '0x'
    SELECT @i = 1
    SELECT @length = DATALENGTH (@binvalue)
    SELECT @hexstring = '0123456789ABCDEF'
    WHILE (@i <= @length)
    BEGIN
        DECLARE @tempint int
        DECLARE @firstint int
        DECLARE @secondint int
        SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
        SELECT @firstint = FLOOR(@tempint/16)
        SELECT @secondint = @tempint - (@firstint*16)
        SELECT @charvalue = @charvalue +
        SUBSTRING(@hexstring, @firstint+1, 1) +
        SUBSTRING(@hexstring, @secondint+1, 1)
        SELECT @i = @i + 1
    END
    return @charvalue
END
GO

SET NOCOUNT ON
GO

PRINT '-- Create Windows logins'
SELECT 'IF NOT EXISTS (SELECT * FROM master.sys.server_principals WHERE [name] = ''' + [name] + ''')
CREATE LOGIN [' + [name] + '] FROM WINDOWS WITH DEFAULT_DATABASE=[' +
default_database_name + '], DEFAULT_LANGUAGE=[us_english]
GO
'
FROM master.sys.server_principals
WHERE type_desc IN ('WINDOWS_GROUP', 'WINDOWS_LOGIN')
AND [name] NOT LIKE 'BUILTIN%'
AND [NAME] NOT LIKE 'NT AUTHORITY%'
AND [name] NOT LIKE '%SQLServer%'
GO

PRINT '-- Create SQL Logins'
SELECT 'IF NOT EXISTS (SELECT * FROM master.sys.sql_logins WHERE [name] = ''' + [name] + ''')
CREATE LOGIN [' + [name] + ']
WITH PASSWORD=' + [master].[dbo].[fn_hexadecimal](password_hash) + ' HASHED,
SID = ' + [master].[dbo].[fn_hexadecimal]([sid]) + ',
DEFAULT_DATABASE=[' + default_database_name + '], DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=' + CASE WHEN is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END + ', CHECK_POLICY=OFF
GO
IF EXISTS (SELECT * FROM master.sys.sql_logins WHERE [name] = ''' + [name] + ''')
ALTER LOGIN [' + [name] + ']
WITH CHECK_EXPIRATION=' +
CASE WHEN is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END + ', CHECK_POLICY=' +
CASE WHEN is_policy_checked = 1 THEN 'ON' ELSE 'OFF' END + '
GO
'
FROM master.sys.sql_logins
WHERE type_desc = 'SQL_LOGIN'
AND [name] NOT IN ('sa', 'guest')
GO

Paso 2 — Copia de seguridad de bases de datos y transferencia al destino

Este metascript genera y ejecuta el script de copia de seguridad, omitiendo y registrando errores sin detener todo el proceso — esencial en instancias grandes con muchas bases de datos:

----------------------------------------------------
-- CONFIGURE HERE
----------------------------------------------------
-- DESTINATION PATH
    declare @destpath nvarchar(255) = 'G:ackup'
-- BACKUP TYPE: FULL_COPYONLY | FULL | DIFF
    declare @bck_type nvarchar(25) = 'FULL_COPYONLY'
-- DATABASE LIST
    create table #dbs (dbname nvarchar(255))
    insert into #dbs values('db1')
    insert into #dbs values('db2')
    insert into #dbs values('db3')
-- To backup ALL user databases:
-- Insert into #dbs select name from master..sysdatabases
--   where name not in ('master','model','msdb','tempdb')
----------------------------------------------------

declare @aDB nvarchar(255)
declare @aQry nvarchar(2048)

DECLARE em_cur CURSOR FOR select * from #dbs
OPEN em_cur
FETCH NEXT FROM em_cur INTO @aDB
WHILE @@FETCH_STATUS = 0
BEGIN
   set @aQry = 'BACKUP DATABASE [' + @aDB + '] TO DISK = ''' +
                 @destpath + @aDB + '.bak'' WITH NOFORMAT, NOINIT, NAME = N''' + @aDB +
                 '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 3, COMPRESSION'
   -- ADD buffercount=64, maxtransfersize=4194304 for better performance
   -- WARNING: requires more memory — use only when safe to do so
   if (@bck_type='DIFF')
       set @aQry = @aQry + ',DIFFERENTIAL'
   else if (@bck_type='FULL_COPYONLY')
       set @aQry = @aQry + ',COPY_ONLY'

   begin try
       print '-------------'
       print @aQry
       exec (@aQry)
       print '-------------'
   end try
   begin catch
       print 'ERROR during backup: ' + ERROR_MESSAGE()
   end catch

   FETCH NEXT FROM em_cur INTO @aDB
END

CLOSE em_cur
DEALLOCATE em_cur
drop table #dbs

Paso 3 — Restaurar bases de datos con reubicación de archivos

Ejecute este metascript en la instancia de origen para generar el script de restauración, luego ejecute la salida en la instancia de destino para restaurar todas las bases de datos con los archivos de datos reubicados en las rutas correctas:

set nocount on
----------------------------------------------------
-- CONFIGURE HERE
----------------------------------------------------
-- backup files position
    declare @restorepath nvarchar(max) = 'G:ackup'
-- data files destination
    declare @pathdata nvarchar(max) = 'D:DataPath'
-- log files destination
    declare @pathlog nvarchar(max)  = 'L:LogPath'
-- DATABASE LIST
    create table #dbs (dbname nvarchar(255))
    insert into #dbs values('db1')
    insert into #dbs values('db2')
    insert into #dbs values('db3')
-- Insert into #dbs select name from master..sysdatabases
----------------------------------------------------

declare @aDB nvarchar(255)
declare em_cur CURSOR FOR select * from #dbs

OPEN em_cur
FETCH NEXT FROM em_cur INTO @aDB
WHILE @@FETCH_STATUS = 0
BEGIN
    select 'RESTORE DATABASE [' + @aDB + '] from disk=N''' + @restorepath + @aDB + '.bak'' WITH '
    union all
    select
    CASE
    WHEN m.type_desc = 'ROWS' THEN 'MOVE N''' + m.name + ''' TO N''' +
    @pathdata + RIGHT(physical_name, CHARINDEX('', REVERSE(physical_name))-1) + ''','
    WHEN m.type_desc = 'LOG'  THEN 'MOVE N''' + m.name + ''' TO N''' +
    @pathlog  + RIGHT(physical_name, CHARINDEX('', REVERSE(physical_name))-1) + ''','
    END
    from sys.master_files m
    inner join sys.databases d on (m.database_id = d.database_id)
    where d.name=@aDB
    union all
    select 'NOUNLOAD, STATS = 10'
    print 'GO'

    FETCH NEXT FROM em_cur INTO @aDB
END

CLOSE em_cur
DEALLOCATE em_cur
drop table #dbs

Tome el script generado y ejecútelo en la instancia de destino para restaurar cada base de datos en la posición correcta.

Nota: Solución alternativa para el hash de inicio de sesión en SQL Server 2012

(Actualización 22-07-2012) En las pruebas con SQL 2012, el procedimiento oficial de MS para exportar los hashes de contraseñas de inicio de sesión no funciona correctamente. Para exportar inicios de sesión con el hash de contraseña original Y el SID original, use esta solución alternativa:

select
    convert(varchar(50), 'CREATE LOGIN [' + name + '] with password='),
    CAST(password AS VARBINARY(26)),
    'HASHED,SID=',
    CAST(sid AS VARBINARY(26))
from sys.syslogins
where password is not null