SQL Server Databases, Backup and Restore

Very recently one of my customers asked me for a 'full disaster recovery guideline', using the backup/restore method. That's what this is. Here I provide a very basic Backup/Restore strategy used for disaster recovery. There are two primary concepts;  Backing up the data, and restoring the data.  The backup(s) should be written remote from the targeted SQL instance, or copied off of the server very quickly after the backup is performed.  This is very important.  If the backup is not moved off the targeted server, this becomes a very costly single point of failure.  The server goes down and doesn’t come back.  You cannot recover business operations because you cannot access your backup files.  No good.

To use the Backup/Restore Disaster Recovery Plan (DRP) option, you must have one of these available:

Cold Backup Server – This means you have a spare physical server available to be used as your recovery server, should your production server fail. Generally speaking, this server does not have SQL installed, or any databases in place.  A Cold Backup Server requires time to install and configure SQL Server, enable alerts and maintenance routines, restore the user databases, and redirect any applications to the new server, before resuming operation.  This method is timely, and it allows for the possibility of data loss if the last transaction logs are not recovered from the failed server, and you cannot restore to the point in time of the failure. 

Warm Backup Server – The warm server is preinstalled and preconfigured.  This saves a lot of time and allows you to resume operation much more quickly than the Cold Backup Server.  The applications still need to be redirected, and you need to refresh/restore the data with the most current backup, but your recovery time is greatly reduced.  The risk of data loss is also here, though, if you are unable to recover the most current logs from the production server, and you cannot restore to the point in time of the failure.

Cold or Warm, your method and ability to recover is based solely on the database Recovery Model. Business critical databases are commonly built with FULL Recovery Model.  This would require a Differential backup strategy using the full, differential and log components as referenced here:  

             FULL Backup    Contains all data and enough log to roll forward.  Typically the largest backup.            

        Differential       Between full backup and log, contains all extents that have changed since the last backup. 

       Transaction Log    Contains every transaction which does not exist in the last transaction log backup.  

   
These are the different point in time recovery options for databases in FULL recovery model:
    -        Full Restore                    

           -     Full Restore + Log(s)

           -        Full Restore + Diff           

           -     Full Restore + Diff + Log(s)

Example Backup Strategy ~

                                              -          Full backup created each Sunday
         -          Differential backup each day at 1AM (much smaller/quicker than Sunday full)
             -          Transaction log backups every hour

Example Failure/Recovery ~ Accident happens on Friday at 7:02AM.  To recover, you must

                                         -          Restore Sunday’s full backup
        -          Restore the differential taken on Friday at 1AM
        -          Restore all 7 transaction log dumps taken on Friday, from 1AM until 7AM

These are the steps necessary to achieve the above listed failure/recovery scenario:

  

           1.   HEADERONLY and FILELIST return details from the backup device(s), to use in your statements:

     RESTORE HEADERONLY FROM DISK = 'C:\YourDB.bak'

 RESTORE FILELISTONLY FROM DISK = 'C:\YourDB.bak' 

    2.   RESTORE DATABASE BACKUP

 RESTORE DATABASE YourDB

 FROM DISK = 'C:\MSSQL\Backup\YourDB_FULL_20140806_080343.BAK'

 WITH 

   MOVE 'YourDB_Primary' TO 'D:\MSSQL\Data\YourDB_Primary.mdf',

   MOVE 'YourDB_Data' TO 'D:\MSSQL\Data\YourDB_Data.mdf',

   MOVE 'YourDB_Log' TO 'D:\MSSQL\Log\YourDB_Log.ldf',

   NORECOVERY; 

           3.    RESTORE DIFFERENTIAL BACKUP – First use FILELISTONLY to identify the DIFF backup filename: 

 RESTORE FILELISTONLY FROM DISK = 'C:\MSSQL\BACKUP\YourDB_DIFF.bak'   

   4.  PERFORM THE DIFFERENTIAL RESTORE:

   RESTORE DATABASE YourDB

   FROM DISK = 'C:\MSSQL\Backup\YourDB_DIFF.BAK'

   WITH

      MOVE 'YourDB_Primary' TO 'D:\MSSQL\Data\YourDB_Primary.mdf',      

      MOVE 'YourDB_Data' TO 'D:\MSSQL\Data\YourDB_Data.mdf',

      MOVE 'YourDB_Log' TO 'D:\MSSQL\Log\YourDB_Log.ldf',

      NORECOVERY; 

   5.   RESTORE TRANSACTION LOG – Run for all transaction log backups since the differential was created:

     RESTORE LOG YourDB

    FROM DISK = 'C:\MSSQL\Backup\YourDB_LOG_20140806_080343.BAK'

    WITH NORECOVERY;

    **Run this statement 7 times -- once for each log backup from 1AM to 7AM.

    6.   RESTORE WITH RECOVERY – Database is now accessible, and no more backup files can be applied:

     RESTORE DATABASE YourDB WITH RECOVERY

       NOTES: 

• Steps #2 and #4 in the recovery include ‘MOVE’.  This is necessary if you restore your database files to a new location, such as the cold or warm SQL Server. 
• If you are performing the RESTORE in the same location (same server, db, directory path), the exact same statements will work.  You just need to remove the ‘MOVE’, like is demonstrated in step #5.
• The SIMPLE recovery model databases only allow recovery to the most recent full backup, not to a point in time. 
• The scenario above is JUST an example.  I encourage you to do fulls and diffs daily if you can.  This will reduce the steps necessary for full recovery.
• How often do you create the backups?  As regularly, and as frequently as you can.
• You cannot restore a LOG backup without restoring the FULL or DIFFERENTIAL first.
• Be sure you are taking regular backups and storing them OFF of the SQL Server somewhere, as a 2^nd copy. 

Remember, the recovery above is just an example.  How much does your data change, and how frequently?  You need to become very familiar with your data to determine the best backup strategy AND schedule for your organization.

Let me know if you have any questions, or if I can help you model a similar routine for your environment.