Edit

Share via

How to schedule a database backup operation by using SQL Server Management Studio

  • Article

This article describes how to schedule a database backup operation by using SQL Server Management Studio (SSMS) for SQL Editions that support SQL Server Agent. To schedule backups for SQL Express editions, see Schedule and automate backups of SQL Server databases in SQL Server Express.

Use maintenance plans to back up databases

You can use Maintenance Plans to back up databases and transaction log files, perform differential backups, and define retention periods for your backups. For more information, see:

Use the Script Action to Job option to back up databases

To schedule a database backup using the Script Action to Job option in SSMS, follow these steps:

  1. Start SQL Server Management Studio and select Connect > Database Engine.

  2. In the Connect to Server dialog box, select the appropriate values in the Server type list, in the Server name list, and in the Authentication list.

  3. Select Connect.

  4. In Object Explorer, expand Databases.

  5. Right-click the database you want to back up, select Tasks, and then select Back Up.

  6. In the Back Up Database - <DatabaseName> dialog box, make sure that Backup type is set to Full and Backup component is set to Database.

  7. Under Destination, select Disk for the Back up to option and then select Add. For other options on this page, see Backup Database.

  8. In the Select Backup Destination dialog box, enter a path and a file name in the Destinations on disk box, and then select OK.

  9. In the Script list, select Script Action to Job.

    Note

    If you don't see the Script Action to Job option, you might be running an express edition of SQL Server. You can check your edition by running the query select @@version. For more information, see Determine which version and edition of SQL Server Database Engine is running.

  10. In the New Job dialog box, select Steps under Select a page, and then select Edit if you want to change the job parameters.

    Note

    • In the Job Step Properties - 1 dialog box, you can see the backup command.

    • Microsoft is currently investigating an error message that can occur in SSMS at this step. If you get an exception at this step, repeat the procedure without this step and make any changes by editing the corresponding job under the Jobs menu under SQL Server Agent in Object Explorer.

  11. Under Select a page, select Schedules, and then select New.

  12. In the New Job Schedule dialog box, enter the job name in the Name box, specify the job schedule, and then select OK.

    Note

    If you want to configure alerts or notifications, you can select Alerts or Notifications under Select a page.

  13. Select OK two times.

    You receive the following message:

    The backup of database '<DatabaseName>' completed successfully.

Note

  • To verify the backup job, expand SQL Server Agent, and then expand Jobs. When you do this step, the SQL Server Agent service must be running.

  • You can use a similar procedure to schedule transaction log and differential backups if you make the appropriate selection in Step 6 for Backup type. If you don't see the Transaction Log option in Backup type, check the recovery model of the database. You can't take transaction log backups if you are using the Simple Recovery model for your database.

Manually create SQL Server Agent jobs

Alternatively, you can create and schedule your own backup jobs using SQL Server Agent. For more information, see Create Jobs and Schedule a Job.

Related content