Database Backups - Recovery Models

 There are many different strategies for doing database backups.  The key thing to understand in appreciating the differences are between transactional versus structural.

If you are just backing up transactions, they won't take into account structural (DDL) changes, in particular, the fields/columns of tables and their data types.

Common situation is the following:

A FULL backup every week, DIFFERENTIAL every day, and a TRANSACTION LOG every 15 minutes.

===============

This also becomes the key difference in how you manage database refreshes, from one environment to another (Dev to QA/Test to Prod and back down the food chain).

Structural changes are also the problem in maintaining a match with associated source code changes.  Microsoft's Entity Framework formalized a lot of the terminology for this in the .NET world, with the labeling of these changes as MIGRATIONS.

============

SQLShack: https://www.sqlshack.com/understanding-sql-server-backup-types/

Microsoft: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-overview-sql-server?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/full-database-backups-sql-server?view=sql-server-2016

MSSQLTips:  https://www.mssqltips.com/sqlservertutorial/6/types-of-sql-server-backups/

====================

Full Backups:

A full backup must be done at least once before any of the other types of backups can be run—this is the foundation for every other kind of backup.

Differential: Changes since last full backup (? so there is no such thing as Differential from last Differential?)

Transaction Log: Doesn't save data per se, just the LOGS of the data changes.  (? MUCH smaller than Differential?)

I wonder if the RECOVERY MODELS Microsoft has are an attempt to describe these three backup types.  To me that is the first order of confusion.  Because now you also have these three terms:

Simple, Full, Bulk Logged

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server?view=sql-server-ver15

Then you have Log-Shipping and Mirroring, and Backup Sets.

Overarching the notions of backups confusion is the perennial conflation of HIGH AVAILABILITY with DISASTER RECOVERY.  

HA != DR