Change your Recovery Model
On occasion you may be tasked with switching your recovery model for a database. This task is very trivial and you may already know how to do this via Sql Server Enterprise Manager or even Management Studio. But you can do this via Tsql as well (as with all things sql server related)
The syntax is basically
ALTER DATABASE Northwinds SET RECOVERY SIMPLE
The recovery Models available are, Simple, BulkLogged and Full.
The Simple Recovery model is as you guessed the basic recovery model which pretty much allows you to recover your database from a full backup. The simple recovery model is ideal for test and development databases where data loss is not generally a concern. You may wish never to use this model in a production environment unless the business is willing to loose up all data changes since the last Full or Differential backup. In this model you do not have to maintain the Transaction log as nothing is generally written to it.
The BulkLogged Recovery model will log every event except those that are Bulk transactons. This has almost the same recoverability as a Full Recovery model. With this recovery model you can do full, differential and log backups, you are also capable of restoring up to the minute so long as your last change was not a bulk logged operation, in which case those changes will remain lost. You will need to schedule a regular backup for your transaction log for those operations that are not Bulk transactions.
The Full Recovery model offers the highest amount of protection against data loss. This model allows you to recover any piece of data up to the minute (prividing you have the last backup). You can do full, differential and log backups. You can restore any of the backups for returning to your point in time. Such as a FULL backup followed by a Differential and finally your set of Log backups until the point in time when the failure occurred. In this recovery model you will want to schedule a regular log backup for your transaction, otherwise your database log files will grow and grow.
The syntax is basically
ALTER DATABASE Northwinds SET RECOVERY SIMPLE
The recovery Models available are, Simple, BulkLogged and Full.
The Simple Recovery model is as you guessed the basic recovery model which pretty much allows you to recover your database from a full backup. The simple recovery model is ideal for test and development databases where data loss is not generally a concern. You may wish never to use this model in a production environment unless the business is willing to loose up all data changes since the last Full or Differential backup. In this model you do not have to maintain the Transaction log as nothing is generally written to it.
The BulkLogged Recovery model will log every event except those that are Bulk transactons. This has almost the same recoverability as a Full Recovery model. With this recovery model you can do full, differential and log backups, you are also capable of restoring up to the minute so long as your last change was not a bulk logged operation, in which case those changes will remain lost. You will need to schedule a regular backup for your transaction log for those operations that are not Bulk transactions.
The Full Recovery model offers the highest amount of protection against data loss. This model allows you to recover any piece of data up to the minute (prividing you have the last backup). You can do full, differential and log backups. You can restore any of the backups for returning to your point in time. Such as a FULL backup followed by a Differential and finally your set of Log backups until the point in time when the failure occurred. In this recovery model you will want to schedule a regular log backup for your transaction, otherwise your database log files will grow and grow.
Comments
Post a Comment