Issue with a stored procedure when I run it as a job

  • 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.

  • 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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • 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!

    • This reply was modified 4 years, 8 months ago by  scott.fountain. Reason: The comment I made lended to an assumption. It might be best to keep it a little more open
  • 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

  • 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.

  • 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.

  • 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.

  • 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.

  • 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

  • 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
  • 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