Twitter Facebook Google + RSS Feed

Hacer backup de todas las bases de un SQL Server

11
SQL Server

martes, 08 de abril de 2014 a las 12:48hs por Gustavo Cantero (The Wolf)

Database BackupUno 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!


11 comentarios »

  1. Carlos A Sanchez dice:

    Gracias por el aporte…

  2. […] 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 […]

  3. Carlos dice:

    Gracias por su aporte, muy bueno

  4. manuel loria dice:

    Soy nuevo en esto, en donde tengo que escribir este script?

  5. 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_cursor
  6. Barbara dice:

    Un muy buen aporte. GRACIAS

Deja un comentario

Buscar