Home > WebEOC > Admin Guide > FAQs > How Do I Set Up an SQL Maintenance Plan?

How Do I Set Up an SQL Maintenance Plan?

Every WebEOC database consists of at least two individual files: a primary data file (.mdf) that stores actual data and a transaction log file (.idf) that records all transactions and modifications within the database. As a best practice, it is recommended to back up the database to preserve both files. Why back up a database? Database backups are key in the event of hard drive failure, catastrophic events, incorrectly modified or destroyed data, or transaction logs that have expanded to unacceptable limits.

Note: Failover replication such as Double-Take is not the same as a database backup. Erroneous data or corruption on a primary database server is replicated automatically/instantly to the secondary database server. Thus, only database backups are safe for restoration from corruption.

Full Database Backup Plan

The easiest way to set up a database backup is using SQL Server Management Studio. If using this method, the user account that is used to create and execute the maintenance plan must have dbowner permissions for the databases affected.

To approach this backup method, you can create a plan to either regularly create a full database backup or regularly back up just the Transaction Log.

To create a full database backup plan

Tip: It is recommended to store your full database backups on a separate system or network drive. If done so, in the event of a database server failure, these database backups are accessible.

  1. From the Microsoft SQL Server Management Studio, expand the Management folder.

  2. Right-click the Maintenance Plans folder and select New Maintenance Plan....

  3. Enter a name for the plan, such as WebEOC Maintenance Plan.

  4. From the bottom left of the page, open the Toolbox and drag the Back Up Database Task item to the bottom of the maintenance plan. 

  5. Double-click the task you added to the plan. The Back Up Database Task window opens.

  6. Verify that, for Connection, Local server connection is selected.

  7. Maintain the selection of Full for Backup type.

  8. For Database(s): select your WebEOC database, such as wedb_7.

  9. Verify that, for Backup component, Database is selected.

  10. For Back up to, select Disk and Create a backup file for every database.

  11. For Folder, to browse the destination directory for where the backups will be stored, click the ellipses button.

  12. Make your selection, and then verify that Backup file extension is set to bak.

  13. Click OK.

  14. From the Toolbox, drag Maintenance Cleanup Task into the maintenance plan.

  15. Double-click the task you added to the plan. The Maintenance Cleanup Task window opens.

  16. For File location, select Search folder and delete files based on an extension.

  17. For Folder, to browse the destination directory for where the backups will be stored, click the ellipses button.

  18. Make your selection, and then verify that File extension is set to bak.

  19. Select Delete files based on the age of the file at task run time.

  20. Select the desired period of time that you'd like to retain these nightly backups.

    Tip: It is recommended that at least two weeks of backups are maintained, if hard drive space is allowed.

  21. Click OK.

  22. In the WebEOC Maintenance PLAN section, click on the calendar icon associated with Subplan_1

  23. In the Job Schedule Properties window, for Occurs, select the desired frequency for the subplan.

    Tip: The recommended setting is for the subplan to run nightly at midnight.

  24. Click OK.

  25. Right-click the maintenance plan's tab and click Save Selected Items.

To create a Transaction Log backup plan
  1. From the Microsoft SQL Server Management Studio, expand the Management folder.

  2. Right-click the Maintenance Plans folder and select New Maintenance Plan....


    Tip: Alternatively, create a second subplan within an existing maintenance plan.

  3. Enter a name for the plan, such as WebEOC Maintenance Plan.

  4. From the bottom left of the page, open the Toolbox and drag the Back Up Database Task item to the bottom of the maintenance plan. 

  5. Double-click the task you added to the plan. The Back Up Database Task window opens.

  6. Verify that, for Connection, Local server connection is selected.

  7. For Backup type, select Transaction Log.

  8. For Database(s): select your WebEOC database, such as wedb_7.

  9. For Back up to, select Disk and Create a backup file for every database.

  10. For Folder, to browse the destination directory for where the backups will be stored, click the ellipses button.

  11. Make your selection, and then verify that Backup file extension is set to trn.

  12. Click OK.

  13. From the Toolbox, drag Maintenance Cleanup Task into the maintenance plan.

  14. Double-click the task you added to the plan. The Maintenance Cleanup Task window opens.

  15. For File location, select Search folder and delete files based on an extension.

  16. For Folder, to browse the destination directory for where the backups will be stored, click the ellipses button.

  17. Make your selection, and then verify that File extension is set to trn.

  18. Select Delete files based on the age of the file at task run time.

  19. Select the desired period of time that you'd like to retain these nightly backups.

    Tip: It is recommended that 24 hours of backups are maintained.

  20. Click OK.

  21. In the WebEOC Maintenance PLAN section, click on the calendar icon associated with Subplan_1

  22. In the Job Schedule Properties window, for Occurs, select the desired frequency for the subplan.

    Tip: The recommended setting is daily.

  23. Click OK.

  24. Right-click the maintenance plan's tab and click Save Selected Items.

You must to post a comment.
Last modified

Tags

Classifications

This page has no classifications.