|
|
Posted by m19peters on 04/04/07 15:53
We have a script that I had to rework a little bit for 2005 that does
a full backup for every database on the server... For some reason on
some nights the script does not backup all databases... Its like it
skips over it for some reason... Output of the script below on the
night in question was:
Executed as user: NT AUTHORITY\SYSTEM. master [SQLSTATE 01000]
(Message 0) Status is ONLINE dbname / dbdevice = master / SQLBUmaster
[SQLSTATE 01000] (Message 0) Processed 376 pages for database
'master', file 'master' on file 1. [SQLSTATE 01000] (Message 4035)
Processed 2 pages for database 'master', file 'mastlog' on file 1.
[SQLSTATE 01000] (Message 4035) BACKUP DATABASE successfully
processed 378 pages in 0.169 seconds (18.298 MB/sec). [SQLSTATE 01000]
(Message 3014). The step succeeded.
A normal night on this particular server includes two other databases
like below:
(Message 0) Processed 376 pages for database 'master', file 'master'
on file 1. [SQLSTATE 01000] (Message 4035) Processed 2 pages for
database 'master', file 'mastlog' on file 1. [SQLSTATE 01000] (Message
4035) BACKUP DATABASE successfully processed 378 pages in 0.711
seconds (4.349 MB/sec). [SQLSTATE 01000] (Message 3014) msdb
[SQLSTATE 01000] (Message 0) Status is ONLINE dbname / dbdevice =
msdb / SQLBUmsdb [SQLSTATE 01000] (Message 0) Processed 688 pages for
database 'msdb', file 'MSDBData' on file 1. [SQLSTATE 01000] (Message
4035) Processed 5 pages for database 'msdb', file 'MSDBLog' on file
1. [SQLSTATE 01000] (Message 4035) BACKUP DATABASE successfully
processed 693 pages in 3.743 seconds (1.516 MB/sec). [SQLSTATE 01000]
(Message 3014) SBC [SQLSTATE 01000] (Message 0) Status is ONLINE
dbname / dbdevice = SBC / SQLBUSBC [SQLSTATE 01000] (Message 0)
Processed 11577184 pages for... The step succeeded.
The script is schedule to be run nightly and it looks like this:
ALTER PROCEDURE [dbo].[usp_backupFull] AS
set nocount on
Declare @start_time datetime,
@end_time datetime,
@backupsize real,
@status varchar(100),
@cmd nvarchar(255),
@monitor_server varchar(50),
@recovery varchar(100),
@db_name varchar(100),
@dev varchar(100),
@log varchar(100),
@backup_folder varchar(100),
@dev_path varchar(255),
@log_path varchar(255),
@message_text varchar(255),
@subject_text varchar(255),
@error varchar(50)
Select @backup_folder ='D:\SQLBU\'
--Select @monitor_server ='MONITOR'
CREATE TABLE #error (dbname varchar(50), error varchar(50))
DECLARE db_cursor CURSOR FOR SELECT name FROM master..sysdatabases
where name not in ('Northwind','pubs','tempdb','model')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @db_name
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT @dev = 'SQLBU' + @db_name
SELECT @dev_path = @backup_folder + @dev + '.bak'
SELECT @log = 'SQLBU' + @db_name + 'LOG'
SELECT @log_path = @backup_folder + @dev + '_log.bak'
PRINT ''
PRINT @db_name
PRINT ''
IF NOT EXISTS (SELECT name FROM master..sysdevices where status=16
and name=@dev) BEGIN
-- Create new backup device if it doesn't exist
EXEC sp_addumpdevice
@devtype='Disk',@logicalname=@dev,@physicalname=@dev_path
PRINT ''
END
Select @recovery =
CONVERT(varchar(100),DATABASEPROPERTYEX(@db_name,'Recovery'))
IF @recovery <> 'SIMPLE' BEGIN
IF NOT EXISTS (SELECT name FROM master..sysdevices where status=16
and name=@log) BEGIN
-- Create log backup device if it doesn't exist and logging not set
to SIMPLE
EXEC sp_addumpdevice
@devtype='Disk',@logicalname=@log,@physicalname=@log_path
END
END
SELECT @status = CONVERT(VARCHAR(100),DATABASEPROPERTYEX(@db_name,
'Status'))
print 'Status is ' + @status + ' dbname / dbdevice = ' + @db_name +
' / ' + @dev
IF @status = 'ONLINE' BEGIN
SELECT @cmd = 'BACKUP DATABASE ' + @db_name + ' TO ' + @dev + ' WITH
INIT'
EXEC(@cmd)
IF @@ERROR <> 0 BEGIN
INSERT INTO #error VALUES (@db_name,'Full backup Failed-Check Log')
--Select @cmd = 'osql -U srvMonitor -P backups -S ' +
@monitor_server + ' -d Monitor -Q "insert into backups
([date],server_name,db_name,backup_type,status) values (GETDATE(),'''+
@@servername +''',''' + @db_name + ''',''Full'',''Failed'')"'
--Execute master..xp_cmdshell @cmd
END
ELSE BEGIN
SELECT @start_time = backup_start_date, @end_time =
backup_finish_date, @backupsize = (backup_size / 1024 / 1024) FROM
msdb..backupset WHERE (type = 'd') AND (database_name = @db_name) AND
(backup_finish_date > DATEADD(mi, -1, GETDATE()))
--Select @cmd = 'osql -U srvMonitor -P backups -S ' +
@monitor_server + ' -d Monitor -Q "insert into backups values
(GETDATE(),'''+ @@servername +''',''' + @db_name +
''',''Full'',''Success'',''' + cast(@start_time as varchar(50)) +
''',''' + cast(@end_time as varchar(50)) + ''',' + cast(@backupsize as
varchar(50)) + ')"'
--Execute master..xp_cmdshell @cmd
END
PRINT ''
SELECT @recovery =
CONVERT(VARCHAR(100),DATABASEPROPERTY(@db_name,'IsTruncLog'))
IF @recovery <> '1' BEGIN
SELECT @cmd='BACKUP LOG '+@db_name+' TO ' + @log + ' WITH INIT'
EXEC(@cmd)
IF @@ERROR<>0 BEGIN
INSERT INTO #error VALUES (@db_name,'Log backup Failed-Check Log')
--Select @cmd = 'osql -U srvMonitor -P backups -S ' +
@monitor_server + ' -d Monitor -Q "insert into
backups([date],server_name,db_name,backup_type,status) values
(GETDATE(),'''+ @@servername +''',''' + @db_name +
''',''Log'',''Failed'')"'
--Execute master..xp_cmdshell @cmd
END
ELSE BEGIN
SELECT @start_time = backup_start_date,@end_time=
backup_finish_date, @backupsize = (backup_size / 1024 / 1024) FROM
msdb..backupset WHERE (type = 'L') AND (database_name = @db_name) AND
(backup_finish_date > DATEADD(mi, -1, GETDATE()))
--SELECT @cmd = 'osql -U srvMonitor -P backups -S ' +
@monitor_server + ' -d Monitor -Q "insert into backups values
(GETDATE(),'''+ @@servername +''',''' + @db_name +
''',''Log'',''Success'',''' + cast(@start_time as varchar(50)) +
''',''' + cast(@end_time as varchar(50)) + ''',' + cast(@backupsize as
varchar(50)) + ')"'
--Execute master..xp_cmdshell @cmd
END
END
PRINT ''
END
ELSE BEGIN
PRINT 'The database was not backed up due to options that were set
under sp_dboptions'
PRINT ''
INSERT INTO #error VALUES (@db_name,'DB Not backed up due to DB
options')
--Select @cmd = 'osql -U srvMonitor -P backups -S ' +
@monitor_server + ' -d Monitor -Q "insert into backups
([date],server_name,db_name,backup_type,status) values (GETDATE(),'''+
@@servername +''',''' + @db_name + ''',''Full'',''Not Backed up -
Check DB Options'')"'
--Execute master..xp_cmdshell @cmd
END
FETCH NEXT FROM db_cursor into @db_name
END --WHILE
-- Open error cursor --
DECLARE db_error CURSOR FOR SELECT dbname,error from #error
OPEN db_error
FETCH NEXT FROM db_error into @db_name,@error
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT @message_text = @error + ' for ' + @db_name
SELECT @subject_text = '!!!!!! ' + @@servername + ' - Backup
failed for ' + @db_name + ' !!!!!!'
--exec msdb..usp_Alerts @mess = @message_text ,
@subj=@subject_text
FETCH NEXT FROM db_error into @db_name,@error
END --WHILE
DROP TABLE #error
PRINT ''
DEALLOCATE db_cursor
DEALLOCATE db_error
set nocount off
---------------------------------------------------------------
Any help I would appreciate it... As you can see from the output above
it looks like its not even getting the database name to backup in the
cursor. But that just doesn't make any sense to me... why could that
be.
[Back to original message]
|