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

Snapper Expand / Collapse
Author
Message
Posted Friday, May 7, 2010 4:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, December 3, 2011 12:37 PM
Points: 5, Visits: 24
Comments posted to this topic are about the item Snapper
Post #918411
Posted Wednesday, May 12, 2010 10:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 12, 2010 1:22 PM
Points: 2, Visits: 8
Sorry for the newbie question, but what is a snapshot?

TIA
Post #920648
Posted Wednesday, May 12, 2010 11:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, December 3, 2011 12:37 PM
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

Post #920705
Posted Wednesday, May 12, 2010 4:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 12, 2010 1:22 PM
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!!!
Post #920901
Posted Wednesday, May 12, 2010 7:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, December 3, 2011 12:37 PM
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
Post #920933
Posted Thursday, June 10, 2010 3:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 22, 2012 8:55 AM
Points: 124, 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 :)

Post #935243
Posted Thursday, June 10, 2010 12:11 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 5:43 PM
Points: 799, Visits: 125
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
Post #935617
Posted Thursday, June 10, 2010 12:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, December 3, 2011 12:37 PM
Points: 5, Visits: 24
Thanks, Vladimir!

Post #935624
Posted Friday, June 11, 2010 12:42 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:46 PM
Points: 79, Visits: 1,331
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.

Post #936251
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse