RECOVERY MODEL ON SQL-SERVER

-
RECOVERY MODEL ON SQL-SERVER
Overview
One of the first things that needs to be set in order to create the correct backups is to set the proper recovery model for each database.  The recovery model basically tells SQL Server what data to keep in the transaction log file and for how long.  Based on the recovery model that is selected, this will also determine what types of backups you can perform and also what types of database restores can be performed.
Explanation
The three types of recovery models that you can choose from are:
Each database can have only one recovery model, but each of your databases can use a different recovery model, so depending on the processing and the backup needs you can select the appropriate recovery model per database.  The only exception to this is the TempDB database which has to use the "Simple" recovery model.
Also, the database recovery model can be changed at any time, but this will impact your backup chain, so it is a good practice to issue a full backup after you change your recovery model.
The recovery model can be changed by either using T-SQL or SQL Server Management Studio.  Following are examples on how to do this.
Using T-SQL to change to the "Full" recovery for the AdventureWorks database.
ALTER DATABASE AdventureWorks SET RECOVERY FULL
GO
Using the SSMS to change the recovery model for the AdventureWorks database.
**********************************************************************************************
FULL- RECOVERY MODEL
Overview
The "Full" recovery model tells SQL Server to keep all transaction data in the transaction log until either a transaction log backup occurs or the transaction log is truncated. The way this works is that 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.  This allows SQL Server to rollback each step of the process in case there was an error or the transaction was cancelled for some reason.  So when the database is set to the "Full" recovery model since all transactions have been saved you have the ability to do point in time recovery which means you can recover to a point right before a transaction occurred like an accidental deletion of all data from a table.
Explanation
The full recovery model is the most complete recovery model and allows you to recover all of your data to any point in time as long as all backup files are useable. With this model all operations are fully logged which means that you can recover your database to any point. In addition, if the database is set to the full recovery model you need to also issue transaction log backups otherwise your database transaction log will continue to grow forever.
Here are some reasons why you may choose this recovery model:
  • Data is critical and data can not be lost.
  • You always need the ability to do a point-in-time recovery.
  • You are using database mirroring
Type of backups you can run when the data is in the "Full" recovery model:
  • Complete backups
  • Differential backups
  • File and/or Filegroup backups
  • Partial backups
  • Copy-Only backups
  • Transaction log backups

SIMPLE RECOVERY MODEL



Overview



The "Simple" recovery model does what it implies, it gives you a simple backup that can be used to replace your entire database in the event of a failure or if you have the need to restore your database to another server.  With this recovery model you have the ability to do complete backups (an entire copy) or differential backups (any changes since the last complete backup).  With this recovery model you are exposed to any failures since the last backup completed.  



Explanation



The "Simple" recovery model is the most basic recovery model for SQL Server.  Every transaction is still written to the transaction log, but once the transaction is complete and the data has been written to the data file the space that was used in the transaction log file is now re-usable by new transactions.  Since this space is reused there is not the ability to do a point in time recovery, therefore the most recent restore point will either be the complete backup or the latest differential backup that was completed.  Also, since the space in the transaction log can be reused, the transaction log will not grow forever as was mentioned in the "Full" recovery model.



Here are some reasons why you may choose this recovery model:


    

  • Your data is not critical and can easily be recreated
    • 

  • The database is only used for test or development
    • 

  • Data is static and does not change
    • 

  • Losing any or all transactions since the last backup is not a problem
    • 

  • Data is derived and can easily be recreated
    • 




Type of backups you can run when the data is in the "Simple" recovery model:


    

  • Complete backups
    • 

  • Differential backups
    • 

  • File and/or Filegroup backups
    • 

  • Partial backups
    • 

  • Copy-Only backups
    • 




Set simple recovery model using T-SQL




ALTER DATABASE dbName SET RECOVERY recoveryOption
GO






Example: change AdventureWorks database to "Simple" recovery model




ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE
GO









BULK LOGGED RECOVERY MODEL



Overview



The "Bulk-logged" recovery model sort of does what it implies.  With this model there are certain bulk operations such as BULK INSERT, CREATE INDEX, SELECT INTO, etc... that are not fully logged in the transaction log and therefore do not take as much space in the transaction log. 



