Backup
Backup can be understood as a copy of used data pages or a copy of transaction log records. Backup has to be done regularly. Backup is needed not only to restore databases in case of physical or logical failure, but it is also useful when, for example, we want to make a copy of the database or migrate the database to another SQL Server instance. To have our backups proper and complete, we need to consider the following points:
- How to configure the database
- Which types of backups combine together
- How to use backup strategies in conjunction with database usage
Recovery model
Every database has a property called recovery model. The recovery model determines how transactions are logged, and for how long time committed transactions will be stored in the transaction log. The recovery model is set by T-SQL command, ALTER DATABASE
:
-- setting full recovery model ALTER DATABASE <database_name> SET RECOVERY FULL
The recovery model has three possible options:
SIMPLE
BULK_LOGGED
FULL
When the recovery...