As a SQL Server developer, there are certain scripts that are essential for maintaining the health and performance of your databases. In this article, we will discuss three important scripts: Backup, Integrity Check, and Index Optimization.

Backup Maintenance

One common issue that many developers face is the error message “BACKUP LOG cannot be performed because there is no current database backup” or “Cannot perform a differential backup for database ”, because a current database backup does not exist.” This usually occurs when a new database is created or when the recovery model of a database is changed from Simple to Full.

To address this issue, SQL expert Ola Hallengren has developed a backup solution that checks if a differential or transaction log backup can be performed before initiating the backup. This is done by checking the system views sys.master_files.differential_base_lsn and sys.database_recovery_status.last_log_backup_lsn.

Here is an example of how to use Ola Hallengren’s backup script:

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

By specifying the parameter @ChangeBackupType as ‘Y’, the script will automatically adjust the backup type based on the database’s recovery model. For new databases in Full recovery model, the backup type will be set to full. For databases that were recently changed to Full recovery model, the backup type will be set to differential. This ensures that the database is backed up properly and eliminates the error messages mentioned earlier.

Index Optimization

Another important aspect of database maintenance is index optimization. Indexes can become fragmented over time, leading to decreased query performance. Ola Hallengren’s index optimization script provides a solution for categorizing indexes based on their fragmentation level and taking appropriate actions.

Here is an example of how to use Ola Hallengren’s index optimization script:

EXECUTE dbo.IndexOptimize 
    @Databases = 'USER_DATABASES',
    @FragmentationHigh_LOB = 'INDEX_REBUILD_OFFLINE',
    @FragmentationHigh_NonLOB = 'INDEX_REBUILD_ONLINE',
    @FragmentationMedium_LOB = 'INDEX_REORGANIZE',
    @FragmentationMedium_NonLOB = 'INDEX_REORGANIZE',
    @FragmentationLow_LOB = 'NOTHING',
    @FragmentationLow_NonLOB = 'NOTHING',
    @FragmentationLevel1 = 5,
    @FragmentationLevel2 = 30,
    @PageCountLevel = 1000

In this example, indexes with a fragmentation above 30% will be rebuilt, online if possible (no LOB columns), otherwise offline (LOB columns). Indexes with a fragmentation between 5% and 30% will be reorganized. Indexes with a fragmentation below 5% or a size below 1000 pages will not be touched.

If you are using partitioning, Ola Hallengren’s script also provides a parameter, @PartitionLevel, to perform index rebuilds and reorganizations on the partition level. Additionally, you can specify whether to perform sort operations in tempdb using the parameter @SortInTempdb, and set a fillfactor using the parameter @FillFactor.

By regularly running Ola Hallengren’s backup, integrity check, and index optimization scripts, you can ensure the reliability, integrity, and performance of your SQL Server databases. These scripts have been widely used and trusted by SQL experts and enthusiasts alike.

For more detailed documentation and to download the scripts, please visit http://ola.hallengren.com/Documentation.html.