Snapshots on a database that gets replaced every month

Category: sql server dbengine


rm99 on Tue, 19 Jun 2018 19:50:42

Hi I have a situation where a production database gets downloade to a local reporting copy (not dimension ally modeled just a copy of the prod DB) I would like to have a monthly snapshot made of this database so I have 1 fiscal years available to report against (I.E. 1 full years data). Will this work IE take a snapshot then replace the master DB. I am hoping that the "snapshot" will  just aggregate all the differences from NEWLY COPIED DATABASE ...IS THAT THE CASE?                                                                                                                                                                                                                                                                                                                                   C                                                                     


Mohsin_A_Khan on Tue, 19 Jun 2018 20:47:35

Sorry I couldn't understand what you are trying to say. Can you please rephrase?

When you say downloaded, how are you "downloading" the DB? backups? or copying the database files?

A monthly snapshot made of the prod database for 12 months = 1-year data. A DB snapshot is a point-in-time, static image of the database. It is not a separate copy of the database. It still relies on the main database to fetch data that existed at the time the snapshot was created (and reads some data from the sparse file).

I may be able to offer some suggestions provided I understand your question better.

Erland Sommarskog on Tue, 19 Jun 2018 21:20:27

In addition to Moshin's post: if you want a copy of the database for every month, you would have to take a physical copy of the database every month. A database snapshot can be useful if you want a new fresh reporting database every morning, but there is always a risk that as more and more changes made to the parent database that the sparse file fills up.
Also, if memory serves, you cannot have more than one active database snapshot of each database.

rm99 on Fri, 06 Jul 2018 14:17:04

Sorry yes these are OLTP mssql databases that are from a cloud service (SAAS ) we get nightly "copies" of the mdf /ldf that we then use as a reporting database and trying to use them to build a dimensionaly modeled EDW Ideally we would do montly MSSQL snapshots of these databases but as they are replaced every night I am not sure ther "snapshot" ing would actually work as the new db for the next evening would have changes that might not be picked up by the previous nigths snapshot...

does that explain it any better?

Erland Sommarskog on Fri, 06 Jul 2018 21:16:03

It's rather makes me more confused. :-)

If you are rebuuild/updating the EDW every day, you have no use of a monthly snapshot. But possibly you could use a daily snapshot rather than copying files around. Then again, if the EDW is on a different server, you have no use of a database snapshot on the source instance.

Mohsin_A_Khan on Fri, 06 Jul 2018 21:58:14

If I understand correctly, you are getting MDF and LDF for databases on a nightly basis and you "attach" them as new databases that you use for reporting. You are now needing a way to load data from these reporting DBs to an EDW but want to incrementally load the EDW.

Snapshots are probably out of the equation because they are static point-in-time. You cannot use a snapshot to incrementally load EDW. Not to mention the overhead involved with managing them. You can, although have multiple snapshots for the same DB but disk space becomes an issue because they start out small but as the source DB is changed, the sparse files grow larger and fills up the drive and eventually leave the snapshots in the suspect state which leaves you no option but to drop a snapshot (repairing a snapshot is not possible).

I can think of a couple of options. 

1. Instead of attaching the MDF/LDF daily, isn't it possible for them to send T-log backups instead? That way you could set up log shipping between the main source to reporting DBs (standby mode) and use the same T-log backups to apply to the EDW. That way the log backups are applied to two instances. That said, I don't think SaaS supports log shipping to on-prem. 

2. Another dirty way is to copy the same MDF/LDF to the EDW server and attach the DBs there too.