SQL Server Database Snapshots – Things to remember!

In this blog post, let’s see what SQL Server database Snapshots are and how they can be created and how to revert to a DB Snapshot and few important things to keep in mind before making use of them.

Microsoft introduced database snapshots in SQL Server 2005 as an enterprise only feature(I believe, that’s true even with SQL 2014…but I could be wrong). The basic concept of a database snapshot is to create a read-only, transactionally consistent(as of the moment of snapshot creation) static view of a given database. Even though they are named as “DB Snapshots”, they have got nothing to deal with Snapshot replication or Snapshot Isolation.

When could a DB Snapshot be useful?
Most common use case:  Let’s say your DEV/Testers are doing some testing(which makes whole bunch of changes to your database) in your test environment and they want the database to be reverted to a state how it was prior to the testing once they are done. A Snapshot works awesome in this case. Reverting is waaaay faster than restoring your database(especially when you are dealing with huge databases).

See this for more info on how they work behind the scenes, when will they grow in size, other use cases and Gotchas to remember while using Snapshots. Let me brief a few important things here.

  • DB Snapshots are not substitutes for your DB backups. Irrespective of whether you’ve snapshots or not, you should have healthy restorable backups in place. Don’t treat DB Snapshots as DR or HA solution.
  • Snapshots will introduce IO overhead, depending on workload on your Source Database.
  • You can’t backup a DB Snapshot and can’t attach/detach a snapshot; you can’t create snapshot for System Databases.
  • No GUI for creating Snapshot(Only T-SQL).
  • You can’t drop Original Source Database as long as a referring snapshot exists for that database.
  • No FAT32 support(Snapshots work on NTFS Sparse files technology).
  • Since they are read-only, static copies…new users can’t be added.
  • Make sure you’ve enough space on the drive holding your Snapshot file. If the drive runs out of space, Snapshot will be marked as “SUSPECT” by SQL Server.
  • Reverting to a Snapshot will break Log chain. Take either a Full or a Diff Backup(assuming you’ve got your latest Full backup) to bridge the Gap in your log chain) immediately after you revert. If not, all the log backups will fail from now on…
  • If your DB is not in SIMPLE recovery mode, Take a log backup before you perform Reversion to secure everything since your last log backup.
  • Full Text Catalogs will be lost once you revert your database. Be careful, if your database uses Full Text features as this could be an unpleasant surprise.
  • Both the source DB and the snapshot will be unavailable when the actual reversion process is in progress.
  • Source DB should be Online,No read only file groups should be present in your Source DB to be able to revert from a snapshot.
  • If your source DB gets corrupted, You can’t use Snapshot for reverting. ( See, they simply can’t replace your Backups)

Now it’s Demo time 🙂
I’ve a database called “Tst_restore” which has 3 tables and this will be my source database for this demo purpose. I’ve created Snapshot as shown in the below screenshot.



IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Tst_Restore_SS')
NAME = Tst,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SREE\MSSQL\DATA\Tst_Restore_SS.ss')

Now you can see I’ve a snapshot created.


See below for the Properties(files section).


As you can see, it has got no ldf file. (If you’ve noticed, while creating the Snapshot I didn’t provided ldf file in my syntax. Yes, that correct…you should provide only (all of) the data files, not the log file while creating DB Snapshot)

Revert a Database to a snapshot:

Now, let’s see how to revert a database to a Snapshot. Assuming something went wrong and you want to revert to snapshot which you’ve taken in the above step, you can use below syntax.

USE master

-- Reverting Tst_Restore to Tst_Restore_SS


As you can see above, to be able to revert we need exclusive access to the database(same as restore). Hence I am taking DB offline and bringing it back online immediately.
Note: Don’t forget to bridge the gap in backup chain right after you revert to a Snapshot. As I’ve already mentioned reverting to a Snapshot will break Log chain. Log backups will fail if you forget this step.

Hope this is informative…!


SQL Server Error Logs – My First Poll.

Hello folks…Thought of creating a poll for the first time 🙂 I hope I picked something which makes sense and…….this polling will be open for the next three days. I will try come up with a follow up blog post If I get at least 10 votes. (Am keeping the results visible for this time)


Buffer Pool Extension – SQL Server 2014

With SQL Server 2014, Microsoft is bringing a lot of features/enhancements into the wild, one of them is “Buffer Pool Extension” AKA BPE. ( Enterprise edition Only). With RTM, MSFT announced that even Standard edition has this feature.

