top of page

Automate SQL database backups, Indexing and DBCC Check DB Using SQL Maintenance Plans

A SQL Server Maintenance Plan is a set of automated tasks that are designed to help manage and maintain a SQL Server database. The Maintenance Plan provides a graphical interface to easily create a variety of tasks such as backups, index, perform database maintenance, perform database core maintenance tasks, integrity checks full database backup transaction log backups, and statistics updates.

A Maintenance Plan can be created using the SQL Server Management Studio (SSMS) and includes a set of predefined templates for common maintenance tasks such as database backups, integrity checks, and index optimization. You can also customize the you create a sql server maintenance plan designer from plans to plan by designer to include additional tasks, or modify the existing tasks to create maintenance plans to better fit your specific needs.

The Maintenance Plan Wizard walks you through the process of creating the new maintenance plan, allowing you to name simple tasks, specify which databases to include, the schedule and frequency of each task, and any additional options or parameters. Once the maintenance plan wizard is created, it can be saved and scheduled to run automatically, reducing the need for manual intervention with system databases and improving the overall reliability, performance tuning and availability of the database.

If you want to roll your own vs Microsoft SQL Server Maintenance Plans


Start Wizard.



Common database maintenance plan tasks

Database Backup:

Creates a backup of data pages from the database, which can be used log backup or to restore the database in case of a failure or data loss.

Check database integrity:

Checks the database for any corruption or data consistency issues.

Rebuild index:

Rebuilds indexes on the database to optimize performance and reduce fragmentation.

Update statistics:

Updates the query optimizer statistics for the database to ensure accurate query performance.

Shrink database:

Reduces the size of the database by reclaiming unused space.

SQL Server Maintenance Plans database maintenance plans are a critical aspect of managing and maintaining a SQL Server database, and they can greatly improve the overall performance, reliability, and availability of the database.

Here are some potential pros and cons of using a SQL Server Maintenance Plan:

Pros:

Ease of use: Maintenance Plans provide a simple, graphical interface for setting up and scheduling routine database maintenance tasks, which can be helpful for users who are not familiar with SQL Server or scripting.

Predefined templates: Maintenance Plans come with predefined templates for common maintenance tasks such as database backups, integrity checks, and index optimization. These templates can save time and effort by reducing the need for users to create custom scripts.

Automation: Maintenance Plans can be scheduled to run automatically, reducing the need for manual intervention and improving the reliability and availability of the database.

Centralized management: Maintenance Plans can be centrally managed and monitored, making it easier to ensure that routine maintenance tasks are being completed on time and as expected.

Cons:

Limited flexibility: Maintenance Plans can be limited in terms of flexibility and customization, which may be a disadvantage for users who require more fine-grained control over their maintenance tasks.

Performance impact: Depending on the nature and frequency of the maintenance tasks, running them on a production system can have a performance impact that may be undesirable during business hours.

Overhead: Depending on the size of the database and the complexity of the maintenance tasks, running them on a regular basis can generate a significant amount of overhead and storage requirements.

Maintenance Plan failures: Maintenance Plans can fail due to various reasons such as connectivity issues, backup media errors, and insufficient disk space. In such cases, it can be challenging to diagnose and resolve the problem, especially for users who are not experienced with SQL Server.

SQL Server Maintenance Plan Wizard

Maintenance Plan Wizard offers easy to use tools and information about select maintenance tasks page these maintenance plans create from other tasks in our database. Thus maintenance tasks are relatively easy with the maintenance plan wizard, just choose maintenance plan wizard with help of this tool. Then click on the Maintenance Plans section under Management then pick Maintenance Plan Wizard.



Name your plan

Here are some of the main properties that can be selected in the Wizard options:

Name: This is the name of the new Maintenance Plan that will be created.

Description: This is an optional description of the Maintenance Plan, which can be helpful for providing additional context or information about the why create a maintenance plan.

Schedule: This property allows you to set the schedule for the the Maintenance Plan designer, including the frequency and time of day that the maintenance plan designer will be run manually.

Select Tasks

Note My Suggested Tasks Are Selected. We are not doing transaction log backups and differential backups in this tutorial.

When using the Maintenance Plan Wizard in SQL Server Management Studio, one of the steps is to select Maintenance Plan Tasks.


Here are brief descriptions of each task listed:


Back Up Database (Full): This task creates a full backup of selected databases in sql server, including all data and log files. --More Info about different Backup Types in SQL Server

Back Up Database (Differential): This task creates a backup of changes made since the last full backup, which can be used to restore the database to a more recent point in time.

Back Up Database (Transaction Log): This task creates a backup of the database design transaction log, which can be used to restore the database or transaction log to a specific point in time. --How To Determine If you need Transaction-Logs Backups

Check Database Integrity: This task checks the consistency and integrity of selected system databases, and can help detect and repair issues with the database. --More Info On DBCC Check DB

Rebuild Index: This task rebuilds or defragments indexes on selected tables, which can help improve query performance and reduce fragmentation. --Detailed Information on Indexes and How To Manage Them

Shrink Database: This task reduces the size of selected databases by removing unused space and reclaiming disk space.

Maintenance Cleanup Task: This task deletes old backup files and other core database maintenance tasks and files that are no longer needed, which can help reduce storage requirements and keep the database environment clean.

Execute SQL Server Agent Job: This task allows you to execute a SQL Server Agent service job as part of the Maintenance Plan. --More info on SQL Agent Jobs

Notify Operator: This task sends an email notification to a specified operator when the Maintenance Plan completes or encounters an an error message. --More Info on setting up alerts for jobs

Suggested Order Of Maintenance Tasks

The suggested order of maintenance tasks is:

