SQL Server 2005 full backup script

[m19p...@gmail.com]

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.

[Erland Sommarskog]

(m19p...@gmail.com) writes:
> 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:

I don't see any apparent flaws, but two thoughts:

1) Make the cursor over sys.databases INSENSITIVE. The default cursor
type is dynamic, and it makes me nervous.

2) You should probably have some TRY-CATCH around the backup commands.
If backup fails with an error that aborts the batch you will not
backup the rest of the databases.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[m19p...@gmail.com]

On Apr 4, 6:21 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> (m19pet...@gmail.com) writes:
> > We have ascriptthat I had to rework a little bit for 2005 that does
> > afullbackupfor every database on theserver... For some reason on
> > some nights thescriptdoes notbackupall databases... Its like it
> > skips over it for some reason... Output of thescriptbelow on the

> > night in question was:
>
> I don't see any apparent flaws, but two thoughts:
>
> 1) Make the cursor over sys.databases INSENSITIVE. The default cursor
> type is dynamic, and it makes me nervous.
>
> 2) You should probably have some TRY-CATCH around thebackupcommands.

> Ifbackupfails with an error that aborts the batch you will not
> backupthe rest of the databases.
>
> --
> Erland Sommarskog,SQLServerMVP, esq...@sommarskog.se
>
> Books Online forSQLServer2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online forSQLServer2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Both very good ideas... thats probably why I didn't think of them...
I'll give it a try and let you know.