Cómo Restaurar Múltiples Bases de Datos en SQL Server (3 Métodos)

¿Puedes restaurar múltiples bases de datos SQL a la vez?

Es lamentable que la interfaz gráfica de SSMS solo admita la restauración de una base de datos a la vez. Si respaldaste varias bases de datos en varios archivos .bak, ¿puedes restaurarlos todos a la vez?

La respuesta es SÍ. En general, puedes usar un script T-SQL válido para restaurar varias bases de datos de una sola vez, pero solo si los archivos de respaldo tienen el nombre exacto de las bases de datos correspondientes y no contienen información adicional como la fecha/hora.

Si deseas hacerlo de manera más flexible, también hay una alternativa fácil con interfaz gráfica. Simplemente elige el método que prefieras.

Requisitos previos para restaurar múltiples bases de datos en SQL Server

Restaurar múltiples bases de datos en SQL Server puede ser un proceso complejo. Para minimizar el tiempo de inactividad, hay varios requisitos previos que deben cumplirse antes de comenzar:

• Tener privilegios administrativos y acceso a los archivos de respaldo.
• Solo debe haber una copia de seguridad de base de datos por archivo de respaldo.
• El archivo de respaldo de la base de datos solo puede contener dos archivos en total: archivo de registro (log) y archivo de datos.
• Dado que utilizamos el nombre de la base de datos para nombrar sus archivos físicos en el disco, los caracteres que se pueden utilizar para los nombres de archivos físicos en la nueva base de datos que estamos restaurando deben estar permitidos.

Forma 1. Restaurar múltiples bases de datos desde archivos .bak utilizando xp_cmdshell

Puedes usar un script para restaurar múltiples bases de datos en SQL Server desde una carpeta. Pero antes, por favor, conecta a la instancia y habilita xp_cmdshell.

1. Haz clic en Nueva consulta e ingresa el siguiente comando:

-- Para permitir cambiar opciones avanzadas.
EXEC sp_configure "show advanced options", 1;
GO
-- Para actualizar el valor configurado actualmente para opciones avanzadas.
RECONFIGURE;
GO
-- Para habilitar la característica.
EXEC sp_configure "xp_cmdshell", 1;
GO
-- Para actualizar el valor configurado actualmente para esta característica.
RECONFIGURE;
GO

Ejecuta la consulta y la característica se habilitará.

2. A continuación, puede restaurar varias bases de datos de SQL Server utilizando el script (por favor, reemplace "D:\backup\" con su propia carpeta que contiene todos los archivos de respaldo):

DECLARE @FilesCmdshell TABLE (
outputCmd NVARCHAR (255)
)
DECLARE @FilesCmdshellCursor CURSOR
DECLARE @FilesCmdshellOutputCmd AS NVARCHAR(255)

INSERT INTO @FilesCmdshell (outputCmd) EXEC master.sys.xp_cmdshell "dir /B D:\backup\*.bak"
SET @FilesCmdshellCursor = CURSOR FOR SELECT outputCmd FROM @FilesCmdshell

OPEN @FilesCmdshellCursor
FETCH NEXT FROM @FilesCmdshellCursor INTO @FilesCmdshellOutputCmd
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @cmd NVARCHAR(MAX) = "RESTORE DATABASE [" SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX(".", @FilesCmdshellOutputCmd)) "] FROM DISK = N"'D:\backup\" SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX(".", @FilesCmdshellOutputCmd)) ".bak"' WITH FILE = 1, NOUNLOAD, STATS = 10"
EXEC(@cmd)

FETCH NEXT FROM @FilesCmdshellCursor INTO @FilesCmdshellOutputCmd
END

✎Nota: Si recibe un error "El final del registro de la base de datos no ha sido respaldado. Utilice BACKUP LOG WITH NORECOVERY para respaldar el registro si contiene trabajo que no desea perder. Utilice la cláusula WITH REPLACE o WITH STOPAT de la instrucción RESTORE para simplemente sobrescribir el contenido del registro", la solución es tal como lo indica el mensaje.

Por ejemplo, podría modificar el comando de restauración a "...WITH FILE = 1, REPLACE, NOUNLOAD, STATS = 10"

Forma 2. Generar script para restaurar todas las bases de datos en SQL Server

Alternativamente, podrías usar un script para generar comandos de restauración para todas las bases de datos en SQL Server, y combinarlos en un nuevo script para ejecutarlos.

1. Haz clic en Nueva Consulta e ingresa los siguientes comandos en la ventana SQLQuery:

DECLARE @folderpath VARCHAR (1000)
SELECT @folderpath = "D:\Backup\" -- Ubicación de la copia de seguridad
SELECT "RESTORE DATABASE[" NAME "] FROM DISK = "'" @folderpath name ".bak"' WITH NORECOVERY,
REPLACE, STATS = 5"
FROM master.sys.databases
WHERE name NOT IN ("master","model","msdb","tempdb","distribution")

Esto generará una serie de comandos para restaurar múltiples bases de datos en SQL Server a partir de archivos bak con los mismos nombres.

2. Haz clic derecho en cualquier comando en Resultados, elige Seleccionar Todo y luego Copiar (o usa Ctrl A y Ctrl C).

3. Pega estos comandos en la ventana SQLQuery como un nuevo script para Ejecutar. Esto restaurará todas las bases de datos de SQL Server a partir de los archivos bak con los nombres de archivo correspondientes.

