SQL Server backup basics: recovery models, full, differential and log backups, and your first restore

A backup you have never restored is a hope, not a plan. This post covers the minimum every person looking after SQL Server should know, and ends with a restore you can practise today.

#Recovery models

SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases;
ALTER DATABASE SalesDB SET RECOVERY FULL;

Warning

FULL recovery without log backups means the transaction log grows until the disk fills. If you set FULL, schedule log backups the same day.

#The three backup types

#Take backups with T-SQL

BACKUP DATABASE SalesDB
TO DISK = N'D:\Backup\SalesDB_FULL_20261029.bak'
WITH COMPRESSION, CHECKSUM, STATS = 10;

BACKUP DATABASE SalesDB
TO DISK = N'D:\Backup\SalesDB_DIFF_20261029_1200.bak'
WITH DIFFERENTIAL, COMPRESSION, CHECKSUM;

BACKUP LOG SalesDB
TO DISK = N'D:\Backup\SalesDB_LOG_20261029_1215.trn'
WITH COMPRESSION, CHECKSUM;

CHECKSUM validates pages as they are read, so corruption is caught at backup time. COMPRESSION usually makes backups smaller and faster.

#Or with PowerShell (dbatools)

Install-Module dbatools -Scope CurrentUser
Backup-DbaDatabase -SqlInstance SQL01 -Database SalesDB -Type Full -Path D:\Backup -CompressBackup -Checksum
Backup-DbaDatabase -SqlInstance SQL01 -Database SalesDB -Type Log  -Path D:\Backup -CompressBackup
Get-DbaDbBackupHistory -SqlInstance SQL01 -Database SalesDB -Last

For scheduling, use SQL Agent jobs or a maintained solution such as Ola Hallengren's maintenance scripts, which handle file naming, cleanup and logging.

#Check backups exist

SELECT d.name,
       MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS last_full,
       MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END) AS last_diff,
       MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS last_log
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset b ON b.database_name = d.name
WHERE d.name <> 'tempdb'
GROUP BY d.name
ORDER BY d.name;

#Check a backup is readable

RESTORE VERIFYONLY FROM DISK = N'D:\Backup\SalesDB_FULL_20261029.bak' WITH CHECKSUM;
RESTORE FILELISTONLY FROM DISK = N'D:\Backup\SalesDB_FULL_20261029.bak';

VERIFYONLY checks the backup is complete and readable. It does not prove the database inside is consistent; only a restore plus DBCC CHECKDB does that.

#Practise a restore to a new name

Restore a copy alongside the original, so nothing is overwritten:

RESTORE DATABASE SalesDB_Test
FROM DISK = N'D:\Backup\SalesDB_FULL_20261029.bak'
WITH MOVE N'SalesDB'     TO N'E:\Data\SalesDB_Test.mdf',
     MOVE N'SalesDB_log' TO N'F:\Log\SalesDB_Test_log.ldf',
     NORECOVERY, STATS = 10;

RESTORE DATABASE SalesDB_Test FROM DISK = N'D:\Backup\SalesDB_DIFF_20261029_1200.bak' WITH NORECOVERY;
RESTORE LOG SalesDB_Test FROM DISK = N'D:\Backup\SalesDB_LOG_20261029_1215.trn' WITH NORECOVERY;
RESTORE DATABASE SalesDB_Test WITH RECOVERY;

DBCC CHECKDB (SalesDB_Test) WITH NO_INFOMSGS;

Get the logical file names (SalesDB, SalesDB_log) from RESTORE FILELISTONLY. The order is always: full, latest differential, then every log backup after that differential, all WITH NORECOVERY, then WITH RECOVERY once at the end.

Caution

Never run RESTORE DATABASE SalesDB ... WITH REPLACE on production by accident. Always restore to a new name first unless you are deliberately overwriting.

dbatools can build the whole chain for you:

Restore-DbaDatabase -SqlInstance SQL02 -Path \\backupshare\SQL01\SalesDB -DatabaseName SalesDB_Test `
  -DestinationDataDirectory E:\Data -DestinationLogDirectory F:\Log -ReplaceDbNameInFile

#Where to keep backups

• Not only on the same server: copy to a network share or object storage (SQL Server 2022 can back up directly to S3-compatible storage; earlier versions back up to Azure Blob with TO URL).
• Keep at least one copy that ransomware on the server cannot delete (immutable storage, separate credentials).

#Prevent it next time

• Every FULL-recovery database has log backups and an alert when the last log backup is older than its schedule.
• Monthly test restore of each critical database, with DBCC CHECKDB, recorded.
• Backups with CHECKSUM and copies off the server.