Some days back, I received one query on my facebook page that user want to create Database Snapshot frequently & want to delete the oldest with same frequency. Quite interesting scenario. I did not go in deep why he has such requirement but I am clear that It can be done very easily.
So let’s do it. I have setup 2 scripts one for creation of snapshot & another is for deletion of snapshot. We can schedule a SQL agent jobs with 2 steps using these scripts.
Step 1 :-
DECLARE @CREATE_SS VARCHAR(MAX)
DECLARE @DT VARCHAR(100)
SET @DT = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),120),’-‘,’_’),’ ‘,’_’),’:’,’_’)
SET @CREATE_SS =
‘CREATE DATABASE TEST_’+@DT+’ ON
( NAME = TEST, FILENAME =
”C:\TEST_’+@DT+’.SS” )
AS SNAPSHOT OF TEST’
EXEC (@CREATE_SS)
Step 2 :-
IF(SELECT COUNT(*) FROM SYS.DATABASES WHERE SOURCE_DATABASE_ID = DB_ID(‘TEST’))>1
BEGIN
DECLARE @DROP_SS VARCHAR(MAX)
DECLARE @SS_NAME VARCHAR(100)
SELECT TOP 1 @SS_NAME = NAME FROM SYS.DATABASES WHERE SOURCE_DATABASE_ID = DB_ID(‘TEST’)
ORDER BY CREATE_DATE ASC
SET @DROP_SS = ‘DROP DATABASE ‘+ @SS_NAME
EXEC (@DROP_SS)
END
Reference : Rohit Garg (http://mssqlfun.com/)
You can find and follow MSSQLFUN :-
http://www.facebook.com/mssqlfun
Other Linked Profiles :-
http://www.sqlservercentral.com/blogs/mssqlfun/
http://social.msdn.microsoft.com/Profile/rohitgarg
http://www.toadworld.com/members/rohit-garg/blogs/default.aspx
