How to Create a Maintenance Plan to Backup SQL Databases ka21A000000D2lMQAS | GE Customer Center

How to Create a Maintenance Plan to Backup SQL Databases

Objective
To perform a SQL Database Backup Utilizing the Microsoft SQL Server Manager.
 
Environment
Primary Product OS : Windows 2012 Server
Primary Product Module : Microsoft SQL 2012
 
Procedure
These steps assume that there is not a maintenance plan or any other backup program currently defined for the SQL server.
  1. Launch Microsoft SQL Server Manager.
  2. Connect to the server and instance that the database(s) were installed to.
  3. Expand the folder called “Management”.
  4. Right click the folder called “Maintenance Plans”.
  5. Select Maintenance Plan Wizard.
  6. If you receive the SQL Server Maintenance Plan Wizard splash screen, click “Next”.
  7. On the Select Plan Properties screen, do the following:
    1. Change the Name field to state “Backup”.
    2. Click the “Change” button.
    3. Change the Frequency Settings to “Occurs Daily and Recurs every 1 day”.
    4. Set the Daily frequency to “Occurs once at 12:00:00 AM”.
    5. Set the Duration start date to “today”.
    6. Set the end date to “No end date”.
    7. Click “OK”.
  8. Click “Next”.
  9. On the Select Maintenance Tasks, “select Back Up (Full) and Maintenance Cleanup Task”.
  10. Click “Next”.
  11. On the Select Maintenance Task Order, “select” the order that the tasks should execute.  Note: The Back Up task will be performing the backup of the database.  The Maintenance Cleanup Task will be deleting old backup files.  If you are tight on space, you may need to delete the old backup files first before performing the backup.
  12. On the Define Back UP Database (Full) Task, do the following:
    1. In the Database(s) field, click the “drop-down arrow” to select the specific databases you want to backup.
    2. For the Back up to option, select “Disk”.
    3. For the Create a backup file for every database section, do the following:
      1. Check the box to “Create a sub-directory for each database”, if you want to keep the files separate.
      2. In the Folder field, enter the path of where the backup files will reside.
      3. For the Backup file extension field, enter “bak”.
    4. Check the “Verify backup integrity”, to verify the backup file after it has completed the backup.
    5. Set backup compression to the desired setting.
  13. When all the settings are configured, click “Next”
  14. On the Define Maintenance Cleanup Task, do the following:
    1. In the Delete files of the following type section, select the “Backup files” radio button.
    2. In the File location section, select the “Search folder and delete files based on an extension” radio button.
    3. In the Folder field, enter or browse to the path where backup files are stored.  Note: Select the directory level where you want the sub-directories to be created.
    4. In the File extension field, enter “.bak”.
    5. Check the box to “Include first-level subfolders”.
    6. In the File age section, check the box “Delete files based on the age of the file at task run time”.
    7. In the Delete files, older than the following field, “set” the appropriate retention time you need to keep the backups.  Note: Typically, one day will work, as you should be moving these files daily to an offsite storage per your disaster recovery plan, but you may need to keep the files longer.
    8. Click “Next”.
  15. In the Select Report Options, when it is configured, click “Next”.
  16. In the Complete the Wizard, click “Finish”.
  17. The wizard will create the maintenance plan.  Verify that you received a Success message.
  18. Click “Close”.
  19. In SQL Management Studio, browse to “SQL Server Agent->Jobs”.
  20. Expand the “Jobs” folder.
  21. The Backup.Subplan_1 job should be listed.
Additional Notes
The steps above were  written to address a backup utilizing Microsoft SQL Server 2012.  This functionality and the steps above may also work with other versions of SQL.