InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 08:02:50 GMT20http://www.sqlservercentral.com/Forums/Topic1402316-391-1.aspxDarn it, I always get confused on that, as they give you backup compression in R2 Standard, but not Snapshots.In that case I would look at setting the isolation level of the command to SNAPSHOT and read the tables from the same connection, which means setting the database option of ALLOW_SNAPSHOT_ISOLATION to on, but that will invoke the row version store, so you will have extra TempDB activity going on.Thu, 03 Jan 2013 07:00:28 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1402316-391-1.aspxHmmm.. back to the drawing board:Got this: "Database Snapshot is not supported on Standard Edition (64-bit)."Not sure my boss is going to fork out the extra 25K for upgrade to next edition (would love it for data driven subscriptions in SSRs :-))Thu, 03 Jan 2013 06:57:00 GMTmark 4643http://www.sqlservercentral.com/Forums/Topic1402316-391-1.aspxA snapshot is a fairly low weight operation. It creates what is called a sparse file, which stores any original data which has been updated from the source DB.These get created with an allocation size of the DB size at the point the snap was created, but actually consume 0 bytes until data starts changing.So, you actually will only need a small amount of space for the snapshot to live in, if your exports are quick enough.So you would do just as you say, snap, copy, drop.Test it out and see how much space is actually consumed by the sparse file.Thu, 03 Jan 2013 06:20:45 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1402316-391-1.aspxThanks for lightning fast AnthonyLooks like that will do the trick. Just need to add some stuff to manage the disk spaceSeems I need to do the followingCreate snapshot of orders as orders_tempCopy the data I wantDrop orders_tempThu, 03 Jan 2013 06:11:16 GMTmark 4643http://www.sqlservercentral.com/Forums/Topic1402316-391-1.aspxCreate a snapshot of the database in question at 00:00CREATE DATABASE dbnamesnapshot ON (......) AS SNAPSHOT OF dbname[url]http://msdn.microsoft.com/en-us/library/ms175876.aspx[/url]That way the data is static and you can then query the tables as no data changes can happen to a snapshot as it is a read only copy as of the moment the snapshot completes.Thu, 03 Jan 2013 06:06:25 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1402316-391-1.aspxHi GuysI'm using SQL Server 2008 R2 (Standard Edition) and trying to get a nightly snapshot of several tables for auditing/integrity checking (from a 3rd party vendor's DB). As a simple example I have an items table which has qty on hand and unit value. From this I calculate my inventory value. I have a separate transaction table that records all issues, receipts, scrap, inventory adjustments. Unfortunately, the users make way too many adjustments and try and come up with workarounds for stuff and it makes month end reconciliation a nightmare. To try and find issues in a more timely manner, I figured I'd take a snapshot of the two tables (there are actually about 6) at midnight calculate the inventory value at end of Day 1 and again at end of Day 2 and make sure the sum of transaction matches the difference in inventory valuation. For exampleItem 1, Date 1, Qty 10Item 1, Date 2, Qty 5Item 1 receipts between Date 1 and Date 2 = 10Item 1 issues between Date 1 and Date 2 = -15Item 1 Net changes = -5 Date 2 inventory - Date 1 Inventory = -5, so things are good for that periodOur warehouse works 24/y so the data is constantly changing and I'm having problems with sync, as the copy of the items table could start at midnight and take 1 min (for example) and then the transactions copy runs, so any transactions between 00:00 and 00:01 would not be reflected in the starting valuation. I could record the snapshot end time and then get transactions using that time instead of midnight.I currently have 2 SP's to copy the tables as sequential steps in a single job, so this obviously and issue.What's the best way to get an exact sync copy?I could use 2 scheduled jobs and start both at midnight to improve things. however I'm not sure how SQL server works with copying large volumes of data and if something like snapshot isolation would work. For example, if I start to copy the transactions 00:00 and it takes 2 mins. If there's a transaction at 00:01, will it be in the copy that completes at 00:02.Worst case, I could do a backup and restore, but same question applies: if I backup the DB while online at 00:00 and it takes 10 mins, am I getting the state at 00:00?I'm sure these are common issues in OLTP environments, so was wondering if someone could pitch in with a solutionThanksMarkThu, 03 Jan 2013 05:55:40 GMTmark 4643