20 Jan 2006

SSIS 2005 and Database Snapshots

I have been looking into the feasibility of using database snapshots as method of rolling back an SSIS package that fails and this is what I found:
Creating a database snapshot was not a real problem, there is one issue that you need to be aware of. For each data file within the database that the snapshot is being based on there will have to be a sparse file created for it. I have created a little SQL script that automates the creation of the database snapshot.

Rolling back a database to its initial state after a package has errorred is a problem. The method to revert a database back to its initial state with database snapshots is a restore operation. The restore is a full restore, which of course means there can't be any connections while the restore is happening and this can't be done on-line because it might have to take out the primary data file. This is a problem because SSIS connection manager will create connections to the database when the package starts and doesn't release the connections until the package has completed its run.

A SQL script that automates the creation of the database snapshot.

   1:  DECLARE @vDBName AS VARCHAR(255) DECLARE @vFiles AS VARCHAR(MAX)DECLARE @vSQLCmd AS VARCHAR(MAX) SELECT @vDBName = DB_NAME(), @vFiles = ''
   2:  SELECT @vFiles = @vFiles + '(Name = ' + Name +', filename='''+ LEFT(physical_name,LEN(physical_name) - CHARINDEX('.',REVERSE(physical_name))) + '_PrePublish.ss''), ' FROM sys.database_files WHERE TYPE = 0
   3:  SET @vSQLCmd = 'CREATE DATABASE ' + @vDBName +'_PrePublish ON ' + LEFT(@vFiles,len(@vFiles)-1) + ' AS SNAPSHOT OF ' + @vDBName
   4:  EXEC(@vSQLCmd);

No comments: