Full, Differential, and Transaction Log Backups in SQL Server

2017-04-16 · mssql, backup, tsql

SQL Server gives you three backup types that work together: a full backup is the baseline, a differential captures everything changed since the last full, and a transaction log backup captures the log records since the previous log backup. Combining them lets you keep the nightly window short while still being able to restore to any point in time, which is what most business owners actually mean when they say "we need backups".

I've been burned by a broken chain more than once, so this is the schedule I now set up on a SQL Server 2016 instance, how I restore it in the right order, and what breaks the chain without telling you.

You need a backup share with enough space (backing up to the same disk as the data files is not a backup), write permission for the service account on that folder, and a database in FULL or BULK_LOGGED recovery. Check with SELECT name, recovery_model_desc FROM sys.databases;

Set the recovery model

ALTER DATABASE ShopDB SET RECOVERY FULL;

Note that after switching from SIMPLE to FULL the log chain does not start until you take a full backup. Until then the database behaves as if it were still in simple recovery.

Full backup

BACKUP DATABASE ShopDB
TO DISK = N'\\backupsrv\sql\ShopDB\ShopDB_FULL_20170416.bak'
WITH COMPRESSION, CHECKSUM, STATS = 10, INIT;

COMPRESSION is available on Standard Edition since 2008 R2 and typically cuts the file to a quarter of the size. CHECKSUM verifies page checksums while reading and writes a checksum for the backup itself. INIT overwrites the media set in that file; without it, successive backups append to the same file and the file grows forever.

I do not use COPY_ONLY here. A copy-only full backup does not reset the differential base, which is exactly what you want for an ad-hoc copy for a developer, but not for your scheduled baseline.

Differentials

BACKUP DATABASE ShopDB
TO DISK = N'\\backupsrv\sql\ShopDB\ShopDB_DIFF_20170416_1200.bak'
WITH DIFFERENTIAL, COMPRESSION, CHECKSUM, INIT;

A differential contains all extents changed since the last non-copy-only full backup, not since the last differential. That means each one gets larger during the week until the next full resets the base, and to restore you only ever need the full plus the latest differential.

Log backups

BACKUP LOG ShopDB
TO DISK = N'\\backupsrv\sql\ShopDB\ShopDB_LOG_20170416_1215.trn'
WITH COMPRESSION, CHECKSUM, INIT;

Run these every 15 minutes (or whatever data loss the business can live with) from a SQL Server Agent job. I'd rather have too many small log files than a 2 GB one every hour. Log backups also truncate the inactive part of the log, so they are what keeps the .ldf file from growing without bound in full recovery.

Restoring, in the right order

The restore sequence is full, then the latest differential, then every log backup after that differential, all WITH NORECOVERY except the final step:

RESTORE DATABASE ShopDB FROM DISK = N'\\backupsrv\sql\ShopDB\ShopDB_FULL_20170416.bak'
    WITH NORECOVERY, REPLACE;
RESTORE DATABASE ShopDB FROM DISK = N'\\backupsrv\sql\ShopDB\ShopDB_DIFF_20170416_1200.bak'
    WITH NORECOVERY;
RESTORE LOG ShopDB FROM DISK = N'\\backupsrv\sql\ShopDB\ShopDB_LOG_20170416_1215.trn'
    WITH NORECOVERY;
RESTORE LOG ShopDB FROM DISK = N'\\backupsrv\sql\ShopDB\ShopDB_LOG_20170416_1230.trn'
    WITH STOPAT = '2017-04-16 12:22:00', RECOVERY;

STOPAT gives you point-in-time recovery inside the last log file. Before a disaster, also take a tail-log backup with BACKUP LOG ShopDB ... WITH NORECOVERY if the data file is gone but the log file is intact; this captures the last few minutes.

Checking the backups are any good

I verify every backup file right after writing it:

RESTORE VERIFYONLY FROM DISK = N'\\backupsrv\sql\ShopDB\ShopDB_FULL_20170416.bak' WITH CHECKSUM;

And check what is in the history tables so you know the chain is intact:

SELECT bs.database_name, bs.type, bs.backup_start_date, bs.is_copy_only,
       bmf.physical_device_name
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE bs.database_name = 'ShopDB'
ORDER BY bs.backup_start_date DESC;

Type D is full, I is differential, L is log. The real verification, however, is a test restore onto a different server; I do this monthly and time it, because that number is your actual recovery time.

When it goes wrong

"BACKUP LOG cannot be performed because there is no current database backup." (Error 4214) You switched to FULL recovery but never took a full backup afterwards. Take one and the log backups will start working.

"The log in this backup set begins at LSN ..., which is too recent to apply to the database." (Error 4305) You skipped a log backup in the sequence. Someone or something (often a third-party tool or a well-meaning colleague) took a log backup to a different location. Query msdb.dbo.backupset for the missing file.

"This differential backup cannot be restored because the database has not been restored to the correct earlier state." (Error 3136) A full backup was taken after the one you restored, resetting the differential base. Restore the newer full or use log backups only.

The log file keeps growing. Check SELECT name, log_reuse_wait_desc FROM sys.databases;. If it says LOG_BACKUP, your log backup job is not running.

The three backup types are easy on their own. The hard part is never breaking the chain, and rehearsing a restore before you need one.