Wednesday, June 1, 2011

SQL: full recovery model : full and differential backups

I have decided to put a small note regarding content database backup.

 In my life everything I do at work is related to SharePoint, that's why I have pointed out the real case for SharePoint developers; however if you are not SharePoint developer, you will get the same benefits from reading this post.
 I wanted to specify that my task is to backup  the prod content SharePoint database and to restore it on my local machine for test\developing purpose. Here is details how you can perform SharePoint content database backup\restore and why you want to do it.

 It's highly probable that you have a full recover model for the content database on Prod. That means it's highly probable that  SQL admins fortify the SQL backup strategy with  differential backups.

A differential backup backs up only modified extents since the last complete backup. The most recent differential backup contains all changes from all previous differential backups performed since the most recent complete database backup
. Differential backups have several limitations including the following:
  • They do not provide point-in-time restore capabilities
  • They may only be restored after a complete database backup is restored
  • They may not be performed on the master database
These yellow highlights are pointing out at the fact that " IF YOU DO A FULL BACKUP WHILE DIFFERENTIAL BACKUPS ARE CONFIGURED FOR THE DATABASE - YOU ARE DISTURBING THE BACKUP SEQUENCE"
   To eliminate such effect on SQL routine, I highly recommend you to set option "COPY-ONLY" for the backup
 
Copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. Usually, taking a backup changes the database and affects how later backups are restored. However, occasionally, it is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database.