1

How to backup only specific days each month on SQL Server (I use both SQL Server 2005 and 2008) using a maintenance plan or Agent job. I mean by that to backup my databases from 28th of each month to 08th of the next month and so on?

4
  • 2
    I do have to wonder why you don't care about backups in the middle of the month? Mar 27 '15 at 2:19
  • The backup in the middle of the month doesn't fit our business needs, so it's not a possible solution for me!
    – O.Badr
    Mar 27 '15 at 2:25
  • 1
    Your business needs are to put your data at risk starting on the 9th of the month, and gradually increase that risk until everyone can breathe easier on the 28th? Mar 27 '15 at 2:30
  • 1
    No, We backup at this specific dates just sipmly because the users don't change the data outside of these days ;)
    – O.Badr
    Mar 27 '15 at 2:35
5

This will be tough to do with the built-in schedule UI, unless you want to create 12 different schedules, but you can easily build this into your job step. Assuming it runs every night:

IF DAY(GETDATE()) <= 8 OR DAY(GETDATE()) >= 28
BEGIN
  -- do backups
END

On other days (9th -> 27th), the job will still run, but everything inside the IF conditional will not. So other than having an entry in sysjobhistory, it will be as if it didn't run at all.

Another idea would be to have a different job that runs every morning, checks the date in a similar way, then enables / disables the backup job accordingly. Or similarly with two schedules (one on the 9th to disable, and one on the 28th to enable).

1
  • What I've done, is running the SQL Server agent job every day and check if we're between 28th and 8th, if yes, then I execute the backup steps if not I stop the job using dbo.sp_stop_job, I know we can have many solutions for a problem, but this solution works for me, anyway you've given me a track for the solution, thank you @aaron-bertrand!
    – O.Badr
    Mar 31 '15 at 13:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.