Restore Database with New Names (T-SQL)





USE [master]
GO



BEGIN


DECLARE @Destinationdb    AS NVARCHAR(255)= 'myDB', --Database Name to be restored
        @fullbackup       AS NVARCHAR(2000),
        @diffbackup       AS NVARCHAR(2000)=NULL,
        @logbackup1       AS NVARCHAR(2000)=NULL,
        @logbackup2       AS NVARCHAR(2000)=NULL,
	@logbackup3       AS NVARCHAR(2000)=NULL,
        @desPathData      AS NVARCHAR(2000) ='C:\MSSQL\Data\',
        @desPathLog        AS NVARCHAR(2000) ='C:\MSSQL\Log\' ,
        @backupSourcePath AS NVARCHAR(2000) ='C:\Backup\',
	--Check filestream feature
	@includeFilestream bit=0

----1.Full backup file
SET @fullbackup = N'MyDB_FULL.bak'

----2.Most recent Diff backup since Full backup (Optional)
--SET @diffbackup = N'MyDB_DIFF_20120712_080511.bak'
----3. Moste recent Log backups since Diff backup (Optional and upto three log files) 
--SET @logbackup1 = N'MyDB_LOG_20120712_083107.trn' 
----SET @logbackup2 = N'MyDB_LOG_20120711_144502.trn' 
----SET @logbackup3 = N'MyDB_LOG_20120711_150002.trn' 


/***************************************************************/


  --Full path to backup files

SET @fullbackup = @backupSourcePath +  @fullbackup
SET @diffbackup = @backupSourcePath +  @diffbackup
SET @logbackup1 = @backupSourcePath +  @logbackup1
SET @logbackup2 = @backupSourcePath +  @logbackup2
SET @logbackup3 = @backupSourcePath +  @logbackup3




DECLARE @CreateNewDatabaseSQL AS NVARCHAR(4000),
        @cmdFull              AS NVARCHAR(2000),
        @cmdDiff              AS NVARCHAR(2000),
        @cmdLog1              AS NVARCHAR(2000),
        @cmdLog2              AS NVARCHAR(2000),
	@cmdLog3              AS NVARCHAR(2000),
        @cmdFinal             AS NVARCHAR(2000),
        @cmdGetFileNames      AS NVARCHAR(1000),
        @cmdUpdateLogicalName NVARCHAR(2000),
        @s                    VARCHAR(128) 



--Check if the Destination Database exists
IF NOT EXISTS (SELECT 1
               FROM   sys.sysdatabases
               WHERE  name = @destinationdb)
  BEGIN
      SET @CreateNewDatabaseSQL='CREATE DATABASE [' + @destinationdb
               + '] ON  PRIMARY ( NAME = N'''
               + @destinationdb + ''', FILENAME = N'''
               + @desPathData + @destinationdb
               + '.mdf'')'
             If  (@includeFilestream<>0)  
               begin
               SET @CreateNewDatabaseSQL=@CreateNewDatabaseSQL+', FILEGROUP  ['
               + @destinationdb + '] CONTAINS FILESTREAM  DEFAULT (NAME = N'''  + @destinationdb + '_filestream'', FILENAME = N''' + @desPathData + @destinationdb + '_filegroup'')'
               end
               
         SET @CreateNewDatabaseSQL=@CreateNewDatabaseSQL+' LOG ON ( NAME = N''' + @destinationdb
               + '_log'', FILENAME =  N''' + @desPathLog
               + @destinationdb + '_log.ldf'')'
--print @CreateNewDatabaseSQL
EXEC (@CreateNewDatabaseSQL)
  END





CREATE TABLE #backupdetails
  (
     LogicalName          NVARCHAR(255),
     PhysicalName         NVARCHAR(255),
     Type                 NVARCHAR(1),
     FileGroupName        NVARCHAR(255),
     Size                 BIGINT,
     MaxSize              BIGINT,
     FileId               INT NULL,
     CreateLSN            NUMERIC(25, 0) NULL,
     DropLSN              NUMERIC(25, 0) NULL,
     UniqueFileId         UNIQUEIDENTIFIER NULL,
     readonlyLSN          NUMERIC(25, 0) NULL,
     readwriteLSN         NUMERIC(25, 0) NULL,
     BackupSizeInBytes    BIGINT NULL,
     SourceBlkSize        INT NULL,
     FileGroupId          INT NULL,
     LogGroupGuid         UNIQUEIDENTIFIER NULL,
     DifferentialBaseLsn  NUMERIC(25, 0) NULL,
     DifferentialBaseGuid UNIQUEIDENTIFIER NULL,
     IsReadOnly           BIT NULL,
     IsPresent            BIT NULL,
     --SQL Server 2008 Only
     TDEThumbprint        VARBINARY(32) NULL
  ) 





