Backup completo en SQL 2005 Express

La versión Express de SQL no dispone de ninguna herramienta para programar copias de seguridad automatizadas. Aunque sí se pueden realizar cópias manualmente ya sea por la interfaz de SQL Server Management Studio Express o  por el comando de T-SQL backup database

Para poder mantener unas copias de un sistema SQL 2005 Express de forma automática podemos crear un procedimiento que ejecute el comando de backup sobre cada una de las bases de datos del sistema y guarde los archivos en el disco duro.

La creación del procedimiento de copia sería algo así:

USE [master]
GO

CREATE Proc [dbo].[spBackupsSQL]  As
DECLARE @nombreBBDD varchar(255),
        @nombreArchivoBackup varchar(255),
        @comandoBorrado varchar(300),
        @fecha varchar(255),
        @existe int

EXEC sp_configure 'show advanced options',1
RECONFIGURE
EXEC sp_configure xp_cmdshell,1
RECONFIGURE

DECLARE miCursor CURSOR
FOR
select name from sys.databases where name <> 'tempbd'

OPEN miCursor

    FETCH miCursor INTO @nombreBBDD

WHILE(@@FETCH_STATUS =
0)
    BEGIN        

    SET @fecha = CONVERT(varchar(255),DATEPART(d,GETDATE()));
    SET @fecha = @fecha +'_'+CONVERT(varchar(255),DATEPART(m,GETDATE()));
    SET @fecha = @fecha +'_'+CONVERT(varchar(255),DATEPART(yyyy,GETDATE()));

    SET @nombreArchivoBackup = 'C:\'+@nombreBBDD+'_'+@fecha+'.bak'

    -- Miramos si existe el archivo   
    EXEC xp_fileexist @nombreArchivoBackup, @existe output

    SET @comandoBorrado = 'del '+@nombreArchivoBackup

    IF @existe = 1
    EXEC xp_cmdshell @comandoBorrado

    backup database @nombreBBDD to Disk=@nombreArchivoBackup

    FETCH miCursor INTO @nombreBBDD
    END
CLOSE miCursor
DEALLOCATE miCursor

EXEC sp_configure xp_cmdshell,0
RECONFIGURE
EXEC sp_configure 'show advanced options',0
RECONFIGURE

El código anterior crea un procedimiento almacenado que carga las bases de datos con la select de la tabla sys.databases en un cursos que recorremos y vamos generando un archivo .bak de cada una de las bases de datos.

Además, verificamos que no exista el archivo antes, en el caso de existir, elimina el archivo para crear el nuevo. El proceso de borrado se realiza con el comando del y por ello activamos la ejecución de comandos con sp_configure xp_cmdshell.

Automatización con .bat

Para poder decir que realmente hemos automatizado todo esto, nos hace falta que el procedimiento se ejecute periodicamente, ya que por sí solo no es posible. Lo podemos realizar con un archivo .bat, contendrá lo siguiente:

SQLCMD -q "Exec spBackupsServidorSQL" -Uusername -Ppassword -Sservername

Recuerda que spBackupsSQL es el nombre del procedimiento que queremos ejecutar.

Por último, con el programador de tareas de Windows ejecutamos el .bat

5 Comentarios en “Backup completo en SQL 2005 Express”

solo como una idea, seria bueno comprimirlo en zip, para que ocupe menos espacio y tambien copiarlo o moverlo a otro servidor. (recomiendo 7-Zip por ser opensource)
Aquí el código un bat de uno de los servidores que me toco hecharle mano:
————————————————-
@echo off
cd “D:”
sqlcmd -S PVC\COMPAC -E -i D:\BackupBD\backup.sql

set Nombre= BD%date%%time%
set Nombre=%Nombre:/=%
set Nombre=%Nombre: =%
set Nombre=%Nombre::=%
set Nombre=%Nombre:,=%
echo %Nombre%

cd “D:\BackupBD\7-ZipPortable\App\7-Zip”
7z a -t7z %Nombre%.zip *.bak -r
Copy “%Nombre%.zip” “M:\CompacWindows\Backup_SQL_Compaqi”

del “%Nombre%.zip”
del “*.bak”
————————————————-
lo explico brevemente:
las primeras 3 líneas es un procedimiento parecido al que describes.
Después viene lo de sacar el nombre de la BD
Ubicamos el ejecutable del 7-Zip para ejecutarlo con los parametros adecuados y comprimir la BD.
Y por último copiar a una unidad y borrar el respaldo para que nos quede espacio disponible.

thedavis dice:

Buena idea esto de la compresión :)

Carlos dice:

Enhorabuena por el script.

Tan solo una cosita, cuando ejecuto el script me acaba asi…:

——————————————————————————–
BACKUP DATABASE procesó correctamente 289 páginas en 0.351 segundos (6.432 MB/s)
.
Se ha cambiado la opción de configuración ‘xp_cmdshell’ de 1 a 0. Ejecute la ins
trucción RECONFIGURE para instalar.
Se ha cambiado la opción de configuración ’show advanced options’ de 1 a 0. Ejec
ute la instrucción RECONFIGURE para instalar.
1>
——————————————————————————

¿Que puedo hacer para que me finalice correctamente? gracias.

Alberto dice:

Gracias por el aporte me funciono. sin embargo, tengo la misma pregunta que carlos si me pudieras ayudar con esto

juancito dice:

muy buen aporte, me funcion correctamente, solo hay que tener en cuenta los permisos que tengas como usuario

Dejar un comentario

*