{"id":70,"date":"2018-05-26T11:57:57","date_gmt":"2018-05-26T11:57:57","guid":{"rendered":"http:\/\/dbtut.com\/?p=70"},"modified":"2020-10-20T20:19:06","modified_gmt":"2020-10-20T20:19:06","slug":"database-recovery-mode-in-sql-server","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2018\/05\/26\/database-recovery-mode-in-sql-server\/","title":{"rendered":"Database Recovery Mode in SQL Server"},"content":{"rendered":"

This article contains information about database recovery mode in SQL Server. You will find many information about FULL,SIMPLE and BULK LOGGED Recovery models and differences between these recovery models.<\/p>\r\n

What is Database Recovery Mode in SQL Server?<\/h2>\r\n

Database recovery mode in sql server is a database property that controls how transactions are logged.<\/p>\r\n

Depending on the Recovery Model, you may or may not use some high availability options, you may or may not use some backup types.<\/p>\r\n

You may ask yourself “What are the recovery models available in SQL?”<\/p>\r\n

Here is your answer; \ud83d\ude42<\/p>\r\n

Database Recovery Models in SQL Server<\/h2>\r\n

There are 3 types of recovery models:<\/p>\r\n

    \r\n
  1. Full Recovery Model<\/li>\r\n
  2. Simple Recovery Model<\/li>\r\n
  3. Bulk Logged Recovery Model<\/li>\r\n<\/ol>\r\n

    Full Recovery Model in SQL Server<\/h3>\r\n

    Full Recovery Model is the model in which all transactions are logged.<\/p>\r\n

    If you do not tolerate data loss, you should definitely use this Recovery Model.<\/p>\r\n

    It must be used in production environments. You can backup Transaction Log and restore the database to the most recent time.<\/p>\r\n

    For example, you create a full backup on Friday night, and differential backup on Wednesday night, and Log Backup every 2 hours.<\/p>\r\n

    Supporse that there was a problem at 08:10 in the morning on Thursday.<\/p>\r\n

    Developers want you to return to the last moment you can return before 08:10.<\/p>\r\n

    You need to restore Full Backup first, then the last Differential Backup, then all Log Backups after the last Differential Backup until Thursday 08:00.<\/p>\r\n

    This way you can go back to the last moment. Actually you will lose ten minutes.<\/p>\r\n

    If you are using Full Recovery Model<\/strong>, you should definitely backup Transaction Log.<\/p>\r\n

    If you do not backup Transaction Log, all processes are logged in the Transaction Log file and SQL Server will not truncate Transaction Log automatically. That’s why your disk will fill up after a while.<\/p>\r\n

    When you backup Transaction Log, excessive growth of the Transacation Log will be prevented because the backed transactions are truncated.<\/span><\/p>\r\n

    The truncate of the Transaction Log may have been frightening, but t<\/span>his truncate operation is not a concern. <\/span>On the contrary, it is a mandatory operation. <\/span>This data is available in your log backup. <\/span>By using these Log Backup files, you can return to any time as I mentioned above.<\/span><\/p>\r\n

    How often you will backup your Transation Log is related to your organization’s policy.<\/span><\/p>\r\n

    SQL Server Transaction Log Backup Interval<\/h3>\r\n

    Ask the below question to yourself to decide transaction log backup interval:<\/span><\/p>\r\n

    “If there is a problem with the database and I need to restore the database from the backup, how many minutes\/hours\/days of loss is important to me?<\/strong>“<\/em><\/p>\r\n

    Simple Recovery Model in SQL Server<\/h3>\r\n

    All transactions are logged in this recovery model, but these transactions are automatically deleted when Checkpoint is executed on SQL Server, and the transaction log file usually does not grow.<\/p>\r\n

    I would recommend reading the article named “What is Database Checkpoint<\/a>“.<\/p>\r\n

    I use “usually” expression, because sometimes a single transaction can fill the transaction log file. Warn application developers in such a cases. They must not execute such a large queries in the database.<\/p>\r\n

    If such a situation occurs in your system, you can shrink the Transaction Log file after the transaction is finished.<\/p>\r\n

    You may want to read the article titled “How To Shrink SQL Server Transaction Log<\/a>“.<\/p>\r\n

    Full,Differential and Log Backup in Simple Recovery Model<\/h3>\r\n

    You can create differential backup<\/strong> in Simple Recovery Model, but you can not create Log Backup <\/strong>in simple recovery model. If you try it, you will receive the below error.<\/p>\r\n

    Msg 4208, Level 16, State 1, Line 1 <\/em><\/span><\/p>\r\n

    The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE. <\/em><\/span><\/p>\r\n

    Msg 3013, Level 16, State 1, Line 1 <\/em><\/span><\/p>\r\n

    BACKUP LOG is terminating abnormally.<\/em><\/span><\/p>\r\n

    If we encounter a situation like the one in the example above, we can not return to 08:00 am on Thursday b<\/span>ecause the simple recovery model does not support log backup. <\/span><\/p>\r\n

    We can return <\/span>only<\/span> Wednesday<\/span> night<\/span>, which is the last differential backup date.<\/span><\/p>\r\n

    I recommend you to use simple recovery model in test and development environments. Of course if your test or development environment is important for you, you can use Full Recovery Model, and backup Transaction Log.<\/p>\r\n

    Simple recovery model does not support Always ON, Database Mirroring and Log Shipping.<\/p>\r\n

    Simple recovery model transaction log full<\/h3>\r\n

    Yes, your transaction log file can be full even if you use simple recovery model. Normally, in the simple recovery model, every transaction is truncated as soon as it is completed. But if the transaction is too large, it can make the transaction log full before it finishes.<\/p>\r\n

    What is difference between simple and full recovery model?<\/h3>\r\n