September 29, 2015 at 10:04 am
Hi,
I am a SQL DBA in our Company. I found lately that the Database Snapshots can be very useful for our testing environment.
The only problem, I could not find a good way of restoring a snapshot backup from Live to the Testing Server.
We are running SQL Server 2008 and 2012 databases mostly on the Enterprise editions.
Any help (suggestions, scripts) will be greatly appreciated.
Thank you.
Alex
September 29, 2015 at 10:42 am
You can't, that's not how snapshots work.
When you create a database snapshot, any changes you make to the source database of that snapshot, the original version of the page is then copied into the snapshot. Hence all that the snapshot contains is the original version of pages that have changed in the source DB since the snapshot was created.
A revert to snapshot just copies those original pages back into the source database.
Hence if you create a snapshot on your testing DB, it's tied to the testing DB, containing nothing more than the pages which have changed in that test DB. It can't be used to revert any other database because it only knows the changes made to the testing DB.
Snapshots aren't backups.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 29, 2015 at 12:50 pm
Thanks Gail,
I probably did not make myself clear.
My purpose is to take a backup of the Live database (that is over 100GB in size), while the site is under maintenance and the database is offline, and restore it over testing database on the other Server.
So, I thought that I maybe can make a use of snapshots (or replicated snapshots), since they are quick in create and restore.
There was an article Online but it was not clear as well.
So, still no luck for me using Snapshots, right?
Thank you.
Alex
September 29, 2015 at 2:41 pm
I understood you just fine. The only restore which a snapshot can be used for is to revert (restore) the source database to the time which the snapshot was created.
A snapshot is quick to create, because minimal data is copied on creation. It's fast to revert to, because minimal data has to be copied back. Both of those are because a snapshot is not a backup, not a database, but a collection of the original version of pages which have been changed since it was created.
If you want to take the live database and restore it to dev, you need to take a backup of the production database, copy the backup file to dev and then restore it. Normal backup database, restore database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 29, 2015 at 2:49 pm
Thanks a lot for your help Gail,
It is crystal clear now.
December 2, 2020 at 2:59 am
I am exploring usage of snapshots as a mechanism for doing day to day row level delta dumps. I use the normal backup database, restore database to put copies of my databases onto another server where I do the delta dumps.
Is it possible to restore not only the database but ALSO some subset of a database's snapshots from the prod to the second server? I know that it is not possible to do a full restore to a database that has snapshots referencing it. But is is possible to add a set of snapshots from the source server to the second server? From what I have been able to dig up so far, the snapshots do not appear to be "portable".
December 2, 2020 at 3:06 am
Is it possible to restore not only the database but also it's snapshots from the Live to the Dev server?
It's about time you found this place and signed up, Joe. Welcome aboard!
--Jeff Moden
Change is inevitable... Change for the better is not.
December 2, 2020 at 3:09 am
Have been signed up for a while... just never had to ask a question. I always seem to be able to find what I need from an existing thread.
December 2, 2020 at 7:03 pm
It is not possible to port a snapshot - because the snapshot is entirely dependent on the source database. The snapshot only contains the delta changes that have occurred in the source database since the snapshot was created. When querying a snapshot...you are still querying the source database except where data has changed - which you then get from the snapshot database file.
You also have to be aware that a snapshot will 'grow' over time - based on how much data is changed in the source database. Eventually that can become an issue and impact the production system.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 2, 2020 at 11:42 pm
Thanks Jeff.
Your response confirms my suspicions about the feature. As I have limited familiarity, I wanted to make sure I wasn't missing something.
Just to be clear.... if you will indulge me... let me pose my question another way by posing another use case.
Lets say I want to migrate a database from server A to server B. Simple enough without snapshots, just backup the db on server A and restore from that backup onto server B. But let's say that the db on server A has a snapshot that I wish to preserve when migrated to server B. It appears there really is no way to do that. I am guessing that the nitty gritty behind why that is... is because the snapshot is a page based entity... and there is no page consistency maintained via the backup and restore... thus making it impossible for the snapshot to align with the new image on server B. Which would explain why there is no way to backup the snapshot.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply