Shulou(Shulou.com)05/31 Report--

SQL Server database which recovery models, many novices are not very clear, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

1.Simple simple recovery model

The old name of Simple mode is "Checkpoint with truncate log". In fact, this name is more vivid. In Simple mode, SQL Server will automatically truncate log after each checkpoint or backup, that is, discard all inactive log records, leaving only a small amount of log needed for instance recovery that occurs automatically when the instance is started. The advantage of this is that the log file is very small, and there is no need for DBA to maintain and back up log, but the disadvantage is also obvious, that is, once an exception occurs in the database. "when you need to restore, you can only restore to the last backup at most, not to the most recent available state, because log is missing." Simple mode is mainly used for non-critical businesses, such as development libraries and test libraries, but most of the SQL Server (even production libraries) of State Street use Simple mode, because the SQL Server here is mostly used for non-critical businesses (Oracle and DB2 are mostly used in critical databases) and can tolerate data loss of less than 1 day (our job backs up the entire database regularly every day).

If you need to compress the database log (Shrink statement), the compression ratio is the highest after switching the database mode to the simple recovery model. If your database uses log compression under the full recovery model or mass log recovery model, the compressed log size will not be ideal.

2.Full full recovery model

Contrary to Simple mode, the old name of Full mode is "Checkpoint without truncate log", that is, SQL Server does not actively truncate log, and only after backing up log can you truncate log, otherwise the log file will keep growing until the hard disk is exploded, so you need to deploy a job regular backup log. The advantage of Full is that it can do point-in-time recovery to ensure that the data is not lost to the maximum extent. It is generally used in critical business environment. The disadvantage is that DBA needs to maintain log and increase staff costs (in fact, it is just more work of backing up log on a regular basis).

3.Bulk-logged bulk log recovery

Bulk-logged mode is similar to full mode, except that it produces as few log as possible for the following Bulk operations: 1) Bulk load operations (bcp and BULK INSERT). 2) SELECT INTO. 3) Create/drop/rebuild index is well known that bulk operations usually produce a large amount of log, which has a great impact on the performance of SQL Server. The role of bulk-logged mode is to reduce this performance impact and prevent the excessive growth of log files, but its problem is that point-in-time cannot be restored to include bulk-logged record for this period of time. The best practice for Bulk-logged mode is to switch to bulk-logged before doing the bulk operation and back to full mode as soon as the bulk operation is over.

Here are some additions that are easier to understand

SQL Server 2008 supports three recovery models, namely, the simple recovery model, the full recovery model, and the bulk-logged recovery model.

1. Simple recovery: no log backup. Automatically reclaim log space to reduce space requirements, virtually eliminating the need to manage transaction log space. Changes made after the latest backup are not protected. In the event of a disaster, these changes must be redone. You can only restore to the end of the backup.

2. Full recovery: log backup is required. Missing or corrupted data files do not result in loss of work. You can revert to any point in time (for example, before the application or user error).

3. Bulk log recovery: log backup is required. Is an add-on to the full recovery model that allows high-performance bulk copy operations. Reduce log space usage by recording most bulk operations in a minimal way. If a log corruption or bulk logging operation occurs after the latest log backup, you must redo the changes you have made since the last backup. Otherwise, no jobs will be lost. You can restore to the end of any backup. Point-in-time recovery is not supported.

"where, use the full recovery model if any of the following requirements are met: you must be able to recover all data." The database contains multiple filegroups, and you want to restore read / write secondary filegroups (and optionally read-only filegroups) segment by segment.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.