- SQL Server backup and restore operations occur within the context of the recovery model of the database
- Recovery models are designed to control transaction log maintenance.
- A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available.
- Each database can have only one recovery model, but each of your databases can use a different recovery mode.
- The database recovery model can be changed at any time, but this will impact your backup chain, so it is a good practice to issue a full backup after you change your recovery model.
- Types of recovery model
b. Full Recovery Model
c. Bulk-Logged Recovery Model
How to check recovery model of database.
For 2000
SELECT name, (SELECT DATABASEPROPERTYEX(name, 'RECOVERY')) RecoveryModel FROM master..sysdatabases ORDER BY name
For 2005 & above
SELECT name, recovery_model_desc FROM master.sys.databases ORDER BY name
OUTPUT:
Reference:-
https://www.mssqltips.com/sqlservertutorial/6/types-of-sql-server-backups/ http://www.sqlbackuprestore.com/introduction.htm
For 2000
SELECT name, (SELECT DATABASEPROPERTYEX(name, 'RECOVERY')) RecoveryModel FROM master..sysdatabases ORDER BY name
For 2005 & above
SELECT name, recovery_model_desc FROM master.sys.databases ORDER BY name
OUTPUT:
More:
Reference:-
https://www.mssqltips.com/sqlservertutorial/6/types-of-sql-server-backups/ http://www.sqlbackuprestore.com/introduction.htm
No comments:
Post a Comment