CHECKDB: This task should be the first one to perform, as it checks the logical and physical consistency of the database, and helps to identify any other data corruption or inconsistencies. It is crucial to run this task regularly to prevent data loss.

Backup Full: After CHECKDB, the next task should be to take a full backup of the database. This ensures data recovery and full backups in case of any disaster or failure. A full backup of sql database is essential for disaster recovery and restoring data in case of any issues.

Reindex: Once the backup is taken, the next task should be to reindex the database. Reindexing helps to improve the performance of the database by optimizing the indexes and reducing fragmentation. It is recommended to reindex regularly to ensure that the database is running at peak performance.

Clean up and stats: After reindexing, the last task is to clean up and update the statistics of the database. This involves deleting unnecessary data and updating the statistics to ensure that the query optimizer is using accurate information to create execution plans.



The order of these tasks is important because it ensures that the integrity of the database is maintained, the backup integrity, and data can be restored in case of any issues. Running CHECKDB first helps to identify any corruption or inconsistencies in the database, which can then be resolved before taking a backup. Taking a backup after running CHECKDB ensures that the database backup is free from any corruption. Reindexing after taking a backup ensures that damaged data in the backup is optimized and ready for recovery in case of any issues. Finally, cleaning up and updating the statistics helps to ensure that the database is running at peak performance.

Check Integrity Task - Select All DB's - Unless you have a reason not to



Define Backups

Backup DB's once more, unless you have a good reason to skip a backup on a database files a specific database, you should backup everything


I like to create a file for each database, I do not like a folder for data files for each database backup location. Also, note the file type extension does not have a .bak, it's just bak

Use backup compression, I do not specify a backup to expire.

Other Options:

Copy-only backup: This option allows you to create a copy-only backup, which does not affect the sequence of regular SQL Server backups. This can be useful for creating backups for ad-hoc purposes, such as creating a backup before making major changes to the database.

Backup set expires: This option specifies when the backup set will expire. After the expiration date, the backup set can be overwritten by subsequent backups. This can help manage the amount of disk space required for backups.

Retain full backup to history: This option specifies how long to retain the backup history. This can help you track the backup history and restore the database to a specific point in time if needed.

Verify backup when finished: This option specifies whether to verify the backup file after it is created. This can help ensure that the backup file is usable if it is needed for a restore operation.

Perform checksum before writing to media: This option specifies whether to perform a checksum before writing the backup file to the media. This can help detect any errors that occur during the backup process.

Continue on error: This option specifies whether to continue the backup process if errors occur. This can be useful for situations such commands where you want to create a backup transaction log, even if some errors occur during log backup.

Encrypt backup: This option allows you to encrypt the backup file. Encryption can help protect sensitive data in the backup file.

Backup compression: This option allows you to specify whether to compress the backup file. Compressing the backup can reduce the storage space required for the backup, but it can also increase the time required to create the backup.



Indexing

Scan type: This option allows you to choose the type of scan that will be used during the index rebuild operation. There are two types of scan available: "Online" and "Offline". Online scan allows the indexes to be available for read and write operations during the rebuild process, while offline scan does not.

Fill factor: This option determines the amount of space left empty on each index page when it is rebuilt. This can be useful for reducing index fragmentation and improving performance. The default value is 0, which means that each index page will be filled completely.

Sort results in tempdb: This option specifies whether to sort the index data in tempdb during the rebuild process. Sorting in tempdb can improve performance, especially for larger indexes. However, it can also consume more disk space on the server.

Maximum degree of parallelism (MAXDOP): This option determines the maximum number of processors that can be used during the index rebuild operation. The default value is 0, which means that the server will use all available processors.

Online rebuild: This option specifies whether to perform the index rebuild operation online or offline. If this option is set to "True", the index will be rebuilt online, which means that it will be available for read and write operations during the rebuild process. If set to "False", the index will be rebuilt offline.

Default space per page: This option determines the amount of free space reserved for future index page growth. The default value is 0, which means that the server will use the default value for index pages in the database.

Page count: This option determines the number of pages to rebuild in each batch. A smaller page count can reduce the impact of the rebuild operation on server resources and improve performance.



Select Database: This option allows you to choose the database for operating system in which you want to log backups and manage the statistics.

Select Tables or Views: This option allows you to choose the tables or views for which you want to manage the statistics. You can either select specific tables or views or choose all tables and views in the given database together.

Update Statistics: This option specifies whether to update statistics for the selected tables or views. You can choose to update statistics either automatically or manually.

Full Scan: This option specifies whether to perform a full scan when updating the statistics. A full scan ensures that statistics are accurate, but can take a long time on large tables. Alternatively, you can choose to update statistics with a sample of the data, which can be faster but may not be as accurate.

Sampling: This option allows you to specify the sampling rate for updating statistics with a sample of the data. The default value is 20%, but you can choose to increase or decrease the sampling rate based on the size of the table and the accuracy required.

Maximum Degree of Parallelism (DOP): This option allows you to specify the maximum number of processors to use when updating the statistics. By default, SQL Server uses all available processors, but you can limit the number of processors used to reduce the impact on the server.

Resample: This option allows you to specify whether to resample the data when updating statistics. Resampling can help improve the accuracy of statistics, but can also increase the time required to update the statistics.

Update Statistics for System Tables: This option allows you to specify whether to update statistics for system tables.



Other Screens that should be be self explanatory or I just keep the defaults



Here is what the finished plan looks like

Here is what the SQL Ahent Job Looks like

Additional Resources



Recent Posts

See All

תגובות

דירוג של 0 מתוך 5 כוכבים
אין עדיין דירוגים

הוספת דירוג

Get in Touch

Thanks for submitting!

bottom of page