so, what is BPE?  The name itself is pretty much self explanatory…It’s an extension for your Buffer pool. yes, with this you can use any non volatile storage device(SSD’s preferred) as an extension for your SQL Server Buffer Pool. Basic idea here is to eliminate requirement of “adding more physical RAM”, Instead you can mount a new Drive and tell SQL Server to treat that newly created drive as Buffer Pool. Sounds very interesting right? But there are few limitations Per Microsoft SQL Server Team’s Blog which we should keep in mind.

Enterprise Only Feature.
Might not be useful for OLAP/DSS Work loads.
Even in OLTP workloads, improvements are very limited for Write-heavy work loads.
If your server got more than 64 GB of RAM, don’t expect much from this.

Personally, for me these limitations are really frustrating. On one hand, this is Enterprise only feature and on the other hand, they are saying don’t expect much from this if your SQL Server has got already more than 64 GB of RAM allocated. What are the chances, any DBA would build a new SQL Server 2014(Enterprise) with less than or equal to 64 Gigs in 2014 or 2015? I would say almost “NIL”.

It Offers:
Performance Gain on Read heavy OLTP Work Loads.

MSFT recommendations:  (From SQL Server team Blog post)
Use High throughput SSD for better results.
Start from 4x-10x of the Memory available to SQL Server.

How to Enable BPE :
Step 1:
I’ve created a new Drive(BPE) for this demo purpose as shown below.


Step 2:
Before enabling BPE, Let’s check max memory setting I’ve got on this Instance.(512 MB as shown below)


Step 3: 
I’ve tried enabling BPE with 500 MB which actually failed. Reason – Buffer Pool extension size must be larger than current memory allocation threshold.(See below screenshot)


Well, now I increased the size to 600MB and now I got a different Error message(See below)!


Am not sure why this happened, but I’ve created a new Drive (F$) with 2 GB size and it worked this time without any issues as you can see below!!!…I will investigate further why this happened some time later.



Examine from DMV:


How to disable BPE: 


Can We alter the size of BPE?
Nope, we can’t. We have to basically disable and recreate it with new size.

Caution: When you disable BPE, chances are….you will get into significant Memory Pressure situation  and hence  increased IO Pressure.

Bottom line: IMHO, this idea sounds interesting, but I don’t think I would implement this on a production SQL Server considering the limitations it has(YMMV). I would rather convince  my management for purchasing real Memory which has no hand cuffs and no limitations!

After all….What are the chances my client doesn’t have money to purchase more DRAM for the sake of true performance boost when they’ve enough money to purchase High Performance SSD’s just for the sake of BPE(which promises me only limited performance gains)? . Remember folks…..now a days physical RAM is very cheap.

Reviewing SQL Server Error Log – A Better approach!

How many of you have a habit of reviewing Error Log(s) of the SQL Server(s) which you support on a daily basis(At least on regular basis, if not daily)? If you are not doing that, go ahead and start making it as a practice from “Right now”. You can thank me later for this suggestion 😀

If you’re already doing that,then you are awesome…! But, let me ask this. How many times you get annoyed reviewing your error log(s), especially if you are looking for a specific error. Well, you can apply filter or you can simply query your error log from T-SQL. But what if you are only looking for all the severe Errors, total error count, Errors by frequency all at one place? Yes, you can write your own query and use SSRS for generating a fancy report. But Wait……What if I say SSMS already has this cool report inbuilt for us?

In this blog post I will show you a better way to decipher your current Error Log for Errors by using that hidden report.
It’s called “Number Of Errors” Report and you can get to this report by right clicking on “Management” Node -> Reports -> Standard Reports->Number of Errors in your SSMS as shown below.a

How it looks? See below…

As you can see, at a glance I know I’ve got 6 Severe Errors(Sev 18 or above) and 5 Moderate errors in my current error log. See below screenshots for how neat the info is once I expand those nodes.

Severe Errors:

You can see I’ve got the same 824 Error repeated 6 times. ( Well, I tried attaching the same corrupted ldf file 6 times for this demo)

Moderate Errors:

All errors by Frequency:

Is in’t nice? Personally, I liked this report…and thought of sharing what I’ve discovered with you folks 😀
Please ignore if you are already aware of this nifty report, if not…well, you know it now!

And I gave it a new name & identity…

