MSSQL Recovery Planning

The concept of database recovery is based on the D in the transactional-integrity ACID properties—transactional durability. Durability means that a transaction, once committed, regardless of hardware failure, must be persistent. SQL Server accomplishes transactional durability with a write-ahead transaction log. Every transaction is written to transaction log prior to being written to the data file. The transaction log ensures that every transaction can be recovered up to the very last moment before the server stopped. The strategy of a recovery plan should be based on the organization’s tolerance level, or pain level, for lost transactions. Recovery-plan tactics involve choosing among the various backup options, generating a backup schedule, and off-site storage.
-you need to know which recovery model the database is using, as by default, SQL Server databases are set to Full Recovery mode, which means they maintain sufficient logs to restore to any specific point in time. From SQL Server 2000 onwards Microsoft supports three different recovery models: SIMPLE Recovery Model, FULL Recovery Model and BULK_LOGGED Recovery Model.
• Simple: In this model the SQL server maintains only a minimal amount of information in the transaction log. The databases using the Simple Recovery Model may restore full or differential backups only. When a database is set to Simple Recovery, it means log files are not kept permanently, so when a TSQL statement executes, changes are written to the data and log files, being truncated. This recovery model is also suited for databases which mostly contain read only data or on those databases where changes happen infrequently.
• Full: Every SQL Server database has a Transaction Log and every Insert, Update and Delete transaction that occurs is placed in the transaction log. Changing the Recovery Model to FULL tells SQL Server to keep all committed transactions in the Transaction Log until there
is a backup. Once a backup has occurred, SQL Server will remove all committed transactions that have been added to database from the log.
• Bulk Logged: With this Model the bulk operations such as CREATE INDEX, SELECT INTO, BULK INSERT, BCP etc are minimally logged within the transaction log thereby improving performance. The log backup and truncation methods that apply here are the same as in full
Recovery mode so nothing really changes. We have to switch to Bulk_Logged mode when we need to do large bulk load operations. Recovery can only stop at the end of a transaction log. This model is typically used if organizations need to run large bulk operations that degrade system performance and do not require point-in-time recovery.
• Use this query to get list of database and their recovery model – SELECT name, recovery_model_desc FROM sys.databases

Leave a Reply

Your email address will not be published. Required fields are marked *