Explanation



The advantage of using the "Bulk-logged" recovery model is that your transaction logs will not get that large if you are doing bulk operations and it still allows you to do point in time recovery as long as your last transaction log backup does not include a bulk operation as mentioned above.  If no bulk operations are run this recovery model works the same as the Full recovery model.  One thing to note is that if you use this recovery model you also need to issue transaction log backups otherwise your database transaction log will continue to grow.



Here are some reasons why you may choose this recovery model:


    

  • Data is critical, but you do not want to log large bulk operations
    • 

  • Bulk operations are done at different times versus normal processing.
    • 

  • You still want to be able to recover to a point in time
    • 




Type of backups you can run when the data is in the "Bulk-logged" recovery model:


    

  • Complete backups
    • 

  • Differential backups
    • 

  • File and/or Filegroup backups
    • 

  • Partial backups
    • 

  • Copy-Only backups
    • 

  • Transaction log backups
    • 




Set bulk-logged recovery model using T-SQL




ALTER DATABASE dbName SET RECOVERY recoveryOption
GO






Example: change AdventureWorks database to "Bulk-logged" recovery model




ALTER DATABASE AdventureWorks SET RECOVERY BULK_LOGGED
GO












































Comments











Post a Comment






























Popular posts from this blog









Query for Tablespace usage with Autoextend






-















                                           Monitoring Tablespace Usage in Oracle       I often see questions in Oracle newsgroups and forums about monitoring space in tablespaces, and many of those questions are regarding Enterprise Manager 12c and the alerts it generates reporting the percentage of used space. Of course how you monitor free space depends on whether or not the datafiles are autoextensible. Let's look at several ways to monitor free and used space, starting with methods used in older releases.   In versions prior to Oracle 10 DBA_TABLESPACES, DBA_DATA_FILES and DBA_FREE_SPACE were used to generate such reports. One script, from MOS, uses these three views to compute the used space percentage:   SELECT     SUM(s.used_gb)/SUM(f.total_gb)*100 FROM dba_tablespaces t, (     SELECT tablespace_name,         SUM(NVL(bytes,0))/(1024*1024*1024) total_gb     FROM dba_data_files ...








Read more










How to configure multiple Oracle listeners






-















  How to configure multiple Oracle listeners      It happened to me few times during the stress tests that Oracle listener became a bottleneck and not be able to handle the required workload. It was resolved by creating  multiple  listeners which appeared to be a quick solution.  Here is a short step-by-step procedure to configure multiple Oracle listeners  on Solaris for standalone Oracle 9i and 10g environment.   1)  First of all, add additional NIC and cables. They can be on separate subnetwork or the same. In the later, make sure to set static route if needed.      2) Assume that we are going to configure two listeners,   LISTENER and LISTENER2   Modify listener.ora and tnsnames.ora as following:   Here is a sample of   listener.ora    LISTENER =   (DESCRIPTION_LIST =     (DESCRIPTION =       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.3)(PORT = 1521))     ) ...








Read more










sheel script - automatic tablespace addition in oracle 11g






-















        sheel script - automatic tablespace addition in oracle 11g        #!/bin/bash   a=`sqlplus -s / as sysdba <<END   set pagesize 0 feedback off verify off heading off echo off;   select tablespace_name from dba_segments group by tablespace_name having SUM(BYTES)/1024/1024>700;   END`   f=${#a}   c=0   for file in $a ; do   eval "var$c=$file";   c=$((c+1));   done   echo $var0   array1=$(df -kh | grep '/u0*'|awk '{ print $6}')   array2=$(df -kh | grep '/u0*'|awk '{ print $5}')   size=${#array1}   size1=${#array2}   if ["$size" -eq 4] && ["$size1" -eq 3] && ["$array2" -gt 85%] && ["$f" -eq 1]   then   d=${array1[0]:0:4}   sqlplus /nolog <<END   connect /as sysdba   alter tablespace $a add datafile '$d/$a01.dbf' size 10m autoextend on maxsize 28G;   END`   fi   if ["$size" -eq 9] && ["$size1" -eq 3] && ["$array2 -gt...








Read more