Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Snapper


Snapper

Author
Message
joef551
joef551
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 24
Comments posted to this topic are about the item Snapper
scoots987
scoots987
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 8
Sorry for the newbie question, but what is a snapshot?

TIA
joef551
joef551
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 24
A database snapshot is a point-in-time, read-only copy of a database. Here's a link with good info on snapshots.

http://msdn.microsoft.com/en-us/library/ms175158.aspx

Joe
scoots987
scoots987
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 8
Thanks for the link. That is what I expected of what a snapshot is. Now the question is why would we use this instead of regular backups or a maintenence plan? Well I suppose the maintenence plan can do a lot more but for the most part why not use maintenence plans for our backups?

Are there articles on how to restore a snapshot? I might be over thinking this. I do see it as a quick and dirty of making a backup.

Thanks!!!
joef551
joef551
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 24
Here's an article on how to revert a database to a snapshot

http://msdn.microsoft.com/en-us/library/ms189281.aspx

and here's a good article on when and how to use a snapshot.

http://searchsqlserver.techtarget.com/tip/SQL-Server-2005-When-and-how-to-use-Database-Snapshots

Joe
Nick W*
Nick W*
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 180
Hmm.. It's amazing how some of the simplest-looking scripts can be so useful. At first glance, I just thought "Pah, backups are better".. And then when I read through your script I thought "hmmm.. Doing this three or four times a day might be REALLY handy...

If you have a customer who makes the occasional mistake (let's face it, Lusers make cockups all the time) , then if you take four snapshots a day, then you know that you can inspect the db state freely at four 'sample' points, copy the offending table back into the main DB, fix the issue, and continue on, without all that tedious mucking about with backups.

Interesting script. Thanks for that.. I'll be playing with it later Smile
Vladimir Sotirov
Vladimir Sotirov
SSC Eights!
SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)

Group: General Forum Members
Points: 803 Visits: 138
Nice script. I looked at it and found two improvements that you can make. First you can replace:
if @first = 0
begin
set @first = 1
set @filenames = @filename
end
else
set @filenames = @filenames + ',' + @filename

with :
set @filenames = COALESCE(@filenames + ',' , '') + @filename

And you can remove the cursor use by following the sample like this:

In this sample, we use COALESCE to generate a flat list. You can specify a delimiter to seperate the items in the list.

CREATE TABLE [dbo].[student](
[id] [int] NOT NULL,
[name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ssn] [varchar](11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_student] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT student VALUES(1, 'andrew', '201-98-9238')
INSERT student VALUES(2, 'lindsay', '656-89-9238')
INSERT student VALUES(3, 'david', '555-22-1111')
GO

DECLARE @list varchar(1024)
SELECT @list = COALESCE(@list + '|', '') + name
FROM student

PRINT @list

Here is the result set.

andrew|lindsay|david
joef551
joef551
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 24
Thanks, Vladimir! ;-)
Adam Gojdas
Adam Gojdas
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 1429
Some other things to improve on the code might be:

Get rid of the return statement after the raiserror since it can never be hit when raising severity level 16

if @db_name is null
begin
raiserror ('error: db name is null',16,1)
return
end



Maybe add an entire TRY/CATCH block to the whole procedure instead of just at the end to deal with this appropriately? And nest the existing one inside of it.

Find out if you need to increase the size of this one it is pretty small and I think a directory path can be up 260:
@snap_dir varchar(100) = 'c:\temp\backup'



note there would be issues with DB names greater than 100 characters since they could be up to nvarchar(128) and the snapshot name seems to limit things even further. Also a potential for filename to have problems with these sizes:

@snapshot_name varchar(50)
@filename varchar(100)



And look at possibly replacing the cursor. There are plenty of examples of how to do so in the forum.
Iwas Bornready
Iwas Bornready
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8680 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search