Configuring MS SQL Server Backup (Full/Differential/Log)

Configuring MS SQL Server Backup (Full/Differential/Log)

The most common cause of data loss in SQL Server is not hardware failure or ransomware. It is the lack of a verified backup strategy: backups were taken, but nobody tested restoration, and at the critical moment the last working backup was three weeks old. Or the transaction log backup silently failed because databases were in SIMPLE recovery model, and all log backups failed with This command is not supported with simple recovery model. We have encountered such situations dozens of times — so we built a process that eliminates these surprises. Our team has years of experience in SQL Server and has delivered over 50 projects involving backup configuration and recovery.

The Three Backup Types and When to Use Each

Full backup captures the entire database. It may take hours on large databases. It is the base for differential and log backups.

Differential backup only includes pages changed since the last full backup. It grows over time: if a week has passed after a full backup, the differential may be nearly as large. It speeds up recovery compared to replaying the entire log chain.

Transaction Log backup archives transaction log records since the last log backup. It requires the recovery model to be FULL or BULK_LOGGED. It enables point-in-time recovery.

A typical strategy for production OLTP databases:

• Full backup: once a week (Sunday night)
• Differential backup: once a day (weekdays at 02:00)
• Log backup: every 15–30 minutes

With this strategy, the maximum data loss in case of a disaster is 15–30 minutes.

Why Transaction Log Backups Matter

Without log backups, recovery is only possible to the last full or differential backup, meaning data loss of several hours or days. With log backups, you can roll back to any second within the interval. This is especially critical for financial and ERP systems where every transaction counts. If the database is in SIMPLE recovery model, log backup is impossible — you need to switch to FULL. This requires downtime, but it pays off.

Configuration via SQL Server Agent Jobs (T-SQL)

GUI Maintenance Plans are fragile and hard to manage with git. The proper approach is T-SQL scripts in Agent Jobs.

-- Example job for full backup with compression
DECLARE @BackupPath NVARCHAR(500) = 'D:\SQLBackup\Full\AppDB_' + CONVERT(NVARCHAR(20), GETDATE(), 112) + '_' + REPLACE(CONVERT(NVARCHAR(8), GETDATE(), 108), ':', '') + '.bak';
BACKUP DATABASE [AppDB] TO DISK = @BackupPath WITH COMPRESSION, CHECKSUM, STATS = 10, DESCRIPTION = 'Weekly Full Backup';
-- Integrity check after backup
RESTORE VERIFYONLY FROM DISK = @BackupPath WITH CHECKSUM;

-- Transaction Log backup
DECLARE @LogPath NVARCHAR(500) = 'D:\SQLBackup\Log\AppDB_log_' + CONVERT(NVARCHAR(20), GETDATE(), 112) + '_' + REPLACE(CONVERT(NVARCHAR(8), GETDATE(), 108), ':', '') + '.trn';
BACKUP LOG [AppDB] TO DISK = @LogPath WITH COMPRESSION, CHECKSUM, STATS = 5;

The CHECKSUM option adds a checksum to the backup — if a page is corrupted, it is detected during backup, not during restoration in a crisis.

Ola Hallengren Maintenance Solution

For most projects, it is more reasonable to use the Ola Hallengren Maintenance Solution (free, de facto standard in the community) instead of writing custom scripts. This reduces maintenance time by more than 5x — no need to debug your own workarounds. It supports Always On, automatically skips log backup for SIMPLE databases.

-- Example call for full backup of all user databases
EXECUTE dbo.DatabaseBackup
    @Databases = 'USER_DATABASES',
    @Directory = 'D:\SQLBackup',
    @BackupType = 'FULL',
    @Compress = 'Y',
    @CheckSum = 'Y',
    @Verify = 'Y',
    @CleanupTime = 168, -- delete files older than 168 hours (7 days)
    @LogToTable = 'Y';

-- Log backup with cleanup
EXECUTE dbo.DatabaseBackup
    @Databases = 'USER_DATABASES',
    @Directory = 'D:\SQLBackup',
    @BackupType = 'LOG',
    @Compress = 'Y',
    @CheckSum = 'Y',
    @CleanupTime = 48,
    @LogToTable = 'Y';

The solution automatically skips log backup for SIMPLE databases, handles Always On AG (backs up on the preferred replica), and writes history to a table for monitoring.

How Often Should You Test Restoration?

A backup without verification is not a backup. At least quarterly, actually restore a database from backup onto a test server. We include a planned test restoration in every project and train your team to perform it independently. This is the only way to be sure that in a critical situation data will be available.

-- Point-in-time recovery
RESTORE DATABASE [AppDB_Test]
FROM DISK = 'D:\SQLBackup\Full\AppDB_YYYYMMDD_HHMMSS.bak'
WITH NORECOVERY, REPLACE,
MOVE 'AppDB' TO 'E:\TestData\AppDB_Test.mdf',
MOVE 'AppDB_log' TO 'E:\TestData\AppDB_Test_log.ldf';

-- Apply differential
RESTORE DATABASE [AppDB_Test]
FROM DISK = 'D:\SQLBackup\Diff\AppDB_diff_YYYYMMDD_HHMMSS.bak'
WITH NORECOVERY;

-- Apply log backups to the desired point
RESTORE LOG [AppDB_Test]
FROM DISK = 'D:\SQLBackup\Log\AppDB_log_YYYYMMDD_HHMMSS.trn'
WITH NORECOVERY;

RESTORE LOG [AppDB_Test]
FROM DISK = 'D:\SQLBackup\Log\AppDB_log_YYYYMMDD_HHMMSS.trn'
WITH RECOVERY, STOPAT = 'YYYY-MM-DD HH:MM:SS';
-- exact recovery point

Backup Storage and Replication

Local backups are only the first line. For protection against server loss, an external copy is needed:

• SQL Server Backup to URL (Azure Blob Storage) — built-in from SQL Server 2012
• Robocopy/rsync to a separate file server or NAS
• Third-party solutions: Veeam Backup & Replication, Commvault, Veritas

The 3-2-1 rule: three copies, two different media, one off-site.

What Is Included in Backup Configuration?

We offer a comprehensive turnkey service:

• Audit of current infrastructure and recovery model
• Development of backup strategy considering RPO/RTO
• Creation and setup of SQL Agent Jobs (T-SQL) or implementation of Ola Hallengren
• Setup of alerts (email, Telegram) for backup failures
• Configuration of backup replication to external storage
• Documentation of restoration procedure
• Training of your team (2-hour live demo)
• Guarantee of correct backup operation for 1 month after setup

We have been on the market for years and delivered over 50 SQL Server projects. Contact us to get a consultation and estimate for your project within one business day.