Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Database Snapshots Expand / Collapse
Author
Message
Posted Thursday, December 18, 2008 8:08 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 12:37 PM
Points: 2,607, Visits: 17,929
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.
Post #622117
Posted Thursday, December 18, 2008 8:29 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 7:12 AM
Points: 33,266, Visits: 15,432
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #622146
Posted Thursday, December 18, 2008 8:33 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, September 8, 2014 1:40 PM
Points: 925, Visits: 999
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



Post #622157
Posted Thursday, December 18, 2008 8:35 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 15, 2011 12:53 PM
Points: 243, Visits: 236
Ah!!! Now that makes sense - thanks David!





Randy
Post #622159
Posted Thursday, December 18, 2008 8:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:46 AM
Points: 5,615, Visits: 25,184
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


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #622164
Posted Thursday, December 18, 2008 8:44 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 7, 2014 10:01 AM
Points: 1,136, Visits: 697
I found the article about the ability to backup snapshots using a third party tool (ttp://technet.microsoft.com/en-us/library/ms189548.aspx) which led me to choose the wrong answer.....Oh well...the point is to think about and research this stuff....I'm a slightly better DBA for it....
:D

Thanks
Post #622176
Posted Thursday, December 18, 2008 8:55 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, September 8, 2014 1:40 PM
Points: 925, Visits: 999
A snapshot backup is different then performing a database snapshot described in the question. SSMS does not support snapshot backups but SQL Server itself does but only via a third party backup software. The benefit of this is the fast backup process with no resource issues... but it's not an online read-only view of the database like a db snapshot a DBA would perform. The URL provided by rgriffin provides more information.

David



Post #622189
Posted Thursday, December 18, 2008 9:00 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, September 8, 2014 1:40 PM
Points: 925, Visits: 999
If everything seems to be going well, you have obviously overlooked something


Hilarious... yet so true.

David



Post #622194
Posted Thursday, December 18, 2008 9:02 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:51 AM
Points: 2,369, Visits: 2,719
jdshabat (12/18/2008)
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.


It helped me, in the sense that I was reminded that you can't take a backup of a snapshot. But I agree that the benefit was incidental given the trick nature of the question. In other words, the question was helpful in spite of, not because of, its tricky nature.

Maybe there is a way to balance trickiness and pragmatism in the questions. I've never tried my hand at a question, so I'm limited in the criticism I think I am entitled to give. :)

- webrunner


-------------------
"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Post #622198
Posted Thursday, December 18, 2008 9:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:46 AM
Points: 5,615, Visits: 25,184
Webrunner
It helped me, in the sense that I was reminded that you can't take a backup of a snapshot


I suggest you go to BOL (The link is in my previous post), which says you can backup a snapshot. True it is 3rd party software, but SQL 2005 contains built in specific capability to allow the 3rd party software to perform a back up of a snapshot.

So can a snapshot be backed up or not. Certainly not using SSMS or other SQL tools generally available to a DBA, but it can be backed up..


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #622259
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse