May 12, 2010 at 10:18 am
Sorry for the newbie question, but what is a snapshot?
TIA
May 12, 2010 at 11:07 am
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
May 12, 2010 at 4:00 pm
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!!!
May 12, 2010 at 7:02 pm
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
June 10, 2010 at 3:36 am
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 🙂
June 10, 2010 at 12:11 pm
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
June 10, 2010 at 12:26 pm
Thanks, Vladimir! 😉
June 11, 2010 at 12:42 pm
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.
May 19, 2016 at 6:51 am
Thanks for the script.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply