• Comments posted to this topic are about the item Snapper

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


  • A database snapshot is a point-in-time, read-only copy of a database. Here's a link with good info on snapshots.


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


  • Here's an article on how to revert a database to a snapshot

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


  • 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 🙂

  • Nice script. I looked at it and found two improvements that you can make. First you can replace:

    if @first = 0


    set @first = 1

    set @filenames = @filename



    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,



    [id] ASC


    ) ON [PRIMARY]


    INSERT student VALUES(1, 'andrew', '201-98-9238')

    INSERT student VALUES(2, 'lindsay', '656-89-9238')

    INSERT student VALUES(3, 'david', '555-22-1111')


    DECLARE @list varchar(1024)

    SELECT @list = COALESCE(@list + '|', '') + name

    FROM student

    PRINT @list

    Here is the result set.


  • Thanks, Vladimir! 😉

  • 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


    raiserror ('error: db name is null',16,1)



    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.

  • Thanks for the script.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply