July 27, 2009 at 1:16 pm
I have a scheduled task in the SQL Server Agent the run the SQL command SP_Backup which is a stored procedure.
The stored procedure is as follow:
BEGIN
SET NOCOUNT ON
DECLARE @DB_NAME VARCHAR(200)
DECLARE @DB_LOCATION VARCHAR(200)
DECLARE @DB_DATE VARCHAR(200)
DECLARE @DB_FILE VARCHAR(200)
DECLARE DB_CUR CURSOR FOR
SELECT [NAME] FROM dbo.sysdatabases
WHERE (dbid > 4 and ([name] <> 'CLAIMS2') and [name] <> 'CLAIMS' and [name] not like 'ReportServer%')
AND STATUS <> 66056 -- OFFLINE DATABASES
ORDER BY dbid
OPEN DB_CUR
FETCH NEXT FROM DB_CUR INTO @DB_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @DB_DATE = CAST(DATEPART(YYYY,getdate()) AS CHAR(4)) + RIGHT(CAST(100+DATEPART(MM,getdate()) AS CHAR(3)),2) + RIGHT(CAST(100+DATEPART(DD,getdate()) AS CHAR(3)),2) + RIGHT(CAST(100+DATEPART(HH,getdate()) AS CHAR(3)),2) + RIGHT(CAST(100+DATEPART(MI,getdate()) AS CHAR(3)),2)
SET @DB_FILE = @DB_NAME + '_backup_' + @DB_DATE + '.bak'
SET @DB_LOCATION = '\\amsnas\TempDB_Dump\PROD\' + @DB_FILE
BACKUP DATABASE @DB_NAME TO DISK = @DB_LOCATION WITH NOFORMAT, NOINIT, NAME = @DB_FILE, SKIP, REWIND, NOUNLOAD, STATS = 10
FETCH NEXT FROM DB_CUR INTO @DB_NAME
END -- DB_CUR Cursor
CLOSE DB_CUR;
DEALLOCATE DB_CUR;
END
This process runs successfully but seems to backup the databases twice. Except for the first databases in the list. Any ideas why?
July 27, 2009 at 1:45 pm
If you run the SELECT, does it return each db once?
I'd add some logging, or SELECT the backup statement instead of running it to see what happens. It looks fine, and if you are getting two backups (check msdb backup history), I wonder if you have two jobs scheduled for reason. Maybe another process is backing up other databases.
July 27, 2009 at 4:13 pm
Yes the select only returns each database once.
I will create user table in master database and will add an insert into statement to the stored procedure to see when it starts to duplicate. Again, it does all but the first database it backs up. What other logging should I perform?
Also, I have looked over and over again to find another job running and I do not see one.
Steve Jones - Editor (7/27/2009)
If you run the SELECT, does it return each db once?I'd add some logging, or SELECT the backup statement instead of running it to see what happens. It looks fine, and if you are getting two backups (check msdb backup history), I wonder if you have two jobs scheduled for reason. Maybe another process is backing up other databases.
July 27, 2009 at 8:37 pm
Is it possible you have a scheduled task on the server at the o/s level that is backing up databases. It would not show as a scheduled SQL Agent job as it is not set up within SSMS (assuming SQL 2005 or newer) or Enterprise Manager.
One more idea, do you have more than 1 schedule enabled for the backup job?
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
July 28, 2009 at 7:24 am
No I checked Task Scheduler and it is empty. Here is how I have it setup:
The code above is in a stored procedure called. SP_Backup
There is a maintenance plan that first deletes old backup files then executes the T-SQL statement 'EXEC SP_Backup'
The SQL Agent runs the maintenance plan once a week.
I am wondering if it has to do with backing up to a network location?
Timothy Ford (7/27/2009)
Is it possible you have a scheduled task on the server at the o/s level that is backing up databases. It would not show as a scheduled SQL Agent job as it is not set up within SSMS (assuming SQL 2005 or newer) or Enterprise Manager.One more idea, do you have more than 1 schedule enabled for the backup job?
July 28, 2009 at 9:13 am
What happens when you run the SP alone (outside the scheduled maintenance plan). If it runs OK --->
You mention you only have 1 backup for the first DB --- Seems like the SP is firing twice at the same time. Getting the same filename for the first file...
I'd try this.. take the T-SQL task (SP execution) out of the maintenance plan. Then run it -- does it cleanup without backing up??
if so, add a Tsql step after the maintenance plan in the agent job (not in the maintenance plan), and re-run it. Does it run correctly at this point?
This should narrow things down a bit.
July 28, 2009 at 9:29 am
I'd agree with Jamie's advice. If you are getting multiple backups, something else is happening.
Are both backups at the same time? I might run Profiler at the time of the second backup (the unknown one) and see where it is running from.
July 28, 2009 at 9:56 am
No one backup is dated at 3am and another at 9am
Ok I have added a log and also changed the plan per Jamie's suggestion. Not the SQL Agent first runs the maintenance plan and all this does is remove old backup files from location. Then the next SQL Agent step does the 'EXEC SP_BACKUP' I will let it run tonight and see tomorrow morning. Thanks for all the quick responses too by the way.
Steve Jones - Editor (7/28/2009)
I'd agree with Jamie's advice. If you are getting multiple backups, something else is happening.Are both backups at the same time? I might run Profiler at the time of the second backup (the unknown one) and see where it is running from.
August 4, 2009 at 7:59 am
For whatever reason, changing the way the SQL Agent ran the stored procedure worked. Thanks.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply