Creating a scheduled SQL Server backup with SQL Server Maintenance Plans

Created by Cheng Kah Poh, Modified on Wed, 15 Nov, 2023 at 4:30 PM by Ariel Hoong

Click Here to learn How to Connect Database by using Microsoft SQL Server Management Studio. 

To schedule an automated backup with a SQL Server Agent job:

  1. Right click Jobs and select New job from the context menu, in the object explorer panel, in SSMS, under the SQL Server Agent node.

  2. Enter a name for the job in the New job dialog
  3. Click on the New button, under the Steps tab, and create a new backup step by adding a SQL statement

    DECLARE @MyFileName varchar(1000)

    SELECT @MyFileName = (SELECT 'D:\MyDB_' + convert(varchar(500),GetDate(),112) +right('0' + CONVERT(varchar(2), datepart(hour, getdate())),2)+

    right('0' + CONVERT(varchar(2), datepart(minute, getdate())),2)+ '.bak') 

    BACKUP DATABASE [SampleDB2023001C4] TO DISK=@MyFileName


    Note: Please Change [SampleDB2023001C4] to your DB Code, Example [MYDB]

  4. To schedule a job, click New, in the New Job dialog, under the Schedule tab

  5. In the Job Schedule tab, select a recurring frequency and a start date and click OK:

  6. Right click the job and select Start job at step option, to check a created job in the SSMS Object Explorer pane, under the SQL Server Agent then Jobs node



** Learn more about Azure Cloud

** Learn more about QNE Hybrid Cloud Software


Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article