TheFirstSQL

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

NOTE: in order to use the functions discussed below you will need to enable Master Data Services on your server and install a version of the MDS database. You *do not* need to configure the Master Data Manager website or anything else, and since the MDS is not a Windows Service it will not create any additional overhead on your server. Just run the .msi and create the database and how you do this is described here: http://thefirstsql.com/2011/02/08/how-to-enable-master-data-services-mds-in-sql-server-2008-r2/

I’ve only recently begun investigating the new Master Data Services in SQL Server 2008 R2 and I have to say that I was completely baffled at some of the new functionality!! MDS is regarded as a Data Management tool and is usually only used in Business Intelligence projects. And it was for this purpose I started investigating, but I soon realised that some of the functions that come with MDS has a far greater potential than just in BI projects. The following functions comes out-of-the-box when you install an instance of Master Data Services:

• mdq.NGrams
• mdq.RegexReplace
• mdq.RegexExtract
• mdq.RegexSplit
• mdq.RegexIsMatch
• mdq.Similarity
• mdq.RegexIsValid
• mdq.SimilarityDate
• mdq.RegexMask
• mdq.Split

Now, I have to admit that I can’t see the real usage value for all of these but I find mdq.RegexIsMatch, mdq.Similarity and mdq.Split to be reeeeeaally interesting :) They can solve a whole bunch of problems that previously had no solution at all or that required some cumbersome workarounds.

mdq.RegexIsMatch

There is no doubt about it; I suck at RegEx! Always when I want to do some complex matching I have to search the web for hours to find an example that I can copy and that’s kind of embarrassing. Nevertheless, this new function makes it possible to use RegEx in sql server without having to create your own clr assembly and that really helps! Here is how you use the new function (be aware that you’ll have to install the MDS database first):

--> Usage: mdq.RegexIsMatch (input,pattern,mask)
declare @table table (
	email varchar(200))
insert into @table values ('my.sample@mail.com'),('15.sample@mail.com'), ('my.sample@mail.c')

select email, isvalid = mdq.RegexIsMatch(email, N'^([a-zA-Z0-9_\-\.]+)@((\[[0-9]{1,3} \.[0-9]{1,3}\.[0-9]{1,3}\.)|(([a-zA-Z0-9\-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)+$', 0)
from @table
--> Result
email	isvalid
my.sample@mail.com	1
15.sample@mail.com	1
my.sample@mail.c	0

A rediculously long regex expression but it works!

mdq.Similarity

The coolest of the new functions BY FAR is the mdq.Similarity function. It’s not something you’ll use every day, but when you need it you *really* need it! What it basically does is to compare two strings and score their similarity from 0.0 to 1.0 and the higher the number is the more alike the two strings are. This is extremely useful when you for example need to merge two tables together or you need to find closely matching records and you don’t have a full-text index available. The function supports 4 different matching algorithms:

• Levenshtein edit distance algorithm (0)
• Jaccard similarity coefficient algorithm (1)
• A form of the Jaro-Winkler distance algorithm (2)
• Longest common subsequence algorithm (3)

I will not go in to detail about each algorithm, but to my (limited) experience it seems that the Jaro-Winkler distance algorithm (3) will give the best result when comparing name records which is what I have done so far. Here’s how you would use it:

--> Usage: mdq.Similarity (input1,input2,method,containmentBias , [ minScoreHint ] )
SELECT mdq.Similarity(N'The First SQL', N'TheFirstSQL', 2, 0.0, 0.0);
--> Result:
(No column name)
0,964102564102564

mdq.Split

The Split function is widely used in programming and in SQL Server there are numerous implementations that span from custom clr-functions to all kinds of user defined functions. And the mdq.Split works more or less exactly as you expect it to, except it has some additional functionality. You can for example use regular expressions inside the split function:

--> Usage: mdq.Split (input,separators,removeEmpty,tokenPattern,[mask])
select * from mdq.split('the, first, sql, rules', ', ', 1, '', 0)
--> Result:
Sequence	Token	IsValid
1	the	1
2	first	1
3	sql	1
4	rules	1

Pretty cool, eh?? :)