Any good database maintenance plan will include the core tasks of backups, integrity checks, index checks, and other types of maintenance tasks. One such tool that has been highly recommended for the last several years is the SQL Server Maintenance Solution created by Ola Hallengren (https://ola.hallengren.com).
The SQL Server Maintenance Solution is supported on SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, and SQL Server 2014.
To implement the SQL Server Maintenance Solution as your database maintenance plan, use the following steps:
- Download MaintenanceSolution.sql at https://ola.hallengren.com/scripts/MaintenanceSolution.sql.
- In the script, find this line:
SET @BackupDirectory = N’C:\Backup’
and replace C:\Backup with the path to your backup directory.
- In the script, find this line:
SET @CleanupTime = NULL
and replace NULL with your cleanup time. The cleanup time is the the number of hours after which the backup files are deleted.
- Execute MaintenanceSolution.sql. This script creates all the objects and jobs that you need.
- Go into [SQL Server Agent] / [Jobs] and start the jobs that have been created. Verify that these jobs are completing successfully. Verify that the backup files are being created. Check the output files in the error log directory.
- Schedule the jobs.
If you are using SQL Server Express or have any other questions, refer to the FAQ page at https://ola.hallengren.com/frequently-asked-questions.html