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 DB_Cursor 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(