September 23, 2010 at 9:54 am
I use the following script to do a full backup of all db's on one of our SQL 2005 servers. Every week or so it will only backup the first two db's and exit, but not report failure. When it succeeds, the backup order is master, model, msdb, userdb1, etc.... When it "fails" without error, it only backups master and model. It's like it hits msdb and quits. I can't figure out what's going on here. Any ideas?
DECLARE @name VARCHAR(128) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
DECLARE @backupDesc VARCHAR(256) -- descriptive name for backup
DECLARE @backupSetId as int--used in restore verify process
SET @path = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT [name]
FROM master.sys.databases
WHERE [name] NOT IN ('tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
-- BACKUP DATABASE
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
SET @backupDesc = @name + '-Full Database Backup'
BACKUP DATABASE @name TO DISK = @fileName
WITH NOFORMAT, NOINIT, NAME = @backupDesc, SKIP, NOREWIND, NOUNLOAD, STATS = 10
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
September 23, 2010 at 10:14 am
Are you sure you have space on the drive for all backups?
Are there any messages in the job history?
You aren't doing any type of logging. Can you try wrapping it in a Try Catch to get the error?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 23, 2010 at 10:47 am
Thanks - I'll set up some logging to see if I can catch something.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply