Mssql express edition 2008 dump

Ce script permet de backuper des bases mssql sous Mssql Express edition, sans avoir besoin de lui spécifier les bases. Il va lister toutes les bases et en exclure certaine (ici on exclut 'master','model','msdb','tempdb')

Commande Sql

• C:\agarik\Backup\backup_sql.bat > C:\Agarik\Backup\Log\output.txt 2> C:\Agarik\Backup\Log\output_err.txt
• backup_sql.bat : "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -S (local)\VIM_SQLEXP -i C:\agarik\Backup\SQLExpressBackups.sql

Script de dump

• Fichier C:\Test\SQLExpressBackups.sql

DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name

 
-- specify database backup directory
SET @path = 'C:\Backup\'  

 
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

 
DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases

 
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

 
WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName  

 
       FETCH NEXT FROM db_cursor INTO @name   
END   

 
CLOSE db_cursor   
DEALLOCATE db_cursor

Fichier de sortie de résultat du dump

• Résultat du fichier de sortie C:\test\output.txt

Processed 280 pages for database 'RSA', file 'RSA_DATA' on file 1.
Processed 304 pages for database 'RSA', file 'RSA_INDEX' on file 1.
Processed 3 pages for database 'RSA', file 'translog' on file 1.
BACKUP DATABASE successfully processed 587 pages in 1.139 seconds (4.023 MB/sec).
Processed 73464 pages for database 'VIM_VCDB', file 'VIM_VCDB_dat' on file 1.
Processed 9 pages for database 'VIM_VCDB', file 'VIM_VCDB_log' on file 1.
BACKUP DATABASE successfully processed 73473 pages in 46.037 seconds (12.468 MB/sec).
Processed 2144 pages for database 'VIM_UMDB', file 'VIM_UMDB_dat' on file 1.
Processed 8 pages for database 'VIM_UMDB', file 'VIM_UMDB_log' on file 1.
BACKUP DATABASE successfully processed 2152 pages in 1.369 seconds (12.275 MB/sec).

Supervision du dump

Ici on vérifie que le fichier de log (output_err.txt) vers lequel est redirigé la sortie d'erreur

set FULLNAME=console1,reverse,randstad,fr

for /F %%A in ("C:\agarik\Backup\Log\output_err.txt") do if %%~zA equ 0 GOTO GREEN

:GREEN
set COLOR=green
C:\agarik\vision\visionsvc.exe /file 10.252.15.235 1984 status+86400 %FULLNAME%.backup %COLOR% C:\agarik\Backup\Log\output.txt
GOTO END

:RED
set COLOR=red
C:\agarik\vision\visionsvc.exe /file 10.252.15.235 1984 status+86400 %FULLNAME%.backup %COLOR% C:\agarik\Backup\Log\output_err.txt

:END

Purge des dumps

-- purge des fichiers de plus de 3 jours
forfiles /p C:\Backup /m *.BAK /d -3 /c "cmd /c del @file"