automatyczny backup PROBLEM

Witam!

Napisałem sobie taki oto skrypt, który ma za zadanie wykonywać pełny backup baz danych w jednej instancji serwera MS SQL. Problem w tym, że nie jestem w stanie wykryć błędu, który jest w nim zawarty, a polega on na tym że skrypt wykonuje backup tylko jednej bazy z instancji. Baz danych jest ponad 20 z czego w ciągłym uzytku są 2 bazy w zasadzie, dlatego ręczne wykonywanie kopii zapasowych jest dość mozolne.

Oto i on:

USE master
GO

EXEC sp_configure 'show advanced options',1
reconfigure with override;
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

----------------------------------------------------------
SET @VirtualPath ='D:\_SQL_Backup\'
SET @CommadArchive ='C:\Program Files\WinRAR\rar.exe'
SET @Prefix='Backup_'
-- @Choice = 0 - Backup, zip i usunięcie poprzedniego *.bak
-- @Choice = 1 - Backup, zip
-- @Choice = 2 - Backup bez kompresji
SET @Choice = 2


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


DECLARE DBCursor CURSOR FOR
SELECT
NAME = db_name(m.database_id)
FROM
sys.master_files m
WHERE
(
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)
BEGIN
SET @Cmd= 'exec xp_cmdshell '+char(39)+'del '+@VirtualPath+@Prefix+'_Backup_'+
datename(yy,getdate()) + '_' +datename(mm,getdate()) + '_' + datename(dd,getdate()) +'.zip' +CHAR(39)
EXEC sys.sp_sqlexec @Cmd

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
end

Czy ktoś z was wie co może stwarzać takie problemy? Będę wdzięczny za każdą pomoc :)

Troche dziwny ten kod.
Czemu tak pobierasz bazy?
W czym przeszkadza backup jesli baza jest w uzyciu?

select 
    name 
from 
    sys.databases

PS. Wez pod uwage ze mozesz miec bazy w trybie Full co wiaze sie z koniecznoscia backupu logu transakcyjnego, a to z pierwszym backupem typu full.
Ogolnie najpier full backup baz (moze byc reczny) pozniej job.
Jesli nie bedziesz robil backupow logu transakcyjnego to liczba VLF'ow bazy moze zabic jej efektywne dzialanie.

Zostawiasz włączony xp_cmdshell

Tu właśnie chodzi o to aby nie robów backupów wszystkich baz ręcznie. W instancji jest ok. 20 baz z czego 2 są w ciągłym użyciu. Postawiłem sobie zadanie napisania skrypotu, który zrobi mi takie backupy, wszystkich tych baz automatycznie, ale póki co z mizernym skutkiem. Taki select jest dlatego iż było to początkowo robione pod wersje MS SQL'a 2000 teraz jest już 2008 RC2. Jeśli natomiast chodzi o logi tranzakcyjne to są one robione przy wersji z kompresją danych.

Jak w takim razie wyłączyć xp_cmdshell? ;/

tak jak wlaczasz :)

EXEC sp_configure 'xp_cmdshell', 0
reconfigure WITH override;

tylko z 0 zamiast 1;

PS jestes pewien ze mozesz uzywac kompresji? (Twoja edycja sql server na to pozwala?).
PS2. Co mowia logi jobow? Tam powinna byc dodatkowa informacja co nie poszlo. Moze sie okazac ze nie robia Ci sie backupy niektorych baz bo jest zwykly blad np tworzenia pliku

Dzięki za podpowiedź

Kompresji mogę używać bo robi to zewnętrzny program, podając tylko ścieżkę działa aż miło. W wersji 2000 nie było takiej mozliwości, to prawda, ale odkąd używam wersji 2008 nie ma z tym żadnych problemów.

Co do samych komunikatów i logów, to po ich przeglądaniu wnioskuje że, kursor, który tworzę jak gdyby jest za wcześnie wyrzucany z pamięci bo dostaje komunikat że kursora o podanej nazwie już nie ma i to za każdym uruchomieniem skryptu...

Zeby nie być gołosłownym dostaje coś takiego:

Msg 16916, Level 16, State 1, Line 54
A cursor with the name 'DB_Cursor' does not exist.

pozbadz sie tego kursora.

SELECT <tutaj przygotowujesz pelne polecenie backupu> [SQL]
INTO #TABLE
FROM 
  sys.databases d
WHERE
  d.name NOT IN ('', '', '') etc

DECLARE @SQL VARCHAR(MAX)

SET ROWCOUNT 1

WHILE EXISTS(SELECT * FROM #TABLE)
BEGIN
   SELECT 
     SQL
   FROM
     #TABLE

   EXEC(@SQL)

   DELETE FROM #TABLE
END;

SET ROWCOUNT 0

Dla 100% pewnosci mozesz sobie dodac bloki

BEGIN TRY
  ..
END TRY
BEGIN CATCH
  ...
END CATCH