SET @cmdGetFileNames ='RESTORE FILELISTONLY FROM DISK='''
                      + @fullbackup + ''''

INSERT #backupdetails
EXEC(@cmdGetFileNames) 



-- build the restore command
SELECT @cmdFull = NULL,
       @cmdUpdateLogicalName = NULL,
       @s = ''

WHILE @s < (SELECT Max(LogicalName)
            FROM   #backupdetails)
  BEGIN
      BEGIN
          SELECT @s = Min(LogicalName)
          FROM   #backupdetails
          WHERE  LogicalName > @s

   
		      SELECT @cmdFull = COALESCE(@cmdFull + ', MOVE ', '') + '''' + @s
                        + ''' to ''' + CASE WHEN #backupdetails.type = 'D' THEN ''+ @desPathData+'' WHEN #backupdetails.type = 'L' THEN ''+ @desPathLog+'' WHEN #backupdetails.type = 'S' THEN ''+ @desPathData+''ELSE ''+ @desPathData+''  END +                        
                        @destinationdb + CASE WHEN #backupdetails.type = 'D' THEN '.mdf' WHEN #backupdetails.type = 'L' THEN '.ldf' ELSE'' +@s+''  END + ''''
          FROM   #backupdetails
          WHERE  LogicalName = @s
      END

      BEGIN
          SELECT @cmdUpdateLogicalName = COALESCE(@cmdUpdateLogicalName, '')
                                         + ' 
                                         ALTER DATABASE [' + @destinationdb
                                         + ']
                 MODIFY FILE (NAME= [' + @s + '], NEWNAME=[' + @destinationdb + ''+ CASE WHEN #backupdetails.type = 'D' THEN '] ' WHEN #backupdetails.type = 'L' THEN '_Log] '  WHEN #backupdetails.type = 'S' THEN '_FileGroup]' ELSE '-' END + ')'
          FROM   #backupdetails
          WHERE  LogicalName = @s
                 AND LogicalName <> @destinationdb + CASE WHEN #backupdetails.type = 'D' THEN '' WHEN #backupdetails.type = 'L' THEN '_Log' WHEN #backupdetails.type = 'S' THEN '_FileGroup'  ELSE '' END
      END
  END


  SELECT @cmdFull = 'Restore database ' + @destinationdb
                  + ' from disk = ''' + @fullbackup
                  + ''' WITH REPLACE,NORECOVERY,  MOVE '
                  + @cmdFull

IF ( @diffbackup IS NOT NULL )
  BEGIN
      SELECT @cmdDiff = 'Restore database ' + @destinationdb
                        + ' from disk = ''' + @diffbackup
                        + ''' WITH NORECOVERY'
  END

IF ( @logbackup1 IS NOT NULL )
  BEGIN
      SELECT @cmdLog1 = 'Restore database ' + @destinationdb
                           + ' from disk = ''' + @logbackup1
                           + ''' WITH NORECOVERY'
  END

IF ( @logbackup2 IS NOT NULL )
  BEGIN
      SELECT @cmdLog2 = 'Restore database ' + @destinationdb
                           + ' from disk = ''' + @logbackup2
                           + ''' WITH NORECOVERY'
  END

  IF ( @logbackup3 IS NOT NULL )
  BEGIN
      SELECT @cmdLog3 = 'Restore database ' + @destinationdb
                           + ' from disk = ''' + @logbackup3
                           + ''' WITH NORECOVERY'
  END

SELECT @cmdFinal = 'Restore database ' + @destinationdb
                   + ' WITH RECOVERY' 


DROP TABLE #backupdetails 


DECLARE @changeMode AS NVARCHAR(255)

--Change to Single-user mode
SET @changeMode='USE [' + @destinationdb
                + ']    ALTER DATABASE [' + @destinationdb
                + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE '

EXEC(@changeMode)
--Restore Full backup with NORecovery to continue
EXEC (@cmdFull)

--Restore Diff backup 
IF ( @cmdDiff IS NOT NULL )
  EXEC (@cmdDiff)


--Restore Log backup 
IF ( @logbackup1 IS NOT NULL )
  EXEC (@cmdLog1)
--Restore Log backup2 
  IF ( @logbackup2 IS NOT NULL )
  EXEC (@cmdLog2)
  --Restore Log backup3
IF ( @logbackup3 IS NOT NULL )
  EXEC (@cmdLog3)


--Restore with Recovery to finish restore process
EXEC (@cmdFinal) 



--Update Logical names for the new database
EXEC (@cmdUpdateLogicalName)

--Change to RECOVERY SIMPLE
SET @changeMode='USE [' + @destinationdb
                + ']    ALTER DATABASE [' + @destinationdb
                + '] SET RECOVERY SIMPLE'

EXEC(@changeMode)

--Change to Single-user mode
SET @changeMode='USE [' + @destinationdb
                + ']    ALTER DATABASE [' + @destinationdb
                + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE  CHECKPOINT  DBCC SHRINKFILE (2, 500);'

EXEC(@changeMode)

--Change back to multiple user mode
SET @changeMode='ALTER DATABASE [' + @destinationdb
                + '] SET MULTI_USER'

EXEC(@changeMode)

--Change to RECOVERY FULL
SET @changeMode='USE [' + @destinationdb
                + ']    ALTER DATABASE [' + @destinationdb
                + '] SET RECOVERY FULL'

--EXEC(@changeMode) 


END


Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s