Automatyczny backup i zip baz w MS SQL Server 2005/2008

Zmęczony ręcznym tworzeniem backupów z SQL Servera oraz pamiętaniem, które bazy należy kopiować zacząłem szukać rozwiązania do automatycznego backupu. Na forum CodeGuru.pl znalazłem namiastkę rozwiązania przedstawiono przez „alazif” . Po modyfikacji i dopisaniu kodu stworzyłem skrypt, który automatycznie wykonuje backup wszystkich baz danych umieszczonych w aktualnej instancji serwera. Nazwy wszystkie kopii składają się z prefixu (w tym wypadku używam nazwy instancji serwera), roku, miesiąca i dnia wykonania kopi oraz z samej nazwy bazy danych. Dodatkowo stworzyłem fragment, który jest odpowiedzialny za wykonanie pakowania, ( za pomocą zipa, rara, 7zipa itp – odpowiednią komendę podaje się w zmiennej tekstowej) backapów baz danych. W zmiennej @choice ustawia się odpowiednie parametry dotyczące sposobu wykonywania kopii, i tak dla: 0 – Wykonywany jest backup, który jest pakowany a następnie pliki *.bak są zakasowywane (opcja oszczędzająca miejsce na dysku) 1 – Wykonywany jest backup, który jest pakowany, pliki *.bak nie są kasowane (podwójne zabezpieczenie) 2 – Wykonywany jest tylko sam backup. Pakowanie nie jest wykonywane. W zmiennej @VirtualPath ustawiamy ścieżkę docelową składowania backapu. W zmiennej @Prefix ustawiamy przedrostek do nazwy kopii. W zmiennej @CommandArchive ustawiamy komendę uruchamiającą archiwizator Z uwagi na to, że skrypt używa widoku systemowego „sys.master_files” oraz na czasową zmianę konfiguracji serwera, która umożliwia dopuszcza użycie „xp_cmdshell” może on być uruchomiony na wersji MS SQL Server 2005/2008/2008R2.

----------------------------------------------------------------------------------------------------------------
-- Script     : AutomaticBackupDataBasesFromSQLServer.sql
-- Author     : Dariusz Brejnak aka `DBSoft (c) 2010
-- Created    : 2010-08-10
-- Modified   : 2010-08-19
-- Description: script designed to automatically copy the databases on the SQL server.
--              copies of the databases are then zip and delete resources
-- Other      : Only MS SQL Server 2005, 2008 and 2008R2.  !!!!!!!!! NOT FOR MS SQL SERVER 2000 !!!!!!!
-----------------------------------------------------------------------------------------------------------------

USE master
GO

-- mode show advanced options is enabled
EXEC sp_configure 'show advanced options',1
reconfigure with override;
-- unblocked access to procedure 'sys.xp_cmdshell'
EXEC sp_configure 'xp_cmdshell',1
reconfigure with override;

DECLARE @DBName varchar(255)
DECLARE @VirtualPath varchar(255)
DECLARE @CommadArchive varchar(255)
DECLARE @Prefix varchar(255)
DECLARE @DBFile varchar(256)
DECLARE @Cmd varchar(500)
DECLARE @Choice int

----------------------------------------------------------
-- DASHBOARD ---------------------------------------------
SET @VirtualPath ='c:\\_SQL_Backup\\'
SET @CommadArchive ='"C:\Program Files\\WinRAR\\rar.exe" a '
SET @Prefix='DBSoft_'
-- @Choice = 0 - Make Backup, zip and delete *.bak
-- @Choice = 1 - Make Backup, zip and don't delete *.bak
-- @Choice = 2 - Only Make Backup
SET @Choice = 0

----------------------------------------------------------

-- Declare Cursor
DECLARE DBCursor CURSOR FOR
 SELECT
 NAME   = db_name(m.database_id)
 FROM
 sys.master_files m
 WHERE
 (
 -- IS ONLINE WHEN STATE = 0 and Only look at databases to which we have access and exclude master, model, tempdb or other
 m.state = 0 and has_dbaccess(db_name(m.database_id)) = 1 and db_name(m.database_id) not in ('master','model','tempdb')
 )
 -- Not multiple line
 GROUP BY m.database_id
 ORDER BY NAME
OPEN DBCursor
FETCH NEXT FROM DBCursor INTO @DBName

WHILE @@FETCH_STATUS = 0
BEGIN
 SET @DBFile = datename(yy,getdate()) + '_'+datename(mm,getdate()) + '_' + datename(dd,getdate()) + '_' + replace(replace(@DBName,':','_'),'\\','_')
 SET @Cmd='BACKUP DATABASE [' + @DBName + '] TO  DISK = N'''+@VirtualPath +
 @Prefix +
 @DBFile + '.bak'+char(39) + ' WITH NOFORMAT, INIT,  NAME = N''' +
 @DBName + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 100'
 EXEC sys.sp_sqlexec @Cmd
 PRINT 'Backup '+@DBName+' as file name '+@Prefix+@DBFile + '.  Done ...'
 FETCH NEXT FROM DBCursor INTO @DBName
END

CLOSE DBCursor
DEALLOCATE DBCursor

-------------------------------------------------------------------------------------

IF (@Choice !=2) -- only backup (don't zip)
 BEGIN
 -- command shell text to delete zip archive
 SET @Cmd= 'exec xp_cmdshell '+char(39)+'del '+@VirtualPath+@Prefix+'_Backup_'+
 datename(yy,getdate()) + '_' +datename(mm,getdate()) + '_' + datename(dd,getdate()) +'.zip' +CHAR(39)
 -- delete
 -- PRINT @cmd
 EXEC sys.sp_sqlexec @Cmd

 --  command shell text to zip backups
 SET @Cmd= 'exec xp_cmdshell '+char(39)+@CommadArchive+' '+@VirtualPath+@Prefix+'_Backup_' +
 datename(yy,getdate()) + '_'+datename(mm,getdate()) + '_' + datename(dd,getdate()) +'.zip ' +
 @VirtualPath+@Prefix+datename(yy,getdate())+'_'+datename(mm,getdate()) + '_' + datename(dd,getdate()) +'_*.bak'+CHAR(39)
 --PRINT @Cmd
 -- zip ....
 EXEC sys.sp_sqlexec @Cmd
END

IF (@Choice = 0) -- delete *.bak
 BEGIN
 --  command shell text to delete backups
 SET @Cmd= 'exec xp_cmdshell '+char(39)+'del '+@VirtualPath+@Prefix+
 datename(yy,getdate()) + '_'+datename(mm,getdate()) + '_' + datename(dd,getdate()) +'_*.bak' +CHAR(39)
 -- delete
 -- PRINT @Cmd

 EXEC sys.sp_sqlexec @Cmd
END

-- set previous settings
EXEC sp_configure 'xp_cmdshell',0
reconfigure with override;
-- mode show advanced options is disabled
EXEC sp_configure 'show advanced options',0
reconfigure with override;

Skrypt ten następnie należy umieścić w SQL Server Agent. Wkrótce ukaże się wersja na MS SQL Server 2000.