SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Joe Fernandez / Snapper / Latest Posts

RE: Snapper

Adam Gojdas — Fri, 11 Jun 2010 12:42:36 GMT

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[code="sql"]if @db_name is null begin raiserror ('error: db name is null',16,1) return end [/code]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:[code="sql"]@snap_dir varchar(100) = 'c:\temp\backup'[/code]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:[code="sql"]@snapshot_name varchar(50)@filename varchar(100)[/code]And look at possibly replacing the cursor. There are plenty of examples of how to do so in the forum.

RE: Snapper

joef551 — Thu, 10 Jun 2010 12:26:35 GMT

Thanks, Vladimir! ;-)

RE: Snapper

Vladimir Sotirov — Thu, 10 Jun 2010 12:11:03 GMT

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 + ',' + @filenamewith : set @filenames = COALESCE(@filenames + ',' , '') + @filenameAnd 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 @listHere is the result set. andrew|lindsay|david

RE: Snapper

Nick W* — Thu, 10 Jun 2010 03:36:38 GMT

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 :)

RE: Snapper

joef551 — Wed, 12 May 2010 19:02:08 GMT

Here's an article on how to revert a database to a snapshothttp://msdn.microsoft.com/en-us/library/ms189281.aspxand 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-SnapshotsJoe

RE: Snapper

scoots987 — Wed, 12 May 2010 16:00:51 GMT

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!!!

RE: Snapper

joef551 — Wed, 12 May 2010 11:07:33 GMT

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.aspxJoe

RE: Snapper

scoots987 — Wed, 12 May 2010 10:18:38 GMT

Sorry for the newbie question, but what is a snapshot?TIA

Snapper

joef551 — Fri, 07 May 2010 16:15:54 GMT

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