April 12, 2020 at 2:49 pm
Hello all,
I have a stored procedure that backs up all my databases.
When I run the stored procedure manually it finishes without any errors and all backups are in the backup location.
When I run the stored procedure as a job it finishes without any errors (reporting a success) but NOT all backups are in the backup location (I guess it skipped them).
Any ideas on the issue?
Thanks in advance,
David.
April 12, 2020 at 2:53 pm
Maybe the SQL Agent user does not have rights to create files in the backup folder ...
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 12, 2020 at 3:15 pm
Phil's suggestion of a permission issue is pretty spot-on.
Consider reviewing the stored procedure for TRY/CATCH statements. I am wondering if the code is preventing an error from being raised.
Another thing to look at it is if backups are made in a particular order. If so, try to determine the last successful backup and which database was slated to be backed up next. In this case, the database slated to be backed up next might be the source of your problem.
Hope this helps!
April 12, 2020 at 3:38 pm
Is the SQL Agent Job running from a sysadmin account - or a user account? If a user account then that account probably does not have access to the databases that are missing.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 13, 2020 at 3:43 pm
Hello all,
Thanks for all your help but the plot get thickened …. (just like in computer science 101) I have undone the last change, I have moved back the schedule of the job back to were it was and wallah the issue is solved.
All my databases are backed up and "no database is left behind".
Mt new question is:
Since I would like reschedule a task to a new time, now it is at 7:00 am and I would like to take it to 3:00 am, away from production hours, how can I check what can cause such behavior? The job ends with a success message but not all databases were backed up .
Since I know now that in a different hour the job works then I guess the job is OK and I need to look for the cause in the system department.
Any ideas will be welcome.
Thanks in advance,
David.
April 13, 2020 at 4:18 pm
You'd have to provide more info about how the job is running and scheduled. SQL Server backups should not interfere with workload, nor should workload interfere with them. Not sure what is going on here, but you haven't provided enough technical details to know what's wrong.
April 13, 2020 at 4:34 pm
Hello Steve,
I guess I have create a trees and forest situation 🙂
I have a backup job, when i schedule it to run at 03:00 it finish with a success message but when i examine the msdb database i find that not all databases were backed up.
If I re-schedule the same job to 07:00, it finish with a success message and when checking in the msdb database I find that all is OK and all my databases are backed up.
my guess is that the issue is not with the job itself, since it works OK at 07:00.
my question is: what can cause such a behavior that the job will finish with a success message when it actually did not.
(will it help if I will add my SP?)
Thanks again,
David.
April 13, 2020 at 5:45 pm
success is for the job. The parts of the job might have issues, but depending on your proc, you might report success. Seeing the proc will help, but honestly, there are well written and tested backup procs you shoudl use.
April 13, 2020 at 8:04 pm
We need to see the code - and how that code is selecting the databases to be backed up. I would suspect there is some criteria that is excluding those databases that are not getting backed up.
Is it possible those databases have been taken offline for some reason? Or - set to read only?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 13, 2020 at 9:19 pm
This is my SP.
It get triggered once an hour.
Thanks.
USE [IDEMaintenance]
GO
/****** Object: StoredProcedure [dbo].[SP_Backup_Databases] Script Date: 14/04/2020 00:16:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_Backup_Databases]
AS
BEGIN
SET NOCOUNT ON;
--GetSettings
DECLARE @BackupPath NVARCHAR(250)
DECLARE @AdminMail NVARCHAR(250)
SET @BackupPath = (Select SettingsValue From IDEMaintenance.dbo.Settings WHERE SettingsKey = 'BackupPath')
SET @AdminMail = (Select SettingsValue From IDEMaintenance.dbo.Settings WHERE SettingsKey = 'AdminMail')
--Application Variables
DECLARE @DatabaseName NVARCHAR(MAX)
DECLARE @recovery_model_desc NVARCHAR(MAX)
DECLARE @Path_Statment NVARCHAR(MAX)
DECLARE @Create_Statment NVARCHAR(MAX)
DECLARE @MailSubject NVARCHAR(MAX)
DECLARE @BackupType NVARCHAR(MAX)
IF DATEPART(HOUR, GETDATE()) = 7
SET @BackupType = 'FULL'
ELSE
SET @BackupType = 'TRN'
--FIRST INSTALL OPTION START
--SET @BackupType = 'FULL'
--FIRST INSTALL OPTION END
DECLARE MYCursor CURSOR FOR
SELECT DatabaseName, recovery_model_desc, Full_Backup_Path ,CREATE_BACKUP
FROM
(
SELECTname AS DatabaseName
,database_id
,create_date
,compatibility_level
,collation_name
,state_desc
,recovery_model_desc
,@BackupPath + '\' + name AS Full_Backup_Path
,CASE
WHEN recovery_model_desc = 'SIMPLE' AND @BackupType = 'FULL' THEN
'BACKUP DATABASE ['
+ name
+ '] TO DISK = '''
+ @BackupPath
+ '\'
+ name
+ '\'
+ name
+ ' '
+ REPLACE(CONVERT(NVARCHAR(MAX), GETDATE(), 120), ':', '')
+ '.BAK'' ; '
WHEN recovery_model_desc = 'FULL' AND @BackupType = 'FULL' THEN
'BACKUP DATABASE ['
+ name
+ '] TO DISK = '''
+ @BackupPath
+ '\'
+ name
+ '\'
+ name
+ ' '
+ REPLACE(CONVERT(NVARCHAR(MAX), GETDATE(), 120), ':', '')
+ '.BAK'' ; '
/*
+ 'BACKUP LOG ['
+ name
+ '] TO DISK = '''
+ @BackupPath
+ '\'
+ name
+ '\'
+ name
+ ' '
+ REPLACE(CONVERT(NVARCHAR(MAX), GETDATE(), 120), ':', '')
+ '.TRN''; '
*/
WHEN recovery_model_desc = 'FULL' AND @BackupType = 'TRN' THEN
/*
'BACKUP DATABASE ['
+ name
+ '] TO DISK = '''
+ @BackupPath
+ '\'
+ name
+ '\'
+ 'FirstBackup'
+ '.BAK'' ; '
+
*/
'BACKUP LOG ['
+ name
+ '] TO DISK = '''
+ @BackupPath
+ '\'
+ name
+ '\'
+ name
+ ' '
+ REPLACE(CONVERT(NVARCHAR(MAX), GETDATE(), 120), ':', '')
+ '.TRN''; '
END AS CREATE_BACKUP
FROMSYS.databases
WHEREsys.databases.name <> 'tempdb'
AND sys.databases.name <> 'model'
--AND sys.databases.name <> 'master'
--AND sys.databases.name <> 'msdn'
--WHEREsys.databases.database_id > 4
) AS TABLE1
OPEN MYCursor
FETCH NEXT FROM MYCursor
INTO @DatabaseName, @recovery_model_desc, @Path_Statment, @Create_Statment
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @DatabaseName
EXEC IDEMaintenance.dbo.CheckAndCreateDirectory @DatabaseName
PRINT @Create_Statment
EXEC (@Create_Statment)
/*
SET @MailSubject = 'Created First Backup for Database - ' + @DatabaseName
EXECUTE IDEMaintenance.dbo.SEND_MAIL_ONE_LINE 'dudik@ide-tech.com'
,@MailSubject
,@MailSubject
--,'Created First Backup for Database - '
--,'Created First Backup for Database - '
--(@recipients_input = 'dudik@ide-tech.com'
--,'Created First Backup for Database - ' & @name
--,'Created First Backup for Database - ' & @name)
*/
FETCH NEXT FROM MYCursor
INTO @DatabaseName, @recovery_model_desc, @Path_Statment, @Create_Statment
END
CLOSE MYCursor;
DEALLOCATE MYCursor;
END
April 13, 2020 at 9:48 pm
Okay - you definitely have a problem with this script. In fact, I can see several issues...
This code:
IF DATEPART(HOUR, GETDATE()) = 7
SET @BackupType = 'FULL'
ELSE
SET @BackupType = 'TRN'
Defines what type of backup is to be performed - and if you change the start time of the agent job then your 'full' backups will not be executed at the new job start time. This is also confusing because you stated you wanted to change the start time of the agent job - but it doesn't work if you change it to 3am. Now - you are saying this job executes every hour...which is it?
The cursor builds the command to be executed...but it does not check for all recovery models. It is missing BULK_LOGGED recovery model...which could be the state of the database if you have some other process running at that time performing index maintenance and someone decided to change the recovery model to bulk_logged to reduce the impact on the log file during that operation.
Based on what I see here - you would be much better off using Ola's utility...https://ola.hallengren.com/sql-server-backup.html. If that utility doesn't work for you and you feel you need to roll your own, then you should rethink this process and code.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply