Select the SQL Server recovery model to ensure proper backup

Problem

One of the first things to do when managing SQL Server is to establish an appropriate backup plan to minimize the loss of any data in a failed event. After setting up a backup plan, of course, you have to do some database configuration, which is established to make sure that you can back up the database correctly. In this article we will look at the different recovery models provided by SQL Server and how to choose a recovery model for your database.

Expert answers

For SQL Server 2000 and 2005,microsoft, you provide three different recovery models for your database. On your server, each database can be built differently, and you can change the recovery model as needed, so this option is not permanent.

These three recovery models are:

Simple (Easy)

The simple recovery model, as its name suggests, provides you with a simple backup that can be used to replace your entire database in the event of failure or if you need to restore your database to another server. With this recovery model you have the ability to complete a full backup (the entire replication) or a different backup (any changes made since the last full backup). Using this recovery model you are exposed to any failures since the last backup. Here's why you can choose this recovery model:

Your data is unimportant or easy to recreate.

This database is for testing or development only.

The data is static and does not change.

It is okay to lose any or all of the transactions after the last backup.

The data is acquired and can be easily recreated.

The type of backup you can run:

Full backup

Differential backup

File and/or filegroup backups

Partial backup

Copy-only (copy only) backup

Bulk_Logged (Batch log)

Batch log recovery is as shown in its name. With this model, a significant portion of the batch operations such as bulk INSERT, CREATE INDEX, SELECT into, and so on are not fully logged into the transaction log, thus not occupying as much space as the transaction log. The advantage of using this recovery model is that if you do batch operations your transaction log will not become that large, and you can also do point-in-time recovery, as long as your last transaction log backup does not include the batch operation mentioned above. If the batch operation is not run, then the recovery model is the same as the full recovery model. One thing to note is that if you use this recovery model, you will also need to make a transaction log backup, or your database transaction log is constantly expanding. Here's why you might choose this recovery model:

The data is important, but you don't want to record a lot of batches in the log.

Batch operations are performed at different times from normal operations.

You still want to be able to recover to a certain point in time.

The types of backups you can run are:

Full backup

Differential backup

File and/or filegroup backups

Partial backup

Copy-only (copy only) backup

Transaction log Backups

Full (complete)