Following my investigations on SSIS 2012 and the issues mentioned in my previous blog post, create environment variables from project parameters, I created a script that easily lets you copy all the existing environment variables either to a new environment on the same server or to a new (or existing) environment on a completely different server.

Please note: this script will NOT make any changes to your environments by itself! It merely generates a sql script that has to be copy/pasted in to a SSMS query window and executed manually.

/*
This script will take all environment variables in an existing environment and make "insert" scripts 
out of them so you can easily deploy them to a new server or a new environment on the same server.

Script created by Henning Frettem, www.thefirstsql.com, 2013-05-28
*/

SET NOCOUNT ON

DECLARE 
	@folder_name nvarchar(200)				= 'ProjectFolderName',
	@environment_name_current nvarchar(200) = 'DEV',
	@environment_name_new nvarchar(200)		= 'TEST',
	@name sysname, 
	@sensitive bit, 
	@description nvarchar(1024), 
	@value sql_variant, 
	@type nvarchar(128)

PRINT 'DECLARE 
	@folder_id bigint,
	@environment_id bigint'

PRINT ''

--> Create folder if it doesn't exist and get folder_id
PRINT 'IF NOT EXISTS (SELECT 1 FROM [SSISDB].[catalog].[folders] WHERE name = N''' + @folder_name + ''')
	EXEC [SSISDB].[catalog].[create_folder] @folder_name=N''' + @folder_name + ''', @folder_id=@folder_id OUTPUT
ELSE 
	SET @folder_id = (SELECT folder_id FROM [SSISDB].[catalog].[folders] WHERE name = N''' + @folder_name + ''')'

PRINT ''

--> Create environment if it doesn't exist
PRINT 'IF NOT EXISTS (SELECT 1 FROM [SSISDB].[catalog].[environments] WHERE folder_id = @folder_id AND name = N''' + @environment_name_new + ''')
	EXEC [SSISDB].[catalog].[create_environment] @environment_name=N''' + @environment_name_new + ''', @folder_name=N''' + @folder_name + ''''

PRINT ''

--> Get the environment_id
PRINT 'SET @environment_id = (SELECT environment_id FROM [SSISDB].[catalog].[environments] WHERE folder_id = @folder_id and name = N''' + @environment_name_new + ''')'

PRINT ''

--> Making cursor because mapping of sql_variant datatype is different than the normal datatypes
DECLARE cur CURSOR FOR 
	SELECT c.name, c.sensitive, c.description, c.value, c.type
	FROM [SSISDB].[catalog].[folders] a
		INNER JOIN [SSISDB].[catalog].[environments] b
			ON a.folder_id =  b.folder_id
		INNER JOIN [SSISDB].[catalog].[environment_variables] c
			ON b.environment_id = c.environment_id
	WHERE a.name = @folder_name
		AND b.name = @environment_name_current

OPEN cur
FETCH NEXT FROM cur INTO @name, @sensitive, @description, @value, @type

PRINT 'DECLARE @var sql_variant'
PRINT ''

WHILE (@@FETCH_STATUS = 0)
	BEGIN
		PRINT 'SET @var = N''' + CONVERT(nvarchar(max), @value) + ''''

		PRINT 'IF NOT EXISTS (SELECT 1 FROM [SSISDB].[catalog].[environment_variables] WHERE environment_id = @environment_id AND name = N''' + @name + ''')
			EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N''' + @name + ''', @sensitive=' + CONVERT(varchar(2), @sensitive) + ', @description=N''' + @description + ''', @environment_name=N''' + @environment_name_new + ''', @folder_name=N''' + @folder_name + ''', @value=@var, @data_type=N''' + @type + ''''

		PRINT ''

	FETCH NEXT FROM cur INTO @name, @sensitive, @description, @value, @type
	END

CLOSE cur
DEALLOCATE cur

A little late in the game perhaps but I just recently started looking in to SQL Server 2012 and especially SSIS, and there were some shortcomings (in my opinion) in regards to the configurations. The new deployment model with versioning and everything is utterly fantastic (!!), and making project parameters is easy peasy. However, when it comes to making the project parameters configurable across different environments it’s a different story. Assuming you already know the deal about the SSISDB catalog and the environments, I feel that the actual creation of the environment variables is very cumbersome. They are usually a very close replica of the design time project parameters, so to make the creation of the variables easier on myself I created a script that takes all the project parameters of a deployed project (deployed to your dev machine is sufficient), check if an environment variable with the same name exists and if it doesn’t then create it with the design time value.

Important: changes to the project parameters are only detected once the project is deployed to the Integration Services catalog!

/*
This script will take all project parameters with their design time values in your SSIS-project and create
an "insert script" for making them in to SSIS environment variables. It will NOT make any changes to
your databases!
  
Script created by Henning Frettem, www.thefirstsql.com, 2013-05-28
*/
  
DECLARE
    @folder_name nvarchar(200)      = 'ProjectFolderName',
    @environment_name nvarchar(200) = 'DEV',
    @parameter_name varchar(200),
    @design_default_value sql_variant,
    @sensitive bit, 
    @description nvarchar(1024), 
    @data_type nvarchar(128)
 
--> Checking that the folder exists
IF NOT EXISTS (SELECT 1 FROM [SSISDB].[catalog].[folders] WHERE name = @folder_name)
    BEGIN
        RAISERROR('Provided folder name does not exist in the Integration Services catalog. Create the folder first, then run the script again', 16, 1)
        RETURN
    END
 
PRINT 'DECLARE 
    @folder_id bigint,
    @value sql_variant,
	@folder_name nvarchar(200)      = ''' + @folder_name + ''',
	@environment_name nvarchar(200) = ''' + @environment_name + ''''
 
PRINT ''
 
PRINT '--> Need folder_id for ''' + @folder_name + ''' 
    SET @folder_id = (SELECT folder_id FROM [SSISDB].[catalog].[folders] WHERE name = @folder_name)'
  
PRINT ''
 
PRINT '--> Create ' + @environment_name + ' environment in ''' + @folder_name + '''-folder if it does not exist
    IF NOT EXISTS (SELECT 1 FROM [SSISDB].[catalog].[environments] WHERE folder_id = @folder_id AND name = @environment_name)
		BEGIN
			EXEC [SSISDB].[catalog].[create_environment] @environment_name = @environment_name, @folder_name = @folder_name
			PRINT ''CREATED: Environment ' + @environment_name + ' in folder ' + @folder_name + '''
		END
	ELSE
		PRINT ''EXISTS: Environment ' + @environment_name + ' exists'''
 
PRINT ''
  
--> Create cursor for all project parameters
DECLARE cur CURSOR FOR
    SELECT a.parameter_name, a.design_default_value, a.sensitive, a.description, a.data_type
    FROM [SSISDB].[catalog].[object_parameters] a
        INNER JOIN [SSISDB].[catalog].[projects] b
            ON a.project_id = b.project_id
        INNER JOIN [SSISDB].[catalog].[folders] c
            ON b.folder_id = c.folder_id
    WHERE c.name = @folder_name
        AND SUBSTRING(a.parameter_name, 1, 3) <> 'CM.'
    ORDER BY a.parameter_name
  
OPEN cur
FETCH NEXT FROM cur INTO @parameter_name, @design_default_value, @sensitive, @description, @data_type
  
WHILE (@@FETCH_STATUS = 0)
    BEGIN
        PRINT '--> Creating variable ''' + @parameter_name + ''' if it doesn''t exist'
        PRINT ' SET @value = N''' + CONVERT(nvarchar(max), @design_default_value) + ''''
        PRINT '
            IF NOT EXISTS (SELECT 1 
                            FROM [SSISDB].[catalog].[environments] b 
                                INNER JOIN [SSISDB].[catalog].[environment_variables] c
                                    ON b.environment_id = c.environment_id
                            WHERE b.folder_id = @folder_id 
                                AND b.name = @environment_name
                                AND c.name = ''' + @parameter_name + ''')
				BEGIN
					EXEC [SSISDB].[catalog].[create_environment_variable] 
						@variable_name = ''' + @parameter_name + ''', 
						@sensitive = ' + CONVERT(varchar(2), @sensitive) + ', 
						@description = ''' + @description + ''', 
						@environment_name = @environment_name, 
						@folder_name = @folder_name, 
						@value = @value, 
						@data_type = ''' + @data_type + '''
					PRINT ''CREATED: Environment-variable ' + @parameter_name + '''
				END
			ELSE
				PRINT ''EXISTS: Environment-variable ' + @parameter_name + ''''

		PRINT ''
  
    FETCH NEXT FROM cur INTO @parameter_name, @design_default_value, @sensitive, @description, @data_type
    END
  
CLOSE cur
DEALLOCATE cur

I recently had the need to create an SSRS report showing the status of some jobs from the 10 last days, so I wrote this script that I thought could come in handy to others as well. The contents should be pretty self explanatory except maybe for the RowNum column which is there to separate each iteration of the job:

WITH cte_jobhistory AS (
		--> Creating a cte only to make the columns run_date and run_time into a "datetime-friendly" format  
		--  so it can be used for filtering
		SELECT 
			RunDateString = SUBSTRING(CAST(run_date as VARCHAR(20)), 1, 4) + '-' +  SUBSTRING(CAST(run_date as VARCHAR(20)), 5, 2) + '-' + SUBSTRING(CAST(run_date as VARCHAR(20)), 7, 2),
			RunTimeString = SUBSTRING(RIGHT('000000' + CAST(run_time as VARCHAR(20)), 6), 1, 2) + ':' + SUBSTRING(RIGHT('000000' + CAST(run_time as VARCHAR(20)), 6), 3, 2) + ':' + SUBSTRING(RIGHT('000000' + CAST(run_time as VARCHAR(20)), 6), 5, 2),
			*
		FROM msdb.dbo.sysjobhistory
		)
SELECT 
	a.name,
	RowNum = ROW_NUMBER() OVER (PARTITION BY a.name, b.step_id ORDER BY instance_id DESC),
	b.step_id,
	b.step_name,
	b.message,
	RunStatus = CASE b.run_status 
		WHEN 0 THEN 'Failed'
		WHEN 1 THEN 'Succeded' 
		WHEN 2 THEN 'Retry' 
		WHEN 3 THEN 'Cancelled' 
		WHEN 4 THEN 'In Progress' 
		END,
	b.run_duration,
	b.RunDateString,
	b.RunTimeString
FROM msdb.dbo.sysjobs a
	INNER JOIN cte_jobhistory b
		ON a.job_id = b.job_id
WHERE CONVERT(datetime, b.RunDateString) > GETDATE()-10
ORDER BY a.name ASC, b.instance_id DESC

I’ve been doing quite a bit of SSIS 2005 lately and I’ve come across a weird problem that almost had the best of me. It’s no secret that I absolutely *hate* Oracle and this problem I just had didn’t make it any better.

What I had was a quite long and complex pl/sql query (over 250 lines of code!) that was running smoothly in sql developer, the Oracle OLE DB provider in SSIS had absolutely no problems with the code when previewing data or mapping the column metadata. But when I saved the package and ran it I got the error message saying “ORA-000938: missing expression”. Sooo…what to do? Obiously there was some kind of syntax error but I have a query that runs perfecty in SQL Developer, it validates without a hitch in SSIS and I can even preview the data in the OLE DB source object in SSIS. Quite amazing to be honest!

So, after 7 hours (!! I kid you not!) of debugging this query I had a colleague step in to my office for a few minutes. And then he dropped the bomb: “Did you remove all the comments in your query??”. I just stared back at him in disbelief but at that point I was ready to do just about anything so I tried it and; Voila. The query ran through. After a bit more debugging I learned that it was the “–” (double dash) it didn’t like and that the /* */ works just fine and I think it has something to do with th driver actually removing all the carriage returns in the statement so anything after the first comment is also commented out. Go Oracle!

In conclusion:
Don’t ever comment your PL/SQL queries in ssis using “–” (double dash), it simply will not work!


The script I created for generating a point-in-time restore t-sql code has been updated and the last version should support sql server 2005, 2008 and 2012 versions.

There is one prerequisite:
- Only backups made to file on disk is supported

Here is the final script:

/*
Generate Point-in-time restore script, given only DBName and Point-in-time timestamp
The script WILL NOT do any changes to your databases, it will only generate sql code 
that you need to manually execute.
 
Created by Henning Frettem, www.thefirstsql.com
*/
SET NOCOUNT ON
GO
 
DECLARE
  @DBName varchar(200),
  @PointInTime datetime,
  @Filename varchar(200),
  @tab varchar(1) = char(9),
  @cr varchar(2) = char(13)+char(10),
  @Full_BackupStartDate datetime,
  @Diff_BackupStartDate datetime,
  @Log_BackupStartDate datetime,
  @SQL varchar(max) = ''
 
SET @DBName  = 'MyDatabase'
SET @PointInTime = '2013-05-21 15:40:00'
 
BEGIN TRY
  --> Performing some checks
  IF NOT EXISTS (SELECT 1 FROM msdb.dbo.backupset WHERE database_name = @DBName)
    RAISERROR(N'The database has never been backed up', 16, 1)
  IF NOT EXISTS (SELECT 1 FROM msdb.dbo.backupset WHERE database_name = @DBName AND type = 'D' AND backup_start_date <= @PointInTime)
    RAISERROR(N'No full backup exists prior to the specified PointInTime', 16, 1)
  ELSE IF NOT EXISTS (SELECT 1 FROM msdb.dbo.backupset WHERE database_name = @DBName AND type = 'L' AND backup_start_date > @PointInTime)
    RAISERROR(N'No backup of the log exist after the specified PointInTime, do a tail log backup first', 16, 1)
 
  --> Getting the filename and the date of the last full backup prior to the PointInTime
  SELECT TOP 1 
      @Filename = b.physical_device_name,
      @Full_BackupStartDate = backup_start_date
  FROM msdb.dbo.backupset a
    INNER JOIN msdb.dbo.backupmediafamily b
      ON a.media_set_id = b.media_set_id
  WHERE a.database_name = @DBName
    AND a.type = 'D'
    AND a.backup_start_date <= @PointInTime
  ORDER BY a.backup_start_date DESC
 
  SET @SQL = 'USE master' + @cr + 
    'GO' + @cr + @cr +
    'ALTER DATABASE ' + QUOTENAME(@DBName) + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE' + @cr + 
    'GO' + @cr + @cr
  
  PRINT @SQL
  SET @SQL = ''
 
  --> Restore the last full backup if it exists
  SELECT TOP 1
    @SQL = @SQL + 'RESTORE DATABASE ' + QUOTENAME(@DBName) + ' FROM DISK = ''' + b.physical_device_name + ''' 
        WITH FILE=' + CAST(a.Position as varchar(10)) + ', REPLACE, STATS=5, NORECOVERY' + @cr + 'GO' + @cr,
    @Diff_BackupStartDate = a.backup_start_date
  FROM msdb.dbo.backupset a
    INNER JOIN msdb.dbo.backupmediafamily b
      ON a.media_set_id = b.media_set_id
  WHERE a.database_name = @DBName
    AND a.type = 'D'
    AND a.backup_start_date = @Full_BackupStartDate
  ORDER BY a.backup_start_date DESC   

  PRINT @SQL
  SET @SQL = ''
   
  --> Restore the last differential backup if it exists
  SELECT TOP 1
    @SQL = @SQL + 'RESTORE DATABASE ' + QUOTENAME(@DBName) + ' FROM DISK = ''' + b.physical_device_name + ''' 
        WITH FILE=' + CAST(a.Position as varchar(10)) + ', NORECOVERY' + @cr + 'GO',
    @Diff_BackupStartDate = a.backup_start_date
  FROM msdb.dbo.backupset a
    INNER JOIN msdb.dbo.backupmediafamily b
      ON a.media_set_id = b.media_set_id
  WHERE a.database_name = @DBName
    AND a.type = 'I'
    AND a.backup_start_date > @Full_BackupStartDate
    AND a.backup_start_date < @PointInTime
  ORDER BY a.backup_start_date DESC
  
  PRINT @SQL
  SET @SQL = ''
   
  IF @Diff_BackupStartDate IS NULL
    SET @Diff_BackupStartDate = @Full_BackupStartDate
     
  --> Generate all log restores except the last one
  SELECT
    @SQL = @SQL + 'RESTORE LOG ' + QUOTENAME(@DBName) + ' FROM DISK = ''' + b.physical_device_name + ''' 
        WITH FILE=' + CAST(a.Position as varchar(10)) + ', NORECOVERY' + @cr + 'GO' + @cr,
    @Log_BackupStartDate = a.backup_start_date   
  FROM msdb.dbo.backupset a
    INNER JOIN msdb.dbo.backupmediafamily b
      ON a.media_set_id = b.media_set_id
  WHERE a.database_name = @DBName
    AND a.type = 'L'
    AND a.backup_start_date > @Diff_BackupStartDate
    AND a.backup_start_date < @PointInTime
  ORDER BY a.backup_start_date
 
  PRINT @SQL
  SET @SQL = ''

  --> Generate last log restore script with the stopat command and recovery
  SELECT TOP 1
    @SQL = @SQL + 'RESTORE LOG ' + QUOTENAME(@DBName) + ' FROM DISK = ''' + b.physical_device_name + ''' 
        WITH FILE=' + CAST(a.Position as varchar(10)) + ', RECOVERY, STOPAT = ''' + CONVERT(varchar(20), @PointInTime, 120) + '''' + @cr + 'GO' + @cr
  FROM msdb.dbo.backupset a
    INNER JOIN msdb.dbo.backupmediafamily b
      ON a.media_set_id = b.media_set_id
  WHERE a.database_name = @DBName
    AND a.type = 'L'
    AND a.backup_start_date > @PointInTime
  ORDER BY a.backup_start_date ASC
 
  PRINT @SQL
 
END TRY
BEGIN CATCH
  PRINT ERROR_MESSAGE()
END CATCH

A new version has been created! Check it out here: Updated: create point-in-time restore script automatically

This is a 2005-version of the script originally posted here: Create a full Point-In-Time restore script automatically! – 2008 version

/*
Generate Point-in-time restore script, given only DBName and Point-in-time timestamp
Works only on SQL2005 databases!!

Script for generating restore script for full backup originally created by Michael 
Valentine Jones, SQLTeam.com

The rest is created by Henning Frettem, www.thefirstsql.com
*/
SET NOCOUNT ON
GO

DECLARE 
  @DBName varchar(200),
  @PointInTime datetime,
  @Filename varchar(200),
  @tab varchar(1),
  @cr varchar(2),
  @Full_BackupStartDate datetime,
  @Diff_BackupStartDate datetime,
  @Log_BackupStartDate datetime,
  @SQL nvarchar(max)
  
SET @DBName = 'MyDatabaseName'
SET @PointInTime = '2011-11-22 09:45:00'
SET @tab = char(9)
SET @cr = char(13)+char(10)
SET @SQL = ''


BEGIN TRY
  --> Performing some checks
  IF NOT EXISTS (SELECT 1 FROM msdb.dbo.backupset WHERE database_name = @DBName AND type = 'D' AND backup_start_date <= @PointInTime)
    RAISERROR(N'No full backup exists prior to the specified PointInTime, restore is not possible at the given PointInTime.', 16, 1)
  ELSE IF NOT EXISTS (SELECT 1 FROM msdb.dbo.backupset WHERE database_name = @DBName AND type = 'L' AND backup_start_date > @PointInTime)
    RAISERROR(N'No backup of the log exist after the specified PointInTime, do a tail-log backup before running this script again', 16, 1)

  --> Getting the filename and the date of the last full backup prior to the PointInTime
  SELECT TOP 1 
    @Filename = b.physical_device_name,
    @Full_BackupStartDate = backup_start_date
  FROM msdb.dbo.backupset a
    INNER JOIN msdb.dbo.backupmediafamily b
      ON a.media_set_id = b.media_set_id
  WHERE a.database_name = @DBName
    AND a.type = 'D'
    AND a.backup_start_date <= @PointInTime
  ORDER BY a.backup_start_date DESC

  --> Create temp-tables for file header information
  DECLARE @header table (
	  BackupName varchar(128) NULL,
	  BackupDescription varchar(255) NULL,
	  BackupType smallint NULL,
	  ExpirationDate datetime NULL,
	  Compressed tinyint NULL,
	  Position smallint NULL,
	  DeviceType tinyint NULL,
	  UserName varchar(128) NULL,
	  ServerName varchar(128) NULL,
	  DatabaseName varchar(128) NULL,
	  DatabaseVersion int NULL,
	  DatabaseCreationDate datetime NULL,
	  BackupSize numeric(20,0) NULL,
	  FirstLSN numeric(25,0) NULL,
	  LastLSN numeric(25,0) NULL,
	  CheckpointLSN numeric(25,0) NULL,
	  DatabaseBackupLSN numeric(25,0) NULL,
	  BackupStartDate datetime NULL,
	  BackupFinishDate datetime NULL,
	  SortOrder smallint NULL,
	  CodePage smallint NULL,
	  UnicodeLocaleId int NULL,
	  UnicodeComparisonStyle int NULL,
	  CompatibilityLevel tinyint NULL,
	  SoftwareVendorId int NULL,
	  SoftwareVersionMajor int NULL,
	  SoftwareVersionMinor int NULL,
	  SoftwareVersionBuild int NULL,
	  MachineName varchar(128) NULL,
	  Flags int NULL,
	  BindingID uniqueidentifier NULL,
	  RecoveryForkID uniqueidentifier NULL,
	  Collation varchar(128) NULL,
	  FamilyGUID uniqueidentifier NULL,
	  HasBulkLoggedData bit NULL,
	  IsSnapshot bit NULL,
	  IsReadOnly bit NULL,
	  IsSingleUser bit NULL,
	  HasBackupChecksums bit NULL,
	  IsDamaged bit NULL,
	  BeginsLogChain bit NULL,
	  HasIncompleteMetaData bit NULL,
	  IsForceOffline bit NULL,
	  IsCopyOnly bit NULL,
	  FirstRecoveryForkID uniqueidentifier NULL,
	  ForkPointLSN numeric(25,0) NULL,
	  RecoveryModel varchar(60) NULL,
	  DifferentialBaseLSN numeric(25,0) NULL,
	  DifferentialBaseGUID uniqueidentifier NULL,
	  BackupTypeDescription varchar(60) NULL,
	  BackupSetGUID uniqueidentifier NULL,
	  Seq int NOT NULL identity(1,1)
    )

  --> Create temp-table for db file information
  DECLARE @filelist TABLE  (
	  LogicalName varchar(128) NULL,
	  PhysicalName varchar(260) NULL,
	  Type char(1) NULL,
	  FileGroupName varchar(128) NULL,
	  Size numeric(20,0) NULL,
	  MaxSize numeric(20,0) NULL,
	  FileID bigint NULL,
	  CreateLSN numeric(25,0) NULL,
	  DropLSN numeric(25,0) NULL,
	  UniqueID uniqueidentifier NULL,
	  ReadOnlyLSN numeric(25,0) NULL,
	  ReadWriteLSN numeric(25,0) NULL,
	  BackupSizeInBytes bigint NULL,
	  SourceBlockSize int NULL,
	  FileGroupID int NULL,
	  LogGroupGUID uniqueidentifier NULL,
	  DifferentialBaseLSN numeric(25,0) NULL,
	  DifferentialBaseGUID uniqueidentifier NULL,
	  IsReadOnly bit NULL,
	  IsPresent bit NULL,
	  Seq int NOT NULL identity(1,1)
    )

  --> Get header and filelist information from the backup file
  INSERT INTO @header
  EXEC ('RESTORE HeaderOnly FROM DISK = ''' + @Filename + '''')

  INSERT INTO @filelist
  EXEC ('RESTORE FilelistOnly FROM DISK = ''' + @Filename + '''')

  --> Generate the full backup restore script
  SELECT
    @SQL = @SQL + 
      CASE
        WHEN a.Seq = 1 THEN 
          @cr + 'RESTORE DATABASE [' + c.DatabaseName + ']' +
          @cr + 'FROM DISK =' + @cr + @tab + '''' +
          @Filename + '''' + @cr + 'WITH' 
        ELSE ''
      END
      + @cr + @tab + 'MOVE ''' + a.LogicalName + ''' TO ''' + a.PhysicalName + ''',' 
      +
      CASE 
        WHEN a.Seq = b.Seq THEN
          @cr + @tab + 'REPLACE, STATS = 5, NORECOVERY'
        ELSE ''
      END
  FROM
    @filelist a
    CROSS JOIN
      (SELECT Seq = MAX(b1.Seq) FROM @filelist b1 ) b
    CROSS JOIN
      (SELECT DatabaseName = MAX(c1.DatabaseName) FROM @header c1) c
  ORDER BY
    a.Seq
    
  SELECT @SQL = @SQL + @cr + 'GO' + @cr + @cr 
    
  --> Restore the last differential backup if it exists
  SELECT TOP 1
    @SQL = @SQL + 'RESTORE DATABASE [' + @DBName + '] FROM DISK = ''' + b.physical_device_name + ''' WITH NORECOVERY' + @cr + 'GO',
    @Diff_BackupStartDate = a.backup_start_date
  FROM msdb.dbo.backupset a
    INNER JOIN msdb.dbo.backupmediafamily b
      ON a.media_set_id = b.media_set_id
  WHERE a.database_name = @DBName
    AND a.type = 'I'
    AND a.backup_start_date > @Full_BackupStartDate
    AND a.backup_start_date < @PointInTime
  ORDER BY a.backup_start_date DESC
  
  IF @Diff_BackupStartDate IS NULL
    SET @Diff_BackupStartDate = @Full_BackupStartDate
    
  --> Generate all log restores except the last one
  SELECT 
    @SQL = @SQL + 'RESTORE LOG [' + @DBName + '] FROM DISK = ''' + b.physical_device_name + ''' WITH NORECOVERY' + @cr + 'GO' + @cr,
    @Log_BackupStartDate = a.backup_start_date   
  FROM msdb.dbo.backupset a
    INNER JOIN msdb.dbo.backupmediafamily b
      ON a.media_set_id = b.media_set_id
  WHERE a.database_name = @DBName
    AND a.type = 'L'
    AND a.backup_start_date > @Diff_BackupStartDate
    AND a.backup_start_date < @PointInTime
  ORDER BY a.backup_start_date
  
  --> Generate last log restore script with the stopat command and recovery
  SELECT TOP 1
    @SQL = @SQL + 'RESTORE LOG [' + @DBName + '] FROM DISK = ''' + b.physical_device_name + ''' 
      WITH RECOVERY, STOPAT = ''' + CONVERT(varchar(20), @PointInTime, 120) + '''' + @cr + 'GO' + @cr
  FROM msdb.dbo.backupset a
    INNER JOIN msdb.dbo.backupmediafamily b
      ON a.media_set_id = b.media_set_id
  WHERE a.database_name = @DBName
    AND a.type = 'L'
    AND a.backup_start_date > @PointInTime
  ORDER BY a.backup_start_date ASC

  PRINT @SQL

END TRY
BEGIN CATCH
  PRINT ERROR_MESSAGE()
END CATCH


I was just recently working with a client that was moving a few 100k rows of data on a daily basis from one system to another and all this data was incremental in the sense that the source data was a ticket system and there was an incrementing ID on all the sales records. Now the problem was that for various reasons not all data had been transferred in the past, a few sales records had been deleted because of void transactions (these were not so many), others were simply missing (this was a substantial bunch). All these missing data  were amounting to quite a bit of money in the sales reports so I had to find a way to identify and transfer them on a regular basis so that this problem was kept at a bare minimum.

The tables that needed to be synced had in excess of 6 mill rows and even though it’s not huge it’s more than enough to cause problems if you write some bad code. SO! With some excellent help from all my geek friends over at http://www.sqlteam.com/forums  I was able to come down with a script that did the trick…and mind you; it took less than 30 seconds to execute on the production box identifying over 500 gaps! Now since we are only talking 100k rows here the query will probably have sub-second execution time but it scales very well (the concept behind the query originally created by MVP Steve Kass, http://www.stevekass.com/)

-- Make sure insert runs a little faster
SET NOCOUNT ON
GO

-- Prepare a sample table
CREATE TABLE GapsTest (
	ID int NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
	Name varchar(50) NULL
	)
GO

-- Insert 100k rows of dummy data
INSERT INTO GapsTest (Name) VALUES ('TheFirstSQL')
GO 100000

-- Delete some rows to create gaps in the identity column
DELETE GapsTest WHERE ID IN (263, 264, 37392, 44, 98712, 66219)
DELETE GapsTest WHERE ID BETWEEN 35000 AND 36000
GO

-- Run the "magic" query
;WITH 
cte AS (
	SELECT 
		ID, 
		RowNum = ROW_NUMBER() OVER (ORDER BY ID) 
	FROM dbo.GapsTest),
cte2 AS (
	SELECT *, DENSE_RANK() OVER (ORDER BY ID - RowNum) As Series 
	FROM cte),
cte3 AS (
	SELECT *, COUNT(*) OVER (PARTITION BY Series) AS SCount 
	FROM cte2),
cte4 AS (
	SELECT 
		MinID = MIN(ID), 
		MaxID = MAX(ID), 
		Series
	FROM cte3
	GROUP BY Series)
SELECT GapStart = a.MaxID, GapEnd = b.MinID
FROM cte4 a
	INNER JOIN cte4 b
		ON a.Series+1 = b.Series
ORDER BY GapStart