Different recovery models in SQL Server:-
There are 3 recovery model present in SQL Server.
1.Full
2.Simple
3.Bulk-Logged.
1.Full:- If a database is in "Full" recovery model, it keeps all transaction data in the transaction log until either a transaction log backup occurs or the transaction log is truncated.
This way all the transactions that are issued in SQL Server first written into the transaction log and then the data is written to the appropriate data file.
This model allows SQL Server to rollback into each step of the process in case of any kind of error or the transaction was cancelled for some reason. So whenever a database
is set to the "Full" recovery model we can can do point in time recovery in case of if its required due to any reason. With full recovery model, you need to setup a regular transaction log backup
to ensure the growth of transaction log files are under control otherwise it will keep on growing until your next full backup.
Reasons when you should choose full recovery model:-
1.Data is very critical and we cant afford to lose it at any cost.
2. There could be a situation where we might need to restore and recover the database in certain point of time.
3. If your database is in mirroring setup.
4. Almost all type of backups are possible if a database in full recovery model i.e complete backup, Differential backups ,File and/or Filegroup backups ,Partial backups ,Copy-Only backups
,Transaction log backups.
When to use it – Full recovery model is recommended for OLTP databases, where you have mostly short lived transactions and you don’t want to lose data for a committed transaction.
How to set the full recovery model for a database:-
ALTER DATABASE testdb SET RECOVERY FULL
GO
2. Simple:-"Simple" recovery model is the most basic recovery model for SQL Server.All transactions are written to the transaction log, but once the transaction is completed and
the data has been written to the data file the space used for transaction log file is now re-usable by the new transactions. Since this space is reused there is possibility to
perform point in time recovery, hence the option we could use to restore the database will be the complete backup or the latest differential backup. Also, since the space in
transaction log can be reused, the transaction log will not grow forever as was mentioned in the "Full" recovery model.
Reasons when you should choose simple recovery model:-
1. When Your data is not that critical and can easily be recreated or restored from backup.
2. When your dbase is running on test enviroment.
3. If your Data is static and does not get changed.
4. If its ok to lose any current transaction due to any kind of failure.
When to use it – Simple recovery model is recommended for In a data warehousing scenario, where you mostly have bulk operations while data loading and in case of failure,
the data can be regenerated from the data source. You can also prefer using Simple recovery model in your development or test environment to ensure the growth of the transaction log
files are controlled..
How to set the simple recovery model for a database:-
ALTER DATABASE testdb SET RECOVERY SIMPLE
GO
3.Bulk-Logged:- In "Bulk-Logged" recovery model bulk operations are minimally logged . The bulk-logged recovery model protects against media failure and, for bulk operations, provides
the best performance and least log space usage.Since bulk operations are minimally logged, this increases the performance of the bulk operations. Also it does not allow the log to
grow unexpectedly when we do some costly operations like rebuild index, create index etc.
The following operations which get minimally logged when your database is in bulk logged recovery model:-
· Bulk import operations (BULK INSERT, and INSERT... SELECT).
· SELECT INTO operations
· WRITETEXT and UPDATETEXT statements when inserting or appending new data into the text, ntext, and image data type columns
· Partial updates to large value data types, using the .WRITE clause in the UPDATE statement when inserting or appending new data
· CREATE INDEX operations (including indexed views).
· ALTER INDEX REBUILD or DBCC DBREINDEX operations.
Reasons when you should choose Bulk-Logged recovery model:-
1. When your data is critical, but you do not want to log large bulk operations.
2. Bulk operations are done at different times versus normal processing.
3. When you dont want to restore the database to a certain point in time.
How to set the Bulk-Logged recovery model for a database:-
ALTER DATABASE testdb SET RECOVERY BULK_LOGGED
GO
References
MSDN: http://msdn.microsoft.com/en-us/library/ms189275.aspx
MSDN: http://msdn.microsoft.com/en-us/library/ms190203.aspx
Please share your ideas and opinions about this topic.
If you like this post, then please share with others.
Please subscribe on email for every updates on mail.
There are 3 recovery model present in SQL Server.
1.Full
2.Simple
3.Bulk-Logged.
1.Full:- If a database is in "Full" recovery model, it keeps all transaction data in the transaction log until either a transaction log backup occurs or the transaction log is truncated.
This way all the transactions that are issued in SQL Server first written into the transaction log and then the data is written to the appropriate data file.
This model allows SQL Server to rollback into each step of the process in case of any kind of error or the transaction was cancelled for some reason. So whenever a database
is set to the "Full" recovery model we can can do point in time recovery in case of if its required due to any reason. With full recovery model, you need to setup a regular transaction log backup
to ensure the growth of transaction log files are under control otherwise it will keep on growing until your next full backup.
Reasons when you should choose full recovery model:-
1.Data is very critical and we cant afford to lose it at any cost.
2. There could be a situation where we might need to restore and recover the database in certain point of time.
3. If your database is in mirroring setup.
4. Almost all type of backups are possible if a database in full recovery model i.e complete backup, Differential backups ,File and/or Filegroup backups ,Partial backups ,Copy-Only backups
,Transaction log backups.
When to use it – Full recovery model is recommended for OLTP databases, where you have mostly short lived transactions and you don’t want to lose data for a committed transaction.
How to set the full recovery model for a database:-
ALTER DATABASE testdb SET RECOVERY FULL
GO
2. Simple:-"Simple" recovery model is the most basic recovery model for SQL Server.All transactions are written to the transaction log, but once the transaction is completed and
the data has been written to the data file the space used for transaction log file is now re-usable by the new transactions. Since this space is reused there is possibility to
perform point in time recovery, hence the option we could use to restore the database will be the complete backup or the latest differential backup. Also, since the space in
transaction log can be reused, the transaction log will not grow forever as was mentioned in the "Full" recovery model.
Reasons when you should choose simple recovery model:-
1. When Your data is not that critical and can easily be recreated or restored from backup.
2. When your dbase is running on test enviroment.
3. If your Data is static and does not get changed.
4. If its ok to lose any current transaction due to any kind of failure.
When to use it – Simple recovery model is recommended for In a data warehousing scenario, where you mostly have bulk operations while data loading and in case of failure,
the data can be regenerated from the data source. You can also prefer using Simple recovery model in your development or test environment to ensure the growth of the transaction log
files are controlled..
How to set the simple recovery model for a database:-
ALTER DATABASE testdb SET RECOVERY SIMPLE
GO
3.Bulk-Logged:- In "Bulk-Logged" recovery model bulk operations are minimally logged . The bulk-logged recovery model protects against media failure and, for bulk operations, provides
the best performance and least log space usage.Since bulk operations are minimally logged, this increases the performance of the bulk operations. Also it does not allow the log to
grow unexpectedly when we do some costly operations like rebuild index, create index etc.
The following operations which get minimally logged when your database is in bulk logged recovery model:-
· Bulk import operations (BULK INSERT, and INSERT... SELECT).
· SELECT INTO operations
· WRITETEXT and UPDATETEXT statements when inserting or appending new data into the text, ntext, and image data type columns
· Partial updates to large value data types, using the .WRITE clause in the UPDATE statement when inserting or appending new data
· CREATE INDEX operations (including indexed views).
· ALTER INDEX REBUILD or DBCC DBREINDEX operations.
Reasons when you should choose Bulk-Logged recovery model:-
1. When your data is critical, but you do not want to log large bulk operations.
2. Bulk operations are done at different times versus normal processing.
3. When you dont want to restore the database to a certain point in time.
How to set the Bulk-Logged recovery model for a database:-
ALTER DATABASE testdb SET RECOVERY BULK_LOGGED
GO
References
MSDN: http://msdn.microsoft.com/en-us/library/ms189275.aspx
MSDN: http://msdn.microsoft.com/en-us/library/ms190203.aspx
Please share your ideas and opinions about this topic.
If you like this post, then please share with others.
Please subscribe on email for every updates on mail.
No comments:
Post a Comment