December 16, 2009 at 9:22 am
I need to create a nightly snapshot of an 05 DB, for historical reporting purposes.
My understanding is that I'll need to run a script to create the snapshot DB.
Something like this:
CREATE DATABASE Rob_test_snapshot_250206_1800 ON
( NAME = N'andrew_test_datafile',
FILENAME = N'E:\DB_Snapshots\andrew_ test_snapshot_2502061800.snap' )
AS SNAPSHOT OF msphxx_data001;
My problem/question is -
How do I automate this script/process so that I get a different named snapshot DB? I would like to auto create a different snapshot DB with the date & name nightly. So that my end user will easily be able to indentify which DB she needs to connect to.
December 16, 2009 at 9:32 am
You need to use dynamic SQL. You can do it directly in the T-SQL job step or as a stored proc executed in a T-SQL job step. Personally, I'd go with the stored proc in case you need to modify it.
December 16, 2009 at 9:38 am
What is your retention policy of those snapshots, do remember that there is a performance overhead on having database snapshots.
Also restoring from one of them will be possible only when all the other database snapshots are deleted, except the one from which you need to restore.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 16, 2009 at 9:55 am
I think the retension policy is going to be one year.
December 16, 2009 at 9:56 am
Lynn Pettis (12/16/2009)
You need to use dynamic SQL. You can do it directly in the T-SQL job step or as a stored proc executed in a T-SQL job step. Personally, I'd go with the stored proc in case you need to modify it.
Any examples I can use for this?
Or should I start a new post in the t-sql forum?
December 16, 2009 at 10:40 am
Actually, start with BOL. It should be able to help you get started with understanding Dynamic SQL.
December 16, 2009 at 10:50 am
Try this
DECLARE @SnapshotNameVarchar(100),
@SnapshotFileNameVarchar(100),
@BKUPSTMTVARCHAR(4000),
@PHYFILENAMEVarchar(200)
SET @SnapshotName = 'Rob_test_snapshot_'
SET @SnapshotName = @SnapshotName + CONVERT(VARCHAR(8), GETDATE(), 112) + '_1800'
SET @BKUPSTMT = 'CREATE DATABASE ' + @SnapshotName + char(13)+ char(10) +' ON ( NAME = N' +'''' + 'andrew_test_datafile' + CONVERT(VARCHAR(8), GETDATE(), 112) + '1800' + ''''+ ',' +
char(13)+char(10) + 'FILENAME = N' + ''''+ 'E:\DB_Snapshots\andrew_test_snapshot_' + CONVERT(VARCHAR(8), GETDATE(), 112) + '1800' + ''''+ ',)' +
char(13)+char(10) +
'AS SNAPSHOT OF msphxx_data001;'
EXEC (@BKUPSTMT)
I apparently have not tested it so do a test of the code.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 16, 2009 at 11:18 am
Awesome thanks !
December 16, 2009 at 11:19 am
You'll also want to a process to delete (drop) snapshots after a specified period of time. You can't just keep creating them as you will run out a of space at some point.
December 16, 2009 at 11:23 am
Lynn Pettis (12/16/2009)
You'll also want to a process to delete (drop) snapshots after a specified period of time. You can't just keep creating them as you will run out a of space at some point.
What Lynn said is right, you will eventually running short of space sonner or later.
Also you would have performance overhead since you are planning to have snapshots for a year and run a snapshot everyday. :w00t:
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 16, 2009 at 11:23 am
krypto69 (12/16/2009)
Awesome thanks !
Welcome !
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 16, 2009 at 11:52 am
Several things to be aware of here.
Database snapshots cannot be backed up. If the source database has to be restored for any reason or the drive with the snapshots fails, that means that all snapshots have been lost and cannot be recovered.
There's a performance implication with database snapshots. If the disk subsystem isn't optimal, you could see an insert/update/delete degradation with as little as 3-5 snapshots. In a test I did on a fairly idle server with a good IO subsystem, a delete of 5000 rows went from 200ms to 1000 ms after adding 7 database snapshots.
If the drive with the snapshots fills up, any snapshot that tries to grow (pretty much any one that needs to add data) will become suspect and will have to be dropped with no chance of recovery.
I really would not recommend snapshots for historical data as long as a year, there are just too many risks. Are you really, really sure this is the best solution?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 16, 2009 at 1:10 pm
krypto69 (12/16/2009)
I think the retension policy is going to be one year.
I just saw this, and I have to say that this solution is not ideal. You really need to look at another solution if you need to be able to look back one year, one day at a time. You really need some kind of historical database that can be queried as need based on an AsOf date method.
December 16, 2009 at 1:27 pm
OKay I'm a little confused...
I thought the snapshots where very small in size...only containing 'changed data'
December 16, 2009 at 1:39 pm
krypto69 (12/16/2009)
I thought the snapshots where very small in size...only containing 'changed data'
To get cleared of that confusion, set up a test system and try creating couple of snapshots, and then modifying data in your database and then few more.
You would get to know it.
I am sure you will run of space sooner or later and as I mentioned earlier, you have a very long retention policy that is awfully long and there should be some mechanism that you have to setup and move data daily (The least required data)
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply