SQL Server backup types: full, differential and transaction log
How often, and with which backup type, should you back up SQL Server databases? Recovery models and a sample backup plan.
Most accounting, ERP and CRM applications keep their data in Microsoft SQL Server. These databases are a company's most critical data and they change constantly. A single nightly backup can mean losing every record entered during the day.
Three backup types
Full backup: A copy of the entire database at that moment. Every restore starts with a full backup.
Differential backup: Contains the data that changed since the last full backup. Much smaller and faster than a full backup.
Transaction log backup: Contains every transaction since the previous log backup. Taken frequently, it cuts the data-loss window down to minutes.
The recovery model matters
Every SQL Server database has a recovery model:
- Simple: No transaction log backups. You can only return to the moment of the last full or differential backup.
- Full: Transaction log backups are possible. This is the recommended model for critical databases.
If a database uses the Full recovery model and you don't take regular log backups, the log file keeps growing and can fill the disk — a problem we see often.
A sample backup plan
For the ERP database of a mid-sized company:
- A weekly full backup (for example, Sunday night)
- A daily differential backup (every night)
- A transaction log backup every 15–30 minutes during business hours
With this plan, if something goes wrong you can bring the database back to the moment of the latest log backup. Use the GFS model for retention: for example, daily backups for two weeks, weeklies for three months and monthlies for a year.
SQL Server backup with AlpCloud
The AlpCloud agent backs up SQL Server in an application-aware way:
- Schedule full, differential and transaction log backups in one plan.
- Restore a database to the same or a different server — handy for testing or migration too.
- Backups are compressed and encrypted with AES-256 before they are sent.
- Get email notifications for failed or overdue backups.
You can also back up your on-premises Exchange server, application-aware, from the same console.
Don't forget: test your restores
Having a backup file doesn't mean the database will come back. Regularly restore a backup to a different server and check that the application works.
Learn more on our SQL Server and Exchange backup page.


