SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Raghu Pyapili / Database Snapshots / Latest Posts

RE: Database Snapshots

L' Eomot Inversé — Tue, 07 Dec 2010 10:25:16 GMT

[quote][b]bitbucket-25253 (12/18/2008)[/b][hr]Webrunner[quote]It helped me, in the sense that I was reminded that you can't take a backup of a snapshot[/quote] I suggest you go to BOL (The link is in my previous post), which says [b]you can backup a snapshot[/b]. 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..[/quote]No, a snapshot can't be backed up.A snapshot backup of a database is not a backup of a database snapshot, it's yet another backup[ mechanism and yet another differnet use of the word "snapshot" (to go alongside database snapshot and replication snapshot) and yet another useul point-in-time view of a database (to go alongside database snapshot and shared scalable database).When I say a database snapshot can't be backed up, I don't mean that it's impossible to take a copy of the sparse data (by splitting a mirror or whatever means you like) but I do mean that that copy is NOT a backup from which you can ever restore the snapshot database, ie it's not a backup at all.

RE: Database Snapshots

L' Eomot Inversé — Tue, 07 Dec 2010 10:13:41 GMT

[quote][b]Brian LeBlanc-449582 (12/18/2008)[/b][hr]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.[/quote]I would expect the DBA to ask whether the snapshot was still required (because reports or other queries against the state of play as at that snapshot date were still needed) not to go off and replace the snapshot with a new one (and effectively make it impossible or at least very expensive to run those required queries and/or generate those required reports). I would also expect him to think I was an idiot for having raised the issue - if that snapshot was no longer required for report generation or ad hoc queries it should have been deleted already, so that its size would be irrelevant.It's a good question not a crappy one, it rewuires you to think outside the narrow tecnhical box and consider real business needs.Of course I intensely dislike the explanation, which doesn't attempt to explain why "take another snapshot" (as opposed to "delete the snapshot because it's obsolete") can never be the right answer (you might want to do that, but not because this one is too big, only because there is a frozen state you want to be able to run queries against and this snapshot doesn't represnt that state).

RE: Database Snapshots

BuntyBoy — Wed, 01 Sep 2010 01:26:48 GMT

I also beileve, beetr take a fresh snaphot

RE: Database Snapshots

UMG Developer — Mon, 07 Jun 2010 23:01:27 GMT

Nice question, at least in it's current form.

RE: Database Snapshots

Anipaul — Sun, 21 Dec 2008 02:07:03 GMT

Nice question...

RE: Database Snapshots

bitbucket-25253 — Fri, 19 Dec 2008 09:18:59 GMT

RaghuAgreed your question did excellently perform the task of teaching. Spent a lot of time with BOL and Technet,+ MSDN and perhaps learned more than what I wanted to know, but teach me something new - YES

RE: Database Snapshots

Raghu Pyapili — Thu, 18 Dec 2008 23:45:14 GMT

Thanks for your thoughts! The intent of the question was not to challenge you SQL Gurus, it was a "trick" question in fact to gain more thoughts from you all.

RE: Database Snapshots

DavidSimpson — Thu, 18 Dec 2008 10:23:32 GMT

A database snapshot cannot be backed up, either with SSMS or any third party software that I'm aware of. Technically since a db snapshot is viewed as a read-only database, a third party software could read the database and create it's own backup file... but it cannot use the SQL backup agents.A Snapshot Backup is a not a database snapshot in the sense CREATE DATABASE ... AS SNAPSHOT, instead it's a method supported by SQL Server for performing database backups.As BOL states the full, partial or file backup types can utilize this method for backing up a database. You are not performing a full/partial/file backup against a database snapshot created by the DDL statement.David

RE: Database Snapshots

bitbucket-25253 — Thu, 18 Dec 2008 09:56:02 GMT

Webrunner[quote]It helped me, in the sense that I was reminded that you can't take a backup of a snapshot[/quote] I suggest you go to BOL (The link is in my previous post), which says [b]you can backup a snapshot[/b]. 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..

RE: Database Snapshots

webrunner — Thu, 18 Dec 2008 09:02:51 GMT

[quote][b]jdshabat (12/18/2008)[/b][hr]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.[/quote]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

RE: Database Snapshots

DavidSimpson — Thu, 18 Dec 2008 09:00:01 GMT

[quote]If everything seems to be going well, you have obviously overlooked something[/quote]Hilarious... yet so true.David

RE: Database Snapshots

DavidSimpson — Thu, 18 Dec 2008 08:55:17 GMT

A snapshot backup is different then performing a database snapshot described in the question. :blink: 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

RE: Database Snapshots

Bob Griffin — Thu, 18 Dec 2008 08:44:23 GMT

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....:DThanks

RE: Database Snapshots

bitbucket-25253 — Thu, 18 Dec 2008 08:38:07 GMT

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[url]ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/9b5720a9-93df-421f-b854-33e4656762e0.htm[/url]Quoting from the above BOL entry:[quote][b]SQL Server snapshot backup is accomplished [/b]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.[/quote]and[quote]Only the following types of backups can be snapshot backups:Full backupsPartial backupsFile backupsDifferential database backups[/quote]

RE: Database Snapshots

Randy-574768 — Thu, 18 Dec 2008 08:35:09 GMT

Ah!!! Now that makes sense - thanks David!

RE: Database Snapshots

DavidSimpson — Thu, 18 Dec 2008 08:33:30 GMT

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

RE: Database Snapshots

Steve Jones - SSC Editor — Thu, 18 Dec 2008 08:29:36 GMT

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.

RE: Database Snapshots

Chad Crawford — Thu, 18 Dec 2008 08:08:03 GMT

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.

RE: Database Snapshots

Steven Cameron — Thu, 18 Dec 2008 07:24:10 GMT

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.

RE: Database Snapshots

Jason Shadonix — Thu, 18 Dec 2008 06:45:00 GMT

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.

RE: Database Snapshots

Brian LeBlanc-449582 — Thu, 18 Dec 2008 06:39:03 GMT

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.

RE: Database Snapshots

clementhuge — Thu, 18 Dec 2008 06:27:44 GMT

I agree. Be more practical on the questions.

RE: Database Snapshots

jdshabat — Thu, 18 Dec 2008 05:58:56 GMT

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.

RE: Database Snapshots

Randy-574768 — Thu, 18 Dec 2008 05:50:37 GMT

Me too - why not take another snapshot?

RE: Database Snapshots

jdshabat — Thu, 18 Dec 2008 05:36:33 GMT

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?

RE: Database Snapshots

clementhuge — Thu, 18 Dec 2008 04:24:52 GMT

The no backup solution was obvious but I do not understand why you cannot take another snapshop, to cancel all changes.

RE: Database Snapshots

DavidSimpson — Wed, 17 Dec 2008 21:43:45 GMT

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

Database Snapshots

Raghu Pyapili — Wed, 17 Dec 2008 21:30:43 GMT

Comments posted to this topic are about the item [B]Database Snapshots[/B]