December 17, 2008 at 9:30 pm
Comments posted to this topic are about the item Database Snapshots
December 17, 2008 at 9:43 pm
Good question. Unfortunately SQL Server 2005 SP2 does not ignore database snapshots when selecting the "all databases" option in the backup task in a maintenance plan. Seems to be a bit of an oversight on MS part and pain in ours. :crazy: Maybe this will be fixed in SP3. Hopefully at least one of the many third-party backup vendors figured out you cannot backup snapshots.
David
December 18, 2008 at 4:24 am
The no backup solution was obvious but I do not understand why you cannot take another snapshop, to cancel all changes.
Clement
December 18, 2008 at 5:36 am
I agree. I know you can't backup snapshots, but then why wouldn't you take another snapshot so that it is fresher than an older snapshot with a fuller sparse file?
December 18, 2008 at 5:50 am
Me too - why not take another snapshot?
Randy
December 18, 2008 at 5:58 am
Yes, this is another wonderful trick question that we'll have to deal with in everyday life as a DBA.
Perhaps, in some countries, where there are 100 million Oracle DBAs and 120 million SQL DBAs, then people have to trick each other to see who really knows his/her stuff, as everyone is a DBA looking for a job and employers have to ask really tough questions.
How many more questions are we going to see like this? It helps no one.
December 18, 2008 at 6:27 am
I agree. Be more practical on the questions.
Clement
December 18, 2008 at 6:39 am
The premise of the question seemed to be “how to reduce the size of the snapshot?” and the answer is – Take another snapshot. I agree this is a crappy question…If a DBA came up to this scenario and was asked to reduce the size of the snapshop file by doing a backup and he replied “you can’t backup a Snapshot” and walked away, I would fire them. I would expect the DBA to say “I’ll take care of it” and do another snapshot to reduce the size.
Brian RL: Be serious about your work, but do not take yourself too seriously {Philip A. Fisher}
December 18, 2008 at 6:45 am
I would argue that a snapshot is a form of backup. I realize it can't be used if the source database is physically gone, but you can revert from it if you accidentally change/delete data.
If you really are running in to disk space issues because of a snapshot being too large, the best (and only) think you can do is drop and recreate that snapshot. Taking no backup won't solve anything.
The Redneck DBA
December 18, 2008 at 7:24 am
Although the question mentions that the snapshot will grow quickly, there is no mention of trying to shrink the size of the snapshot or running out of disk space. It asks what kind of backup is best for a snapshot database.
You might think that this kind of 'trick question' doesn't help anyone, but I would hope that it would help the 79% (at the time of this comment) of the people who got this question wrong.
December 18, 2008 at 8:08 am
I also had a hard time picking between another snapshot and no backup. I did have one thought in favor of the answer specified though (although I thought about it after answering the question). Taking another snapshot could destroy the purpose of having the snapshot in the first place. I mean, if you're taking a snapshot to capture a specific point in time, taking another snapshot would cause you to lose that 'bookmark'. I guess I'm also assuming that if the growth is what you are worried about you wouldn't keep the old snapshot around after creating a new one.
Don't get too wrapped up in the QOD. If you're more worried about the points and percentage than learning and taking a shot, there are a several ways to make sure you get them all right.
December 18, 2008 at 8:29 am
Mr. Cameron has the right idea. Plenty of people might not understand that you can't take a backup, or that another snapshot might not help since it's from another point in time.
Just because you know something is easy, or what to do, doesn't mean plenty of other people don't understand it.
The original question wasn't well written and I tried to salvage it while keeping the author's intent there.
December 18, 2008 at 8:33 am
Not to be obvious, but you cannot shrink a snapshot. Taking a new snapshot does not shrink it, it creates a new snapshot with a new and update view of the data. Even if the answer appears to be about shrinking a snapshot, there is really nothing you can do and still keep the same view of the database. I do not feel this was a trick question, just don't be too quick to jump to a conclusion. Taking a new snapshot and deleting the old one could have huge ramifications and I would not want a DBA just immediately jumping down that path. Explaining all the options and the results of each option would be a better solution... but the immediate answer is that you cannot backup a snapshot.
David
December 18, 2008 at 8:35 am
Ah!!! Now that makes sense - thanks David!
Randy
December 18, 2008 at 8:38 am
Can not take Snapshot backups seems to be the prevelant accepted answer but how do you reconcile that with the following from BOL fpr 2005
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/9b5720a9-93df-421f-b854-33e4656762e0.htm
Quoting from the above BOL entry:
SQL Server snapshot backup is accomplished in cooperation with third-party hardware or software vendors, or both. These vendors use SQL Server 2005 features that are designed for this purpose. The underlying backup technology creates an instantaneous copy of the data that is being backed up. The instantaneous copying is typically accomplished by splitting a mirrored set of disks or by creating a copy of a disk block when it is written. This preserves the original. At restore time, the original is made available immediately and synchronization of the underlying disks occurs in the background. This results in almost instantaneous restore operations.
and
Only the following types of backups can be snapshot backups:
Full backups
Partial backups
File backups
Differential database backups
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply