SQL Server backup types outline how the data is handled. Here we have several options, but they all start with a full backup.
A full backup will maintain all the data files and the transaction log. A full backup is required before any of the other types can be implemented.
Once a full backup is in place, a differential backup can be made. This only stores the parts of the database that have changed since the last full backup was taken. If a database holds 10 records and a full backup is made, then 5 more records get added, the differential backup will only have a record of the five recent additions. If another full backup was made at that point, it would contain all 15.
A transaction log backup will maintain the log history of what’s changed since the last full backup was created. This is what allows for the point-in-time restore since the last full backup without technically saving a copy of the actual data file again, as in the differential backup.
When restoring a database from anything other than a full backup, the process will involve piecing together the individual backups to obtain a complete restore of the database. A typical scenario might have a full backup taken each night at midnight, then transaction logs every hour until noon or a differential backup occurs. Then more transaction log backups every hour until the next full backup at midnight. If a restore was required at noon, the process would involve restoring the full backup from the night before and then tacking on the differential backup from noon. If a restore was required for 3pm, the process would start with the full backup from midnight, then add the differential from noon which bundles up all the intermediate transactions, and then append the transaction logs in sequence for 1pm, 2pm, and finally 3pm.
Sources:
Comments