SQL Server Backup: Full vs. Differential vs. Log and a Practical Plan

Many finance, inventory, payroll and office automation applications use SQL Server. Backing up the whole virtual machine is necessary, but it is not enough for the database. If you want to restore data to an hour ago, or restore just one database to another server, you need SQL Server’s own backups.

Three backup types

• Full: A complete copy of the database at the moment of backup, and the base for every restore.
• Differential: Only the changes since the last full backup. Each differential grows until the next full backup, and a restore needs only the full backup and the latest differential.
• Transaction Log: The transactions recorded since the last log backup. With this type you can restore the database to a specific point in time, for example a few minutes before data was deleted by mistake.

Understand the recovery model

Each database has a recovery model that determines whether log backups are possible:

• Simple: Log space is freed automatically and there are no log backups. You can only restore to the time of the last full or differential backup.
• Full: Transactions stay in the log until they are backed up, and point-in-time restore is possible.
• Bulk-logged: Similar to Full, but some bulk operations are minimally logged; meant for special use cases.

If a database is in the Full model and no log backups are taken, the log file keeps growing until the disk fills up. This is a common reason finance applications suddenly stop working.

A practical plan

The right plan depends on each database’s RPO. For the main database of a mid-sized company, this schedule is a good starting point:

1. Set the recovery model to Full.
2. Run a full backup nightly or weekly, depending on database size.
3. Run a differential backup every night when no full backup is taken.
4. Run log backups every 15 to 60 minutes during business hours, depending on how much data loss is tolerable.
5. Automatically move backup files to separate storage outside the database server.

For less important databases, the Simple model with a nightly full backup is enough. Example commands:

• BACKUP DATABASE [FinanceDB] TO DISK = N'E:\Backup\FinanceDB_full.bak' WITH CHECKSUM, COMPRESSION
• BACKUP DATABASE [FinanceDB] TO DISK = N'E:\Backup\FinanceDB_diff.bak' WITH DIFFERENTIAL, CHECKSUM
• BACKUP LOG [FinanceDB] TO DISK = N'E:\Backup\FinanceDB_log.trn' WITH CHECKSUM

In practice, include the date and time in file names so each backup has its own file. Backup compression is not supported in every edition; for example, remove this option in Express. In editions with SQL Server Agent, schedule backups with a Maintenance Plan or a Job. In Express, which has no Agent, you can run sqlcmd from Windows Task Scheduler.

Restore order

1. If the database is still accessible, first take a tail-log backup so the latest transactions are not lost.
2. Restore the latest full backup with the NORECOVERY option.
3. Restore the latest differential taken after it, also with NORECOVERY.
4. Restore the subsequent log backups in order, and in the final step use RECOVERY or STOPAT to stop at the desired moment.

Common mistakes

• Keeping backup files only on the database server’s own disk.
• Taking ad hoc manual full backups without COPY_ONLY. This changes the base for later differentials and makes restores confusing.
• Relying on successful jobs without running RESTORE VERIFYONLY and real test restores.
• Forgetting system databases such as master and msdb, which hold logins and jobs.

Checklist

• Each database’s recovery model was chosen deliberately.
• In the Full model, log backups run regularly and log size is monitored.
• Backup files are moved off the server.
• Backups are taken with CHECKSUM and periodically restored on a test server.
• System databases are backed up too.

A proper database backup is the difference between “a few minutes of rework” and “days of re-entering documents.” ITPlus724 engineers can review and implement a backup and recovery plan for your company’s databases.