Automatyczny backup i zip baz w MS SQL Server 2000

Przedstawiam skrypt,, który automatycznie wykonuje backup wszystkich baz danych umieszczonych w aktualnej instancji SQL Server 2000.

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 „xp_cmdshell” może on być uruchomiony na wersji MS SQL Server 2000. W wersjach wyższych należy dokonać czasowej zmiany konfiguracji serwera, która nadaje uprawnienia do użycia „xp_cmdshell”

----------------------------------------------------------------------------------------------------------------
-- Script     : AutomaticBackupDataBasesFromSQLServer2000.sql
-- Author     : Dariusz Brejnak aka `DBSoft (c) 2010
-- Created    : 2010-08-19
-- 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 FOR MS SQL SERVER 2000 !!!!!!!
--            : If you want to use SQL 2005 or higher, select ONLY option 2-'Only Make Backup'
-----------------------------------------------------------------------------------------------------------------

USE master
GO

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_SCRIPTS\a\'
SET @CommadArchive ='"C:\Program Files\WinRAR\rar.exe" a '
SET @Prefix='DBSoft2000_'
-- @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.dbid)
 FROM
 [master].[dbo].[sysdatabases] 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.dbid)) = 1 and db_name(m.dbid) not in ('master','model','tempdb')
 )
 -- Not multiple line        
 GROUP BY m.dbid
 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

DBBS