In this guide, I will explain the differences between Full, Differential, and Transaction Log backups in SQL Server. We will simulate a disaster scenario where a database is corrupted and then walk through the steps to fully restore it using a backup chain.
Before restoring, it is crucial to understand how different backup types work together to minimize data loss.
A Full Backup captures the entire database at a specific point in time (e.g., every Saturday). It serves as the foundation for any restore operation.
Differential backups record all changes made since the last Full Backup. They grow in size over time until the next Full Backup resets the chain. For example, if you have a Full Backup from Saturday and a Differential from Friday, restoring these two will bring your data up to Friday.
Transaction Log backups capture every single transaction and are typically run frequently (e.g., every 5-60 minutes). They allow you to restore to a very specific point in time, minimizing data loss to just minutes before an incident.
Requirement: To use Transaction Log backups, your database Recovery Model must be set to Full (default is Simple).
Let's assume "Timmy from HR" accidentally updated all product prices to $1. We need to recover the database.
First, take the database Offline immediately to prevent further transactions or user access.
Navigate to the physical location of your database files (MDF and LDF). Rename the existing files (e.g., append _old) to keep them as a safety measure. Then, delete the database from SQL Server Management Studio (SSMS) to clear the slate for restoration.
We will now rebuild the database using our backup files.
Right-click Databases in SSMS and select Restore Database. Choose Device and browse for your backup files. You need to add them in order:
In the Files tab, ensure the restore paths are correct. You can rename the destination files (e.g., add today's date) to avoid overwriting conflicts or to clearly identify the restored version.
In the Options tab, ensure "Close existing connections" is checked if necessary, and "Restore with Recovery" is selected. Click OK to begin.
Once complete, you should see a "Database successfully restored" message. Verify the data by checking the table (e.g., the product prices should be back to normal).