SQL Server provided SQL Server Agent, which can backup the database on a specific schedule

Step-by-step guide

Part A: Setup backup job

  1. Ensure your SQL Server Agent job is started and running, in Windows "Service", find "SQL Server Agent"

    Enable it by doubling it and selecting "Startup type" as "Automatic"
    ** Make sure the "Log On As" user has access right to your backup disk drive
  2. Using "SQL Server Management Studio", connect to your DB server, enable you to find "SQL Server Agent" under object explorer
  3. Expand "Management" > "Maintenance Plans", double check if you have any backup plan already defined.
    If not, right-click "Maintenance Plans" and select "Maintenance Plan Wizard"


  4. Press "Next" on the first screen, then provide the name of the job, then press "Change" under "Schedule" 
  5. Define the scheduling frequency, start time, and first start date, and press OK
  6. Select the "Back Up Database (Full)" and "Maintenance Cleanup Task", and other tasks which is suitable, and press Next
  7. Press Next again if you don't need to reorder the executing sequence
  8. In "Define Back Up Database (Full) Task",
    In the General tab: Select the database(s) you want to backup (All/ User / or selection)

    In the Destination tab: select the Folder the backup file should locate, and select "Create a sub-directory for each database" if you want each database to have its own folder (more organised if you have different backup dates)

    In the Option Tab: select "Backup compression", and select "Compress backup" if you want to save backup space. 

    Press Next once completed
  9. In "Define Maintenance Cleanup Task", select the folder of regular delete those backup and file extensions (normally *.bak)
    If you have select "Create a sub-direct for each database", then you must select "Include first-level subfolders"
    Then, select "Delete files based on the age of file at task run time" and pick the age in the boxes just below it 
    Press Next
  10. Uncheck "Write a report to a text file" if that's not necessary, otherwise please make sure you clean the report folder with other tasks as well, press Next then Finish
  11. It will then generate the task in "Maintenance Plans" and "SQL Server Agent"


Part B: Manually trigger the job and view history
You can either right-click the job in Maintenance Plan, press "Execute" or "View History"

Or right-click the Server Agent job and press "Start Job at Step..."

To start the backup job immediately, and view the executing history and result


Part C: Setup Email alert

1) Setup Database email profile


In Office365, create an email account for this purpose first
Then, right-click and select "Configure Database Mail"

Press Next for "Set up Database Mail"

Provide a profile name and description, and then press "Add"


In "New database mail account", fill in the following info
Account name / Email address / Basic Authentication User name: Email address of the account
Server name: smtp.office.com, Port: 587
Tick "The server require a secure connection (SSL)"

For the password, go to portal.office.com, log in with the email account just created, go to top right corner and press the account logo, then select "View Account"

Select "Security Info", then "Add sign-in method"

Select "App password", then provide a name like "database mail"

Copied the generated password out, then put it back into the "Password" and "Confirm Password" boxes in the "New database mail account box", press Next

Add the email profile as Public (or private whatever is more suitable), press Next


Press Next to confirm the parameters, then Finish

Try the database mail if it is working or not by right click "Send Test E-mail"