Archive for July, 2011

Internals of database-snapshot

July 31, 2011 Leave a comment


Check out our new blog post on Internals of Database Snapshot. This blog is the joint effort of Suhas and me.


Categories: Database Snapshot

Reverting Database using Database Snapshot

July 16, 2011 Leave a comment


It is always on ease having a backup and recovery strategies in place but few times it also important to recover a database to appoint which doesn’t require a full database restore or in shot no hard efforts. While working with one of my previous organization I was asked to do something which actually made me interested in using Database snapshot feature.


Scenario: A New modules was designed and developed for finance. Once the Module will get deployed in production excel data files need to be uploaded. At the backed few SP will execute which will do processing of data and finance related data will be updated /flag (inactive).


I have been asked to design a recovery strategy for the Database which recovers the database before the time of deployment and without downtime. I thought on all recovery models and at last decided to user database snapshot.

Database snapshot give me a flexibility to revert back to the time when the snapshot was created. As the snapshot database is read-only we can rum comparison between OLPT database and database snapshot.


Combing back to the original question, let’s say Deployment has to start at 1:00 AM, will stop the customer /user access to the application and database so that no transaction should take place. Once the application and database is inaccessible to the customer run select * from sys.sysprocesses to check what all open transaction are in place other then background processes.

Create the database Snapshot.



( NAME = SPri1_dat,

( NAME = SPri2_dat,

( NAME = SGrp1Fi1_dat,

( NAME = SGrp1Fi2_dat,

( NAME = SGrp2Fi1_dat,

( NAME = SGrp2Fi2_dat,





In case you think after the deployment there is some issue with the module or data and you need to revert back. Restore the snapshot which will revert the changes to the database till the point of snapshot.


Here is an example

Revert the database to a database snapshot (No connection should exists to the source database to apply the snapshot.)




Now to verify that database is reverted successfully, check the records







Categories: Database Snapshot

Perform full database backup without breaking the backup chain

July 9, 2011 Leave a comment

In today’s time every organization retains a daily full database backup copy on tape. A DBA also make sure that he has full database backup and transactional log backup files to perform a point in time / Full recovery. When there is a major Change Deployment happens on the database a full database backup is mostly done. While all this is in progress DBA forgets what happens If the full backup done in middle of transactional log backup’s and the same is lost thinking it is of no use ,as Transactional backup job is running and performing the Log Backups.

When it comes on restoring the database backup and T-Logs DBA get surprised to see the below error

“System.Data.SqlClient.SqlError: The log in this backup set terminates at LSNXXXXXXXXXXXXXXXXXXXXX, which is too early to apply to the database. A more recent log backup that includes LSN XXXXXXXXXXXXXX can be restored. (Microsoft.SqlServer.Smo)”

On checking the backup tables DBA get more surprised that a full backup of the database is done and the same doesn’t exists to the location. Now only question is; how we will do point in time/Full recovery of the database .Other DBA will answer to this; we have used DB Mirroring rather doing Full, Diff and transactional Log Backup.

What will a DBA do in case an Update statement is executed as” Human Error” on a table which holds the most important data for the organization? How will this be recovered by Database Mirroring? Well it will not.

I will suggest DBA’s the following Strategies which I mostly follow.

  1. Revisit the Organization Backup Policy and check how and when the backup is scheduled and who hold the responsibility.
    1. If the Primary Owner for the Backup is Tape Backup Admin (Datacenter Backup Admin), check how the full backup is done.
      1. File Level Backup (Data File and Log File) —-Cannot Restore the Backup in No Recovery Mode; Point in time / Full recovery is not possible.
      2. SQL Native Backup (SQL Server Format)
      3. LiteSpeed Format
      4. DPM (Data Protection Manager)
      5. Alerts on Failure and Success of Backup
      6. Action Plan on Failure.
    2. If the Primary owner is DBA
      1. What is the backup strategy (Once Full à5 Min Log Backup àEvery 5 hour’s Differential Backupà5 min Log Backup) .Strategy is defined as per Organization business and on Database importance.
      2. Where is the backup done (Disk /Tape)
      3. What is the format of the backup (Compressed /LiteSpeed format)
      4. Alerts on Failure and Success of Backup
      5. Action Plan on Failure.

If you follow any of the above 2 steps always the secondary owner to perform Full Database backup if needed using COPY_ONLY HINT .

A 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. For this purpose, copy-only backups were introduced SQL Server 2005. The types of copy-only backups are as follows:

  • Copy-only full backups (all recovery models)A copy-only full backup cannot serve as a differential base or differential backup and does not affect the differential base.
  • Copy-only log backups (full recovery model and bulk-logged recovery model only)A copy-only log backup preserves the existing log archive point and, therefore, does not affect the sequencing of regular log backups. Copy-only log backups are typically unnecessary. Instead, you can create another routine, current log backup (using WITH NORECOVERY), and then use that backup together with all other previous log backups that are required for the restore sequence. However, a copy-only log backup can be created for performing an online restore


In the below example i have done a Database Full Backup as part of backup strategy along with Transactional Log Backup and in the middle done a Database Full Backup again using COPY_ONLY HINT to provide the same to develpment team.


Restore the Database Full backup and TLog Backup part of Backup strategy.

Categories: Uncategorized

Database (Full, Diff & Transaction Log) Backup List

July 4, 2011 Leave a comment

I thought of writing about the subject after visiting a customer. It is really important to have the list of fullback and transaction log back sequentially performed. When it comes to Recovery I have seen most of the DBA running queries on msdb database and struggling to get the list of Backups and sequential Transaction Log Backup.

I have written T-SQL Code and designed a Report which helped me and other DBA to quickly check the backup and transaction log backups performed on a specific Database. This report was designed for one of our mission critical Database Only. Users are allowed to change the code according to their environment.

Snapshot of the Report is as below.



SELECT bs.backup_set_id,bs.first_lsn,bs.last_lsn,bs.checkpoint_lsn,


                type= CASE bs.type
‘FULL Backup’
‘DIFF Backup’
‘LOG Backup’

                    FROM msdb.dbo.backupset bs INNER
JOIN msdb.dbo.backupmediafamily bmf ON


            and bs.database_name=‘DatabaseName’ and bs.backup_finish_date between

BY bs.backup_set_id asc






Categories: Uncategorized