<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Raghu Pyapili
»
Database Snapshots
28 posts, Page 3 of 3
««
«
1
2
3
Database Snapshots
Rate Topic
Display Mode
Topic Options
Author
Message
DavidSimpson
DavidSimpson
Posted Thursday, December 18, 2008 10:23 AM
SSC Eights!
Group: General Forum Members
Last Login: Friday, May 17, 2013 10:29 AM
Points: 890,
Visits: 931
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
Post #622296
Raghu Pyapili
Raghu Pyapili
Posted Thursday, December 18, 2008 11:45 PM
Grasshopper
Group: General Forum Members
Last Login: Friday, July 24, 2009 12:57 AM
Points: 21,
Visits: 16
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.
Post #622618
bitbucket-25253
bitbucket-25253
Posted Friday, December 19, 2008 9:18 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 6:52 AM
Points: 5,102,
Visits: 20,204
Raghu
Agreed 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
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 #623100
Anipaul
Anipaul
Posted Sunday, December 21, 2008 2:07 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Today @ 1:35 AM
Points: 4,787,
Visits: 1,336
Nice question...
Post #623549
UMG Developer
UMG Developer
Posted Monday, June 07, 2010 11:01 PM
SSCrazy
Group: General Forum Members
Last Login: Monday, May 13, 2013 11:21 AM
Points: 2,163,
Visits: 2,148
Nice question, at least in it's current form.
Post #933790
BuntyBoy
BuntyBoy
Posted Wednesday, September 01, 2010 1:26 AM
Old Hand
Group: General Forum Members
Last Login: Saturday, April 27, 2013 9:30 PM
Points: 392,
Visits: 384
I also beileve, beetr take a fresh snaphot
===========================================
Better try and fail than not to try at all...
Database Best Practices
SQL Server Best Practices
Post #978539
L' Eomot Inversé
L' Eomot Inversé
Posted Tuesday, December 07, 2010 10:13 AM
SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 5:48 PM
Points: 7,088,
Visits: 7,143
Brian LeBlanc-449582 (12/18/2008)
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.
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).
Tom
Que conclure à la fin de tous mes longs propos? C'est que les préjugés sont la raison des sots. (Voltaire, 1756)
Post #1031373
L' Eomot Inversé
L' Eomot Inversé
Posted Tuesday, December 07, 2010 10:25 AM
SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 5:48 PM
Points: 7,088,
Visits: 7,143
bitbucket-25253 (12/18/2008)
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..
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.
Tom
Que conclure à la fin de tous mes longs propos? C'est que les préjugés sont la raison des sots. (Voltaire, 1756)
Post #1031386
« Prev Topic
|
Next Topic »
28 posts, Page 3 of 3
««
«
1
2
3
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