Forma 3. Hacer copia de seguridad y restaurar múltiples bases de datos de SQL con una interfaz gráfica de usuario (la más fácil)

Los scripts anteriores solo son aplicables cuando tus archivos bak se llaman exactamente igual que las bases de datos de SQL, lo cual es bastante restrictivo en la práctica. Por lo tanto, me gustaría proporcionar una forma más conveniente de hacer copias de seguridad y restaurar múltiples bases de datos, e incluso la instancia completa.

AOMEI Cyber Backup es una solución de gestión centralizada confiable para hacer copias de seguridad y restaurar bases de datos SQL en todas las computadoras de escritorio, laptops y servidores dentro de una LAN.

Funciona en Windows 11/10/8.1/8/7, Windows Server 2022/2019/2016/2012 (R2)/2008 (R2), y admite copias de seguridad de SQL Server 2005 a 2022. Además de la base de datos SQL, también admite copia de seguridad de máquinas virtuales de Hyper-V y VMware.

• Ejecute el archivo .exe descargado para instalar AOMEI Cyber Backup y abra su interfaz principal. Haga clic en Dispositivo Fuente -> Microsoft SQL -> Agregar Microsoft SQL.

• Seleccione Descargar programa proxy o Copiar enlace para descargar el programa Agente e instálelo en el dispositivo con SQL Server instalado. Luego, cambie a Proxy ya instalado para seleccionar el dispositivo.
• A continuación, haga clic en -> Autenticación para validar la instancia de la base de datos. Puede elegir Autenticación de Windows o Autenticación SQL.

Eso es todo, ahora puede programar la copia de seguridad de datos para el servidor SQL agregado.

◉ Cómo hacer copia de seguridad de múltiples bases de datos en SQL Server:

1. Haga clic en Tarea de copia de seguridad -> Crear nueva tarea, y elija el tipo de copia de seguridad como Copia de seguridad de Microsoft SQL.

2. Haz clic en Nombre del dispositivo para especificar la instancia SQL y las bases de datos que deseas respaldar. Puedes seleccionar una o varias bases de datos según tus necesidades.

3. Selecciona un almacenamiento Destino para guardar tus respaldos de SQL. Puedes especificar una ruta local o una ruta de red.

4. Configura el Programar para ejecutar el respaldo de la base de datos de SQL diariamente, semanalmente o mensualmente, y elige el método de respaldo como Completo, Incremental o Diferencial.

Haz clic en Iniciar respaldo para crear y ejecutar la tarea. Una vez finalizado, lo encontrarás en la pestaña de Tareas de respaldo.

✍Más características útiles:

• Limpieza del respaldo te ayuda a eliminar automáticamente las versiones antiguas del respaldo y a ahorrar espacio de almacenamiento.
• Notificación por correo electrónico te permite recibir notificaciones por correo electrónico cuando la tarea sea anormal o exitosa.
• Archivo te ayuda a archivar los archivos de respaldo en un almacenamiento de AWS S3.

◉ Cómo restaurar varias bases de datos desde el respaldo:

1. Haz clic en Tareas de respaldo en la barra de menú izquierda, encuentra la tarea que deseas restaurar y haz clic en -> Restaurar.

2. Seleccione una versión de respaldo que desee restaurar las bases de datos de SQL.

3. A continuación, seleccione la ubicación de destino a la que desea restaurar. Hay 2 opciones:

• Restaurar a la ubicación original (por defecto): recuperación en el lugar que puede revertir las bases de datos de SQL a una versión anterior.
• Restaurar a una ubicación nueva: seleccione otro destino para realizar la recuperación fuera de espacio. Puede especificar el nombre de la nueva base de datos y modificar la ubicación de almacenamiento.

Sobrescribir la base de datos con el mismo nombre: Si activa esta opción, las bases de datos respaldadas sobrescribirán las bases de datos de destino con los mismos nombres. Si no la activa, las bases de datos de destino con los mismos nombres se omitirán durante la restauración.

4. Después de todas las configuraciones, haga clic en Iniciar Restauración para comenzar el proceso de restauración y espere pacientemente a que se complete. Puede hacer clic en el botón para ver los detalles de restauración o cancelarlo.

✍Notas:

• "Restaurar a la ubicación original" debe confirmar la existencia de la ubicación original. De lo contrario, solo puede seleccionar "Restaurar a una nueva ubicación".
• "Restaurar a la ubicación original" sobrescribirá o eliminará los datos originales de la base de datos, si la base de datos original tiene datos importantes, se recomienda elegir "Restaurar a una nueva ubicación".

Conclusión

La interfaz gráfica de usuario de SSMS le permite restaurar solo una base de datos a la vez. Si desea restaurar varias bases de datos en SQL Server al mismo tiempo, el enfoque más común es utilizar scripts de T-SQL. Sin embargo, tiene restricciones estrictas en los nombres de archivo de respaldo y dificulta las operaciones como restaurar bases de datos en otra instancia. Por lo tanto, también podría intentar el software de respaldo de SQL: AOMEI Cyber Backup.

Facilita mucho la operación. Por ejemplo, puede hacer una copia de seguridad automática de las bases de datos de SQL con solo unos clics y restaurar varias bases de datos a la vez mediante una operación igualmente sencilla.