Nothing Special   »   [go: up one dir, main page]
Scribd Logo
Upload

Welcome to Scribd!

  • 154 views

    Recovery Models-SQL

    Uploaded by

    rajakumari
    The document discusses different recovery models in SQL Server that control transaction log maintenance and database recovery options. The three recovery models are: full, allowing complete point-in-time recovery but requiring regular transaction log backups; simple, not requiring log backups but preventing point-in-time recovery; and bulk-logged, allowing for high-performance bulk operations while requiring log backups to prevent data loss. The recovery model and regular backups are important to recover databases to a consistent state after a failure.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    Recovery Models-SQL

    Uploaded by

    rajakumari
    154 views2 pages
    The document discusses different recovery models in SQL Server that control transaction log maintenance and database recovery options. The three recovery models are: full, allowing complete point-in-time recovery but requiring regular transaction log backups; simple, not requiring log backups but preventing point-in-time recovery; and bulk-logged, allowing for high-performance bulk operations while requiring log backups to prevent data loss. The recovery model and regular backups are important to recover databases to a consistent state after a failure.

    Original Description:

    Recovery Models-SQL

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document discusses different recovery models in SQL Server that control transaction log maintenance and database recovery options. The three recovery models are: full, allowing complete point-in-time recovery but requiring regular transaction log backups; simple, not requiring log backups but preventing point-in-time recovery; and bulk-logged, allowing for high-performance bulk operations while requiring log backups to prevent data loss. The recovery model and regular backups are important to recover databases to a consistent state after a failure.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    154 views2 pages

    Recovery Models-SQL

    Uploaded by

    rajakumari
    The document discusses different recovery models in SQL Server that control transaction log maintenance and database recovery options. The three recovery models are: full, allowing complete point-in-time recovery but requiring regular transaction log backups; simple, not requiring log backups but preventing point-in-time recovery; and bulk-logged, allowing for high-performance bulk operations while requiring log backups to prevent data loss. The recovery model and regular backups are important to recover databases to a consistent state after a failure.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    You are on page 1of 2

    RECOVERY MODELS:

     Recovery model determines what level we will be able to recover our database and recently
    entered data in case of system failure.
     In order to create the correct backups, we need to set the proper recovery model for each
    database.
     Basically designed to control the transaction log maintenance.
     Transaction Logging - It is the internal mechanism of SQL Server that keeps logging all
    transactions and Database modifications that are made by each transaction as a string of log
    records in a serial sequence as they are created. This is important to bring the database back to
    a consistent state if there is a system failure.
     What log files do?
     SQL Server Database has atleast a mdf data file and a ldf log file.
     Mdf file has all database objects and data such as tables, stored procedures and user
    information.
     Ldf file has all logs in database.
     Note: we never only count on log files to recover our database . For.eg., to recover
    database to some time-point through ldf log file and the previous full backup.
     Each database can have only one Recovery model but each of our database can have different
    Recovery models. The only exception is that TempDB database has to use Simple Recovery
    Model.
     Types: Full, Simple, Bulk-Logged.

    FULL RECOVERY MODEL:

     Most complete recovery model.


     Here, all the transactions data are retained within the transaction log file until the log file is
    backed up.
     It allows to take full, differential and transactional log backup.
     Point-in-time recovery.
     If we set the Full Recovery Model, We need to issue transactional log backup otherwise the
    transactional log will grow forever. ( for Full RM scenario, the data is not overwritten in the
    transactional log ) (explanation: All transactions that are issued against SQL server first get
    entered into the transaction log and then the data is written to the appropriate data file)
     ADVANTAGES- data cannot be lost, point -in-time recovery
     DISADVANTAGES-if the log is damaged, changes since the most recent log backup must be done.

    SIMPLE RECOVERY MODEL:

     Basic Recovery Model.


     Here, Every transaction is still written to the transaction log, but once the transaction is
    complete, the data has been written to the data file, the space that was used in the transaction
    log file is now resusable by other transactions. ( SQL Server truncates the transaction log each
    time the database reaches a transaction checkpoint , leaving no entries for disaster recovery
    purposes.) Note: Checkpoints – flush dirty data pages from buffer cache of current database to
    disc.
     Since the data is overwritten, the transaction log will not grow forever unlike in Full Recovery
    Model.
     It allows to take full and differential backup. Transactional log backup is not possible here.
     Advantages- Reclaims log space to keep space requirements small.
    Permits bulk logged operations
     DISADVANTAGES- Changes since the most full or differential backup must be redone in case of
    failure.

    BULK LOGGED RECOVERY MODEL:

     Similar to Full RM except that it handles Bulk Operations(BULK INSERT, INDEX CREATION, SELECT
    INTO).
     Bulk logged model records these operations in transaction log using a technical known as
    Minimal Logging.
     ADVANTAGES: permits high-performance bulk copy operations, minimal log space is used by
    bulk operations.
     DISADVANTAGES : If the log is damaged, Bulk operations occurred since the most recent log
    backup must be redone.

    TYPES OF BACKUP:

    FULL BACKUP :

     Creates complete backup of our database as well as part of transaction log

    TRANSACTIONAL LOG BACKUP:

     Backups the active part of the transactional log

    DIFFERENTIAL BACKUP:

     Backups any extent that has changed since last full backup.

    You might also like