SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Database Snapshot as a Reporting Solution – Some Realtime Issues and Answers

One of Our production database is used by lots of Reporting users. Mirroring is enabled on this database. But no one is using the high performance mirroring/reporting server.

I thought of moving Reporting users to make use of Mirroring server as a Reporting platform by taking Database Snapshots and making them to use Snapshot database.

If you are new to database snapshots, here is the link to learn
How snapshot works

Pros and Cons of Database Snapshots:


Creating Snapsots:

Here is sample script to create database snapsots in mirrored server.

CREATE DATABASE ssAdventureWorks_dbss2230 ON
( NAME = AdventureWorks_Data, FILENAME =
‘C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataAdventureWorks_data_2230.ss’ )
AS SNAPSHOT OF AdventureWorks;

Database health:
You can  create snapshots only when Mirroring database is in Synchronized state.

here is the DMV query  to check Mirroring database status.

select DB_NAME(database_id),mirroring_state_desc, mirroring_role_desc, mirroring_safety_level_desc

from sys.database_mirroring  

where DB_NAME(database_id) =’DB_Name’

Login Issue & Fix:

Issue: Once you create database snapshot, users can not connect to mirroring server immediately because scope of Mirroring is database level. Meaning when new login is created in principal server, that is not propogated to Mirroring server. Only database level credentials are updated in Mirroring server.

Fix: scripts of principal server login’s (with password) can be generated by using sp_help_revlogin.
Here is two good links on this.

Time taken to create snapshot of 100 GB  size database:
In my case,  my job to refresh snapshot takes 11 seconds to drop and recreate database size 100 GB.

Cross Database reference issue:

View in a database snapshot has cross db reference (which is also in restoring state) will fail with below error.

Database 'Another_DB' cannot be opened. It is in the middle of a restore.


Update: We have to keep all  tables available in single snapshot database. In my case, cross db references was less. It works.


Data mismatches between Principal Database and Database Snapshots in Mirroring Server:

Data in principal server can have more updates even after taking database snapshots. Initially there will be some queries from reporting users

on data mismatch between principal and snapshots. We have to educate them that Snapshot db will reflect data of one particular point of time
where in production there can be some updates even after taking database snapshots. We may have to refresh snapshots based on user needs.


Database Snapshot naming conventions and Synonyms:

Its suggested that a database snapshot name should have date & time and with .snap extention.

For example:



In this case, Reporting user has to change the database reference everytime new snapshot is created. this will not be an feasible idea if

Snapshot name is refered in front-end or in ETL tool.


Workaround: We can create a database only with synonyms. Synonyms has to point snapshots. Whenever snapshot is refreshed,

actual database synonyms has to refer new database snapshots.


here is a good link on how to make use of synonym for this case.







I am Ramkumar, 34 years old Consultant, trainer, blogger and speaker at SQL Server user group in India. I have more than 10 years of experience as a developer and SQL Server DBA. I love reading, teaching and blogging about SQL Server internals and performance tuning. My Facebook page: https://www.facebook.com/LivingForSqlServer


No comments.

Leave a Comment

Please register or log in to leave a comment.