martes, 08 de abril de 2014 a las 12:48hs por Gustavo Cantero (The Wolf)
Uno de los problemas que podemos tener en un motor de base de datos utilizado para desarrollo es la constante creación y eliminación de bases, principalmente cuando tenemos muchos proyectos, lo que nos dificulta la tarea de hacer backups programados. Para resolver esto en Scientia hicimos un script que toma del diccionario de datos el nombre de cada base y hace un backup de las mismas guardándolos en una carpeta de la red (en nuestro caso dentro de un NAS), dentro de una carpeta creada con la fecha actual.
Bueno, vamos al código….
-- Declaración de las variables
DECLARE @PathBase nvarchar(500)
DECLARE @Path nvarchar(500)
DECLARE @File nvarchar(500)
DECLARE @Cmd nvarchar(500)
DECLARE @DBName nvarchar(100)
DECLARE @Desc nvarchar(500)
-- Establezco el path donde voy a guardar los backups
SET @PathBase = '\\nas\Backups\SQL Server\ '
-- Creo un subdirectorio con la fecha actual con el formato "yyyymmdd"
SET @Path = @PathBase + CONVERT(char(8), GetDate(), 112) + '\ '
SET @Cmd = 'MD "' + @Path + '"'
EXEC master..xp_cmdshell @Cmd
-- Busco el nombre de todas las bases de datos, excepto las de sistema
DECLARE db_cursor CURSOR FOR
SELECT Name
FROM master.dbo.sysdatabases
WHERE Category != 16 AND Name NOT IN ('master','model','msdb','tempdb')
ORDER BY Name
-- Creo un cursor para recorrer las bases
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
-- Hago el backup de cada base, agregándole descripción
-- y estableciendo que debe utilizarse compresión
SET @File = @Path + @DBName + '.bak'
SET @Desc = 'Backup de la base ' + @DBName + ' (' + CONVERT(varchar(100), GetDate(), 0) + ')'
BACKUP DATABASE @DBName TO DISK = @File WITH FORMAT, COMPRESSION, MEDIADESCRIPTION = @Desc
END TRY
BEGIN CATCH
END CATCH;
FETCH NEXT FROM db_cursor INTO @DBName
END
-- Elimino el cursor
CLOSE db_cursor
DEALLOCATE db_cursor
Este script lo ejecutamos con un trabajo en el SQL Server periódicamente, para asegurarnos de no perder nada en caso de falla de los discos.
Espero que les sea de utilidad.
Suerte!
Categoria SQL Server | Etiquetas: backup, SQL Server
Gracias por el aporte…
De nada, Carlos.
Saludos!
[…] igual que contaba en el artículo “Hacer backup de todas las bases de un SQL Server“, uno de los problemas que podemos tener en un motor de base de datos utilizado para […]
Gracias
De nada.
Suerte!
Gracias por su aporte, muy bueno
Soy nuevo en esto, en donde tengo que escribir este script?
Buenos días Manuel.
Este script lo podés ejecutar en el SQL Server Management Studio, si es que lo querés ejecutar una sóla vez, o podés crear una tarea programada para que lo realice periódicamente (acá tenés un artículo que habla sobre eso: Automatizar las tareas administrativas).
Suerte!
Muchas gracias, quite la compresión para poder ejecutarlo en SQL 2005, ademas de que agregué unas lineas para poder usar el EXEC, me quedó así:
-- To allow advanced options to be changed. EXEC sp_configure 'show advanced options', 1; GO -- To update the currently configured value for advanced options. RECONFIGURE; GO -- To enable the feature. EXEC sp_configure 'xp_cmdshell', 1; GO -- To update the currently configured value for this feature. RECONFIGURE; GO -- Declaración de las variables DECLARE @PathBase nvarchar(500) DECLARE @Path nvarchar(500) DECLARE @File nvarchar(500) DECLARE @Cmd nvarchar(500) DECLARE @DBName nvarchar(100) DECLARE @Desc nvarchar(500) -- Establezco el path donde voy a guardar los backups SET @PathBase = 'D:\RespaldosBD\' -- Creo un subdirectorio con la fecha actual con el formato "yyyymmdd" SET @Path = @PathBase + CONVERT(char(8), GetDate(), 112) + '\' SET @Cmd = 'MD "' + @Path + '"' --EXEC master..xp_cmdshell @Cmd EXEC master..xp_cmdshell @Cmd -- Busco el nombre de todas las bases de datos, excepto las de sistema DECLARE db_cursor CURSOR FOR SELECT Name FROM master.dbo.sysdatabases WHERE Category != 16 AND Name NOT IN ('master','model','msdb','tempdb') ORDER BY Name -- Creo un cursor para recorrer las bases OPEN db_cursor FETCH NEXT FROM db_cursor INTO @DBName WHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRY -- Hago el backup de cada base, agregándole descripción -- y estableciendo que debe utilizarse compresión SET @File = @Path + @DBName + '.bak' SET @Desc = 'Backup de la base ' + @DBName + ' (' + CONVERT(varchar(100), GetDate(), 0) + ')' BACKUP DATABASE @DBName TO DISK = @File WITH FORMAT, MEDIADESCRIPTION = @Desc END TRY BEGIN CATCH END CATCH; FETCH NEXT FROM db_cursor INTO @DBName END -- Elimino el cursor CLOSE db_cursor DEALLOCATE db_cursorMuchas gracias Miguel!
Saludos.
Que tal muy buen aporte con este script pude respaldar todo ya que tengo 2005, ahora viene el paso contrario, con que script puedo restaurar de nuevo todos los respaldos?
Hola Francisco.
Deberías hacer un script parecido al que hicimos que devuelve todas las bases, y por cada una ejecutar el comando RESTORE.
Saludos.
Un muy buen aporte. GRACIAS
Excelente Script. Gracias por el aporte
Estimado , muy interesante lo que indica , se pueden efectuar en horario productivo ? ralentiza mucho el motor de datos en mi caso slqserver 2017
Hola Guillermo.
El hacer backups hace más lento al servidor, ya que realiza un consumo intensivo del disco.
Se puede hacer en producción, pero te recomiendo algo como un backup diferencial cada día y uno completo los fines de semana o a principios de mes, y siempre en el horario en el que menos se utiliza el servidor.
Saludos.