Recommended SQL Server Maintenance Plans Follow

Transaction Log Backup Maintenance Plan

Database transaction log backup provides a means to restore a database to a specific point in time without requiring constantly running full or partial backups, which can cause undue load and locks on the database. The transaction log back provides more granular restore points between two full or partial backups.

Setting up a transaction log backup maintenance plans only requires setting up two tasks, as described below. The maintenance plan should be set to run on a recurring schedule every 15 minutes, as setting it to run any more frequently may also have an adverse effect on the server's and database's performance.

Back Up Database Task

When adding a "Back Up Database Task" to a maintenance plan, set the Backup Type to "Transaction Log" and select the GlasPacLX database from the "Databases" drop-down.

Next, select the "Back up to:" target to Disk and select the "Create a backup file for every database" and enter in the folder where the transaction log backup files will need to be stored. The path needs to either point to a physical path on the server or a UNC path to a file server, as mapped drives cannot be used. If the path is a UNC path, the share and directory security needs to be set so that the user account that the SQL Server Agent service is running as has access. It is recommended that the SQL Server Agent service user account be a domain account and must have the appropriate rights and privileges as documented in the "SQL Server 2008 R2 Security Best Practices" guide.

The backup file extension should be set to "trn".

Maintenance Cleanup Task

Since the database transaction log backup files only need to be kept between two successful full (or partial) backups, a cleanup task is highly recommended. When creating the "Maintenance Cleanup Task" in a maintenance plan, set the task to delete "Backup files" from the same folder path and extension used in the "Back Up Database Task". It is also recommended to check "Include first-level subfolders".

Next, check "Delete files based on the age of the file at task run time" and set the "Delete files older than the following" to a value that you would like to keep the files around.

Daily and Weekly Backup Maintenance Plans

In addition to setting up a transaction log backup maintenance plan, daily and weekly full backup maintenance plans also need to be created. Both maintenance plans need to include the following tasks and executed in the order provided below. For all of the tasks below, make sure to include all system and user databases and use the default settings, unless otherwise noted.

The maintenance plan should be scheduled during pre-defined maintenance windows and/or outside of peak or business hours if at all possible. All of the tasks included in the maintenance plan will greatly affect the performance of the server.

Step 1: Check Database Integrity Task

This task should be run against all system and user databases.

Step 2: Rebuild Index Task

This task should be run against the GlasPacLX database.

Step 3: Update Statistics Task

This task should be run against all system and user databases.

Step 4: Back Up Database Task (Full Backup)

The same caveats regarding database backup path listed for the "Back Up Database Task" item for transaction log backups also apply here. For this task, set the backup type to "Full" rather than "Transaction Log" and the backup file extension should be set to "bak". The location of the full database backup files should reside in a different directory as the transaction log backup files to prevent accidental deletion by an administrator or the maintenance cleanup tasks.

Step 5: Shrink Database Task

This task should only target the GlasPacLX database and should be used to help keep the transaction log file trimmed down. The size that the database should be shrunken down to by this task depends on the size of the database and the number of database transactions per day. Use the database properties to determine how much space both the main database file and the transaction log file take up and have available. Use those numbers to create a baseline of how much the database should be shrunken.

Step 6: Maintenance Cleanup Task

Use the same settings that is used create the Maintenance Cleanup Task for the transaction log backup maintenance plan, but change the extension to "bak" and set the "Delete files based on the age of the file at task run time" to a longer period (2-4 weeks recommended).

Step 7: History Cleanup Task

This task should be set to clean up and remove "Backup and restore history", "SQL Server Agent job history" and "Maintenance plan history" after a period of time. Depending on data retention requirements, this value can be set to the same value set in the Maintenance Cleanup Task above, or a longer period of 180 days or longer.

Have more questions? Submit a request

Comments

Powered by Zendesk