Cómo respaldar todas las bases de datos SQL-Server a la vez

Escrito por Administrator. Publicado en artículos técnicos

¿Cómo respaldar todas las bases de datos de un servidor MS SQL-Server?

Hace poco tuvimos el caso de un cliente que instaló un nuevo sistema contable multi-compañía que usa como motor de base de datos el SQL-Server, y crea una base de datos independiente por cada compañía.

El problema es que durante el proceso de implementación y capacitación, se creaban y borraban con frecuencia compañías de prueba: algunas no eran importantes, pero otras duplicaban el entorno de producción, y varias debían respaldarse (pues aunque eran temporales, construirlas requería un esfuerzo que no queríamos repetir).

Y ahí surgió la duda: ¿Cómo hacer para respaldar todas las bases de datos de un servidor en forma automática, sin tener que especificar los nombres de cada una?

Respaldar SQL-Server

Al investigar diversas opciones, tales como usar un programa de un tercero, y otras alternativas, llegamos a la conclusión de que la mejor forma de hacerlo era utilizar las mismas funciones provistas por SQL-Server, en particular, el procedimiento ms_spforeachdb.  Este procedimiento ejecuta una sentencia SQL sobre cada una de las bases de datos de un servidor dado.

De modo que creamos este procedimiento (en nuestro caso lo hemos creado en MASTER, pero puede ubicarlo en la base de datos permanente que usted elija):

CREATE PROCEDURE [dbo].[sp_backupAllDatabases] @databaseName sysname AS

--
-- Respalda todas las bases de datos del servidor actual
--
BEGIN
SET NOCOUNT ON;
DECLARE @comando NVARCHAR(1000)
DECLARE @dateTime NVARCHAR(20)
--
-- Elimina los caracteres separadores de la fecha y la hora
--
SELECT @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),111),'/','') +
REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')
--
-- Prepara el comando de respaldo y lo ejecuta sobre la base de datos
-- cuyo nombre se ha recibido como parámetro.
-- NOTA: El respaldo se realiza a la carpeta C:\BACKUP
--       Modifiquelo para apuntar a la carpeta de su elección.
--
SET @comando =  'backup database ' + @databasename + ' to disk =  ' + '''' + 'c:\backup\' + @databasename + '_Full_' + @dateTime + '.BAK' + ''''
EXECUTE sp_executesql @comando
END
--
-- FIN DE PROCEDIMIENTO
--

Una vez creado el procedimiento, realizar un respaldo automático de todas las bases es tan sencillo como invocar esta sentencia:

EXEC sp_msforeachdb     'sp_backupAllDatabases ?'

Note que no especificamos la base de datos (en nuestro caso es MASTER) pues el uso del prefijo “sp_” garantiza que el procedimiento es público.

Esta sentencia se puede ejecutar por medio de un JOB y ¡listo!  Se almacenará un respaldo de cada base de datos, con su nombre, fecha y hora, en la carpeta indicada.

Si artículo ha sido de utilidad, recomiéndenos con sus amigos, y no olvide presionar "Me Gusta".

  • Paquetes WEB
  • Aplicaciones
  • Apoyo Técnico
  • Sistemas de Pesaje