December 3, 2009 at 6:18 am
I've got an issue that's got me pulling my hair out. I have a script that gets run every day at 6AM to do a full backup of all my databases (46 of them). Every day at 6AM this script runs and it happily backs up 26 databases. Then it quits - reporting success. So I come in between 7AM and 8AM, discover that the script stopped short of backing up everything, and run kick off the job again. And it backs up all 46 databases.
I've tried everything I can think of:
1) It's not a permissions issue. When I run the job after getting to work I actually add a one-time scheduled run to the job.
2) There's not much going on at 6AM scheduled job-wise. I've made sure that we keep that time frame relatively inactive.
3) It's not a network issue. I'm backing up to local drives and moving the files later.
4) I've added output to the script in an attempt to see what's going on.
a) The output shows me the 26 databases getting backed up and runs a query on the backup tables in msdb showing me that only 26 of them ran.
b) When I run it 1-2 hours later the output shows me all 46 of them.
So here's the stored procedure I'm using:
USE [master]
GO
/****** Object: StoredProcedure [dbo].[usp_FullBackup] Script Date: 12/03/2009 08:06:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[usp_FullBackup]
as
declare @backup_start as datetime
declare @dbname as varchar(50)
declare @path as varchar(255)
declare @backupdate as varchar(255)
declare @filename as varchar(255)
set @backup_start = DATEADD(MINUTE, -1, GETDATE())
print '*********************************************************************************************************'
print 'TLOG BACKUP'
print '*********************************************************************************************************'
exec master.dbo.usp_TLogBackup
@override = 1
print '*********************************************************************************************************'
print 'FULL BACKUP'
print '*********************************************************************************************************'
set @path = 'c:\BackupsForTape\ ' -- I added a space after the "\" because the code tag was seeing it as an escape character. the actual sp doesn't have the space
/* set Julian date to add to the backup filename */
set @backupdate = '_' + right(cast(datepart(yy, getdate()) as varchar(200)), 2) + cast(datepart(dayofyear, getdate()) as varchar(200))
DECLARE db_cursor CURSOR for
SELECT name
from master.dbo.sysdatabases
WHERE name not in ('tempdb')
OPEN db_cursor
FETCH NEXT from db_cursor INTO
@dbname
WHILE @@FETCH_STATUS = 0
BEGIN
set @filename = @path + @dbname + @backupdate + '.bak'
print '**********************************************************************************************************************'
print 'backup'
print @dbname
print @backupdate
print @filename
backup database @dbname
to disk = @filename
with init, compression
FETCH NEXT from db_cursor INTO
@dbname
END
close db_cursor
deallocate db_cursor
SELECT a.backup_set_id,
a.backup_start_date,
a.backup_finish_date,
a.backup_size,
a.compressed_backup_size,
cast((a.backup_size-a.compressed_backup_size) as numeric(23,10))/cast((a.backup_size) as numeric(23,10)) as compression_ratio,
a.compatibility_level,
a.type,
b.logical_name
FROM msdb.dbo.backupset a
INNER JOIN msdb.dbo.backupfile b on a.backup_set_id = b.backup_set_id
where a.backup_start_date >= @backup_start
and a.type = 'd'
Does anyone have any other ideas on what might be going wrong or what I can use to try to determine the problem? Can anyone prevent me going bald?
This thing is cursor-driven. So I'm starting to think the query that populates the cursor is not getting all the db names. What I can't explain is why it would perform differently at different times of the day.
December 3, 2009 at 6:54 am
Nothing to do with the problem at hand, however, I would change master.dbo.sysdatabases to sys.databases for the following reason from SQL Server 2008 BOL:
Important: This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
Why it is failing when run at 6:00 AM and then work properly an hour or so later, not sure.
December 10, 2009 at 1:03 pm
reaching for straws here, but something could be happening to mess up your rowset... maybe one of the tables it first finds no longer exists by the time you get down to it.
I would perhaps change the way you get your snapshot list of database names, don't use "not in ('tempdb')" try to be more specific in the where clause to eliminate tables that may be transient.
Of course you can try using @@fetch_status this way:
while @@fetch_status <> -1
which may also mitigate something happening while your cursor is active.
The probability of survival is inversely proportional to the angle of arrival.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply