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.