MSSQL Recovery Model
SIMPLE
Changes since the most recent backup are unprotected.
No log backups required.
Does not support Log shipping, Always On or Database mirroring.
FULL
Can recover to a specific point in time.
Log backups required.
Supports Log shipping, Always On and Database mirroring.
Check
Check
SELECT name, recovery_model_desc
FROM sys.databases;
GO
SSMS
SSMS
Right click your database in the Object Explorer and select Properties...
Change To FULL
Change To FULL
USE [master] ;
ALTER DATABASE [myDatabase] SET RECOVERY FULL ;
Take a backup.
Schedule Transaction Log Backups
SSMS
SSMS
Navigate to the Properties screen shown in the Check section above and select FULL from the Recovery model dropdown.
Take a backup.
Schedule Transaction Log Backups
Change To SIMPLE
Change To SIMPLE
ALTER DATABASE [myDatabase] SET RECOVERY SIMPLE;
Bibliography
Bibliography
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/view-or-change-the-recovery-model-of-a-database-sql-server?view=sql-server-ver15https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server?view=sql-server-ver16https://learn.microsoft.com/en-us/sql/relational-databases/logs/troubleshoot-a-full-transaction-log-sql-server-error-9002?view=sql-server-ver16https://learn.microsoft.com/en-us/sql/relational-databases/logs/change-the-target-recovery-time-of-a-database-sql-server?view=sql-server-ver16https://www.goengineer.com/blog/change-sql-from-full-recover-to-simple-recovery-modelhttps://dba.stackexchange.com/questions/69467/is-it-ok-to-change-from-full-recovery-to-simple-recovery-in-sql-serverhttps://dzone.com/articles/sql-recovery-model-simple-vs-full