In this guide, we will install and configure the industry-standard SQL Server Maintenance Solution by Ola Hallengren. This solution handles backups, integrity checks, and index optimization automatically. It is trusted by organizations like NASA, HP, and Microsoft.
Before we begin, ensure your SQL Server Agent service is running, as it is required to execute the scheduled jobs.
Open Services (services.msc) and locate SQL Server Agent. Set its Startup Type to Automatic and start the service. It depends on the main SQL Server service, so ensure that is running as well.
Visit ola.hallengren.com and download the MaintenanceSolution.sql script. This single script contains everything needed for backups, integrity checks, and index maintenance.
Open SQL Server Management Studio (SSMS) and connect to your instance. Drag and drop the downloaded MaintenanceSolution.sql file into the query window.
You can customize the script parameters before execution. Although optional (it uses default server paths otherwise), it is good practice to review these:
168 (one week) to automatically delete old files and save disk space.'Y') to save significant storage space.Once configured, press Execute (F5). You should see "Command completed successfully." This creates all the necessary stored procedures and SQL Agent jobs.
Expand the SQL Server Agent > Jobs folder. You will see a list of new jobs created by the script (e.g., DatabaseBackup - USER_DATABASES - FULL).
To test, right-click the DatabaseBackup - USER_DATABASES - FULL job and select "Start Job at Step...". This will immediately back up all user databases.
Navigate to your backup directory. You should see a folder structure created for your server and databases, containing the compressed backup files (.bak). Check that the file size is compressed compared to the actual database size.
The jobs are created but not scheduled. You must define when they run. Here is a recommended weekly schedule for a standard production environment: