SQL database recovery model (log)

————————————————– ——————–
SQL database recovery model
————————————————– ——————–
1) the full recovery model
—————–
(1) backup database data files and log files to be backed up (2) restore to restore the database using the database backup copy of the data file and log information.
(3) can restore all the data, and the database can be restored to any given moment.
(4) To ensure the realization of a point-in-time recovery, the database * for the complete recorded in the log so that the log space, also have an impact on performance.
——————
(2) the bulk-logged recovery model
——————
(1) backup database data files and log files to be backed up (2) restore to restore the database using the database backup copy of the data file and log information.
(3) The log does not record * details (such as select into, create index, etc.), and record only * for the eventual outcome of log space.
(4) only supports to restore the database to the moment of the transaction log backup, does not support a point-in-time recovery, and may produce a loss of data.
——————-
(3) simple recovery model
——————-
(1) backup backs up only the data files, restore the backup data file to restore the database.
(2) can only restore data to the data file backup time may produce up to data loss.
(3) is not suitable for production systems and large-scale operating environment selection.
—————————————–
alter database d1 set recovery simple – set the database recovery model
alter database d1 set recovery bulk_logged
alter database d1 set recovery full
————————————————– ——————–

In addition to the system database in SQL Server, you create a database has three recovery model to choose: Simple (simple), full (complete), the bulk-logged (batch log). The following statement shows all online database recovery model:
SELECT name, (SELECT DATABASEPROPERTYEX (name, ‘RECOVERY’)) RecoveryModel FROM master .. sysdatabases ORDER BY name

SQL Server 2005 and above can also use the following statement to see:
SELECT name, recovery_model_desc FROM master.sys.databases ORDER BY name

If you want to change the recovery model of the database, you can use the following SQL statement:

Simple Recovery Model: ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE
Full recovery model: ALTER DATABASE AdventureWorks SET RECOVERY FULL
Bulk-Logged Recovery model: ALTER DATABASE AdventureWorks SET RECOVERY BULK_LOGGED

In reality, you should choose which recovery model? The answer lies in how much data you can afford to lose.

In addition to the system database in SQL Server, you create a database has three recovery model to choose: Simple (simple), full (complete), the bulk-logged (batch log). The following statement shows all online database recovery model:
SELECT name, (SELECT DATABASEPROPERTYEX (name, ‘RECOVERY’)) RecoveryModel FROM master .. sysdatabases ORDER BY name

SQL Server 2005 and above can also use the following statement to see:
SELECT name, recovery_model_desc FROM master.sys.databases ORDER BY name

If you want to change the recovery model of the database, you can use the following SQL statement:

Simple Recovery Model: ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE
Full recovery model: ALTER DATABASE AdventureWorks SET RECOVERY FULL
Bulk-Logged Recovery model: ALTER DATABASE AdventureWorks SET RECOVERY BULK_LOGGED

In reality, you should choose which recovery model? The answer lies in how much data you can afford to lose.

That being the case, that people why use bulk-logged recovery model? One of the main reasons is the performance. Let us select into to cases from a result set to create a large table. If you are using the full backup model, this table each inserted data is recorded, the transaction log can consume a lot of disk space. If you are using the bulk-logged recovery model, then only modify the details of the data page will be recorded in order to achieve the best performance. As we just described, the benefits of using the transaction log can be restored to a specified recovery point, but it will greatly affect the performance.

Following several operations will minimize the log operation:
· Batch import operation (for example: INSERT … SELECT * FROM OPENROWSET (BULK.), and BULK INSERT)
· Select into operation
· Use the update to update the part of the data value of the data type. Write the statement insert or append data, pay attention to minimize the log will not be recorded when the updated data exists
· If the database recovery model is set to batch recovery or simple recovery index DDL operations will minimize the generation of log, whether this operation is online or offline is executed
· Delete the index new heap