Bom dia aos colegas
Tenho um script (T-SQL) em job para realizar backup full e algumas verificações para todos os bancos de dados de instância atual, porém sempre o último banco de dados não é realizada as operações pré-determinadas. O mais interessante é que as vezes é executado
as vezes não. Por exemplo, neste final de semana foi executado como esperado na Sexta, Sábado e Domingo, já na Segunda houve o erro. Realizei um teste colocando na mesma ordem os banco de dados e sempre o último que não é executado (no caso o msdb). O job
se encerra como não houvesse erro.
SQL Server 2008 R2 Enterprise SP2 x64
Windows Server 2008 R2 Enterprise SP2 x64
Segue o script e a o retorno do job.
DECLARE @banco VARCHAR(50);
DECLARE @endFixo VARCHAR(256);
DECLARE @endAtual VARCHAR(256);
DECLARE @destino VARCHAR(256);
DECLARE @nome VARCHAR(256);
DECLARE @data VARCHAR(15);
DECLARE @erro INT;
DECLARE @backupSetId INT;
DECLARE @dataExclusao VARCHAR(23);
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('tempdb' )
ORDER BY name;
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @banco;
SET @endFixo = N'\BACKUP\'
SET @erro = 1
WHILE @@FETCH_STATUS = 0
BEGIN
SET @endAtual = @endFixo + @banco +'\'
SET @dataExclusao = CONVERT(VARCHAR(23),DATEADD(day,-2,GETDATE()))
SET @erro = 0
SET @data = CONVERT(varchar,GETDATE(),112)+'_'+REPLACE(CONVERT(varchar,GETDATE(),108),':','')
SET @nome = @banco+'_backup_full_'+@data +'.bak'
SET @destino= @endAtual + @nome
--BACKUP FULL
BACKUP DATABASE @banco TO DISK = @destino WITH FORMAT, NAME = @banco, COMPRESSION, CHECKSUM, STATS=5
IF @@ERROR != 0
BEGIN
SET @erro = @erro +1
END
--VERIFICAR BACKUP
IF @erro > 0
BEGIN
SET @backupSetId = (SELECT MAX(position) FROM msdb..backupset WHERE database_name=@banco AND type='D' AND name=@banco)
RESTORE VERIFYONLY FROM DISK = @destino WITH FILE = @backupSetId
IF @@ERROR != 0
BEGIN
SET @erro = @erro +1
END
END
--CHECAR INTEGRIDADE
IF @erro > 0
BEGIN
EXEC('USE ' + @banco + ';' + 'DBCC CHECKDB WITH NO_INFOMSGS')
IF @@ERROR != 0
BEGIN
SET @erro = @erro +1
END
END
--EXCLUIR BACKUP ANTERIOR
IF @erro = 0
BEGIN
EXECUTE master.dbo.xp_delete_file 0,@endAtual,N'bak',@dataExclusao,1
END
FETCH NEXT FROM db_cursor INTO @banco;
END
CLOSE db_cursor;
DEALLOCATE db_cursor;
Executed as user: DOMAIN\user. ...processed. [SQLSTATE 01000] (Message 3211) 15 percent processed. [SQLSTATE 01000] (Message 3211) 20 percent processed. [SQLSTATE 01000] (Message 3211) 25 percent processed. [SQLSTATE 01000] (Message 3211)
30 percent processed. [SQLSTATE 01000] (Message 3211) 35 percent processed. [SQLSTATE 01000] (Message 3211) 40 percent processed. [SQLSTATE 01000] (Message 3211) 45 percent processed. [SQLSTATE 01000] (Message 3211) 50 percent
processed. [SQLSTATE 01000] (Message 3211) 55 percent processed. [SQLSTATE 01000] (Message 3211) 60 percent processed. [SQLSTATE 01000] (Message 3211) 65 percent processed. [SQLSTATE 01000] (Message 3211) 70 percent processed. [SQLSTATE
01000] (Message 3211) 75 percent processed. [SQLSTATE 01000] (Message 3211) 80 percent processed. [SQLSTATE 01000] (Message 3211) 85 percent processed. [SQLSTATE 01000] (Message 3211) 90 percent processed. [SQLSTATE 01000] (Message
3211) 95 percent processed. [SQLSTATE 01000] (Message 3211) Processed 21131984 pages for database 'DADOS', file 'DADOS_data' on file 1. [SQLSTATE 01000] (Message 4035) 100 percent processed. [SQLSTATE 01000] (Message 3211) Processed
1058 pages for database 'DADOS', file 'DADOS_log' on file 1. [SQLSTATE 01000] (Message 4035) BACKUP DATABASE successfully processed 21133042 pages in 3697.030 seconds (44.657 MB/sec). [SQLSTATE 01000] (Message 3014) 6 percent processed. [SQLSTATE
01000] (Message 3211) 10 percent processed. [SQLSTATE 01000] (Message 3211) 16 percent processed. [SQLSTATE 01000] (Message 3211) 21 percent processed. [SQLSTATE 01000] (Message 3211) 25 percent processed. [SQLSTATE 01000] (Message
3211) 31 percent processed. [SQLSTATE 01000] (Message 3211) 35 percent processed. [SQLSTATE 01000] (Message 3211) 40 percent processed. [SQLSTATE 01000] (Message 3211) 46 percent processed. [SQLSTATE 01000] (Message 3211) 50 percent
processed. [SQLSTATE 01000] (Message 3211) 57 percent processed. [SQLSTATE 01000] (Message 3211) 61 percent processed. [SQLSTATE 01000] (Message 3211) 65 percent processed. [SQLSTATE 01000] (Message 3211) 71 percent processed. [SQLSTATE
01000] (Message 3211) 76 percent processed. [SQLSTATE 01000] (Message 3211) 80 percent processed. [SQLSTATE 01000] (Message 3211) 86 percent processed. [SQLSTATE 01000] (Message 3211) 90 percent processed. [SQLSTATE 01000] (Message
3211) 95 percent processed. [SQLSTATE 01000] (Message 3211) Processed 376 pages for database 'master', file 'master' on file 1. [SQLSTATE 01000] (Message 4035) 100 percent processed. [SQLSTATE 01000] (Message 3211) Processed 3 pages
for database 'master', file 'mastlog' on file 1. [SQLSTATE 01000] (Message 4035) BACKUP DATABASE successfully processed 379 pages in 0.844 seconds (3.503 MB/sec). [SQLSTATE 01000] (Message 3014) 9 percent processed. [SQLSTATE 01000] (Message 3211)
14 percent processed. [SQLSTATE 01000] (Message 3211) 19 percent processed. [SQLSTATE 01000] (Message 3211) 24 percent processed. [SQLSTATE 01000] (Message 3211) 29 percent processed. [SQLSTATE 01000] (Message 3211) 34 percent
processed. [SQLSTATE 01000] (Message 3211) 39 percent processed. [SQLSTATE 01000] (Message 3211) 44 percent processed. [SQLSTATE 01000] (Message 3211) 49 percent processed. [SQLSTATE 01000] (Message 3211) 54 percent processed. [SQLSTATE
01000] (Message 3211) 59 percent processed. [SQLSTATE 01000] (Message 3211) 64 percent processed. [SQLSTATE 01000] (Message 3211) 69 percent processed. [SQLSTATE 01000] (Message 3211) 74 percent processed. [SQLSTATE 01000] (Message
3211) 79 percent processed. [SQLSTATE 01000] (Message 3211) 84 percent processed. [SQLSTATE 01000] (Message 3211) 89 percent processed. [SQLSTATE 01000] (Message 3211) 94 percent processed. [SQLSTATE 01000] (Message 3211) 99 percent
processed. [SQLSTATE 01000] (Message 3211) Processed 160 pages for database 'model', file 'modeldev' on file 1. [SQLSTATE 01000]... The step succeeded
