Database Maintenance

Navigate to the SQL Server Maintenance Solution website by clicking the following link:

SQL Server Maintenance Solution

Open SQL Server Management Studio (SSMS).

1. Launch SSMS and connect to your SQL Server instance.

2. Enter your server credentials and ensure you have administrative rights.

Run the Olla script you downloaded to create jobs and stored procedures in the Master database.

Confirm the following stored procedures are created in the Master database and SQL Server Agent jobs folders by opening the applicable folders in the Object Explorer screen.

1. Open the Stored Procedures folder and ensure the following stored procedures are created:

   • dbo.DatabaseBackup

   • dbo.DatabaseIntegrityCheck

   • dbo.IndexOptimize

   

2. Open the SQL Server Agent's Jobs folder and ensure the following jobs are created:

   • DatabaseBackup - SYSTEM_DATABASES - FULL

   • DatabaseBackup - USER_DATABASES - DIFF

   • DatabaseBackup - USER_DATABASES - FULL

   • DatabaseBackup - USER_DATABASES - LOG

   • DatabaselntegrityCheck - SYSTEM_DATABASES

   • DatabaselntegrityCheck - USER_DATABASES

   • IndexOptimize - USER_DATABASES

   

Now, customize the Olla script and edit each job based on your requirements.

First, edit the backup jobs.

1. Edit the FULL backup job for user databases.

   1. In the Object Explorer screen, right-click the Database Backup - USER_DATABASES - FULL job created above and select Job Properties.

   2. On the Job Properties pop-up window, select the Steps page on the left.

   3. Click the Edit button.

   4. On the Job Step Properties pop-up window, paste the following script in the Command field:

      EXECUTE dbo.DatabaseBackup
      @Databases = 'USER_DATABASES',
      @Directory = 'C:\Backup',
      @BackupType = 'FULL',
      @Verify ='Y'
      
      

   5. Click the OK button.

2. Edit the Differential (DIFF) backup job for user databases.

   1. In the Object Explorer screen, right-click the Database Backup - USER_DATABASES - DIFF job created above and select Job Properties.

   2. On the Job Properties pop-up window, select the Steps page on the left.

   3. Click the Edit button.

   4. On the Job Step Properties pop-up window, paste the following script in the Command field:

      EXECUTE dbo.DatabaseBackup
      @Databases = 'USER_DATABASES',
      @Directory = 'C:\Backup',
      @BackupType = 'DIFF',
      @Verify ='Y'
      
      

   5. Click the OK button.

3. Edit the LOG backup job for user databases.

   1. In the Object Explorer screen, right-click the Database Backup - USER_DATABASES - LOG job created above and select Job Properties.

   2. On the Job Properties pop-up window, select the Steps page on the left.

   3. Click the Edit button.

   4. On the Job Step Properties pop-up window, paste the following script in the Command field:

      EXECUTE dbo.DatabaseBackup
      @Databases = 'USER_DATABASES',
      @Directory = 'C:\Backup',
      @BackupType = 'LOG',
      @Verify ='Y'
      
      

   5. Click the OK button.

   Note

   Replace the backup directory with your backup location for all types of backups.

4. Next, edit the Index Optimize and Stats Update job for user databases.

   1. In the Object Explorer screen, right-click the Index Optimize - USER_DATABASES job created above and select Job Properties.

   2. On the Job Properties pop-up window, select the Steps page on the left.

   3. Click the Edit button.

   4. On the Job Step Properties pop-up window, paste the following script in the Command field:

      EXECUTE dbo.IndexOptimize
      @Databases = 'USER_DATABASES',
      @FragmentationLow = NULL,
      @FragmentationMedium = 
      'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
      @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
      @FragmentationLevel1 = 5,
      @FragmentationLevel2 = 30,
      @UpdateStatistics = 'ALL',
      @OnlyModifiedStatistics = 'Y'
      
      

   5. Click the OK button.

Schedule the SQL Server Agent job.

1. In the Object Explorer screen, right-click the Database Backup - USER_DATABASES - FULL job created above and select Job Properties.

2. On the Job Properties pop-up window, click the Schedules page on the left.

3. Click the New... button to set up a maintenance schedule.

4. On the New Job Schedule pop-up window, set the frequency (daily, weekly, or monthly) and time for when the SQL Server Agent job should run.

5. Click the OK button.

Monitor and validate the SQL Server Agent job.

1. Ensure the SQL Server Agent is running.

2. If the SQL Server Agent is not running, right-click SQL Server Agent in the Object Explorer screen and select Start.

Check the Job History.

1. After the job runs, right-click the job and select View History to check the results.

2. On the Log File Viewer pop-up window, ensure there are no errors, and the script has run successfully.