Hello folks…I am very pleased to announce a brand new name and identity which I’ve given to my blog. It will be http://www.mssqltrek.com from now on.

A brand new Name in brand new year 🙂 Welcome to 2014…

Well, you will be redirected automatically to the new URL even if you visit sqlbuzz.wordpress.com 😀
For feedback and suggestions, please shoot an email to sree@mssqltrek.com.

Thank you – Sree!

SQL Server Discovery Report – Know What SQL components you’ve got Installed at a glance

Let’s assume you’ve got a new SQL Server under your support as a primary DBA and you’ve decided to investigate and document all the SQL Server components/features installed on this Server. What are your options? Will you go to Config manager and look at Services for Installed Services. Well, what about Client components? You can pull build levels/Version(SP/CU) easily for your database engine using T-SQL, what about other services? What about your client tool(s) version…Chances are they will be same as your DB Engine(but they could be different based on how and who patched the SQL Servers)? What if you’ve got multiple Instances of SQL Server with different versions…?

Well, we’ve something called “Installed SQL Server features Discovery Report” under Tools in your Installation Center which provides all this information in a single place 🙂 .  Go to the Installation Center from your start->All Programs->Microsoft SQL serverXXXX->Configuration tools->SQL Server Installation Center. Go to Tools and click on discovery report as shown below.


After few seconds, I got below report which gives me basic information on what all I’ve got Installed related to SQL Server on this Server with the build numbers, Edition, clustered/StandAlone etc all at single shot (You can see I’ve got 2 Named Instances, 2008 and 2012) which is pretty useful IMO.


Hope you guys find this nifty tool useful! Cheers…


Microsoft SQL Server 2014 for sure is bringing a lot of cool features/enhancements to aid DBAs and Database Developers….and one of my favorite features it has to offer for a DBA is “Native Database Backup Encryption”.

Till SQL Server 2012:
Let’s say you need to send a backup to Vendor and you want it to be encrypted as per your company security policies. Well, there is no way you can create an encrypted SQL Server database backup natively without help of third party backup tools from vendors like RedGate, Dell, Idera etc. If we don’t have any of those backup tools in our shop, our choice will be pretty much restricted to enabling TDE on the database and take a backup which will be automatically encrypted as well(Unfortunately TDE is Enterprise Edition only feature). What if you are running on standard edition?

Starting SQL Server 2014:
All you’ve to do is select a checkbox while taking a backup(if you are using SSMS GUI) or providing few additional switches in your T-SQL backup statement 🙂
Note: We should create Master Key and Certificate to be able to Encrypt the database backup.

Is this Enterprise Only Feature?
Nope 🙂 You can take Encrypted backups in Enterprise,Business Intelligence and Standard Editions.
Do we need to Turn on TDE at database level?
Nope 🙂
What are the supported Encryption algorithms?
AES_128, AES_192, AES_256, and Triple DES.

So, Now you can Encrypt your database backup in Enterprise,Standard,BI,Developer Editions and an encrypted backup can be restored on any edition(as long as you’ve got certificates 😀 )

Okay folks, now It’s Demo time……I’ve SQL Server 2014 CTP2 Installed in my lab and I created a test database “Tst” for this demo purpose. As of now, it’s a brand new Instance and a brand new database without any keys and certificates.

Step 1:
We’ve to create a Master Key and a Certificate in MASTER Database.(Shown Below)


Step 2: (Am using SSMS for now).
Go to Database Tasks from object Explorer and select backup.  In “General” tab , select your database Backup Path and now navigate to “Media Options” tab. This is important…“Backup to existing Media set” is not supported for Encrypted Backups as of SSMS 2014 CTP2. You should select “Backup to a new media set“(Shown below). Am not sure if this intended or Just a bug which will be fixed in RTM.


If not, your encryption section under “Backup Options” tab will be greyed out as you can see in the below screenshot 😦


Step 3:
After selecting your backup path and selecting Backup to new media set, check the Encrypt Backup option in the “Backup Options” tab and select your desired Algorithm and choose the Certificate which we’ve created in Step 1.


Click “OK”. Tadaaaaaaaaaaa……That’s it guys, we’ve created an Encrypted SQL Server Database Backup 🙂

Btw, if you are interested in T-SQL Syntax, here it is…

BACKUP DATABASE [Tst] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\tst_Encrypt.bak' 
NAME = N'Tst-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, 

In the next post let’s see how to restore this encrypted backup.