Before you even think about your backups, you need to understand the recovery models that SQL Server internally uses while the database is in operational mode. A recovery model is about maintaining data in the event of a server failure. Also, it defines the amount of information that SQL Server writes to the log file for the purpose of recovery.
SQL Server has three database recovery models:
- Simple recovery model
- Full recovery model
- Bulk-logged recovery model
This model is typically used for small databases and scenarios where data changes are infrequent. It is limited to restoring the database to the point when the last backup was created. It means that all changes made after the backup are lost. You will need to recreate all changes manually. The major benefit of this model is that the log file takes only a small amount of storage space. How and when to use it depends on the business scenario.