Recovery Model of MS SQL Server database
Check the Recovery Model of MS SQL Server Databases:
SELECT name AS [Database Name],recovery_model_desc AS [Recovery Model] FROM sys.databases;
SELECT 'ADVENTUREWORKS' AS [Database Name],DATABASEPROPERTYEX('ADVENTUREWORKS', 'RECOVERY') AS [Recovery Model];
Change the Recovery Mode with following command:
ALTER DATABASE dbName SET RECOVERY recoveryOption
ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE
Option of RECOVER MODEL:
SIMPLE Recovery Mode:
SQL Server maintains only a minimal amount of information in the transaction log. It can clean transaction log time to time. You can restore full or differential backups only. It is not possible to restore such a database to a given point in time. You can lost changes which you made after backup.
The bulk-logged model records these operations in the transaction log using a technique known as minimal logging.It will create minimal log of large transaction. It operate like full recovery mode. This saves significantly on processing time, but prevents you from using the point-in-time restore option.
This Mode ensure the data is not lost that includes a combination of full and differential database backups in conjunction with transaction log backups.It allow you to restore point in time recovery with transaction log backup.