About Me

Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group for 11 years, from January 2009 to 2020.

Wednesday, April 16, 2014

RestoreScriptGenerateFromBak - Generate restore script from backup file

Here’s a handy-dandy proc for restoring a database backup file.  You simply pass in the path of the backup, your new DB name, and the locations for data and log files. 

This is especially helpful when your database has multiple files (10, 20, 50 files) due to partitioning or separated file groups.  But even if you only have 2 files, it is still a time-saver.


USE Admin
IF OBJECT_ID('dbo.RestoreScriptGenerateFromBak') Is Not Null
      DROP PROC dbo.RestoreScriptGenerateFromBak
GO

CREATE PROC dbo.RestoreScriptGenerateFromBak
      @PathFilename varchar(300),
      @NewDBName varchar(100),
      @DataPath varchar(200),
      @LogPath varchar(200)
AS
/*    DATE        AUTHOR            REMARKS
      4/16/14           PPaiva            Initial creation.
     
      DESCRIPTION
            Creates a restore script for a given backup file
                  with a common specified destination folder for data
                  files, and another for log files.
            This is especially useful when making a copy of a multi-file
                  (10, 50, 100+ files) database, as in the case of
                  partitioning and/or multiple file groups.
            Click on Messages tab to review script.
     
      DEPENDENCIES
            Admin.dbo.udfGetFilenameOnly()
            RESTORE FILELISTONLY
     
      USAGE
            Exec RestoreScriptGenerateFromBak
                  'C:\Sql\Bak\ProdDB_20121116.bak',
                  'MyDevDB',
                  'E:\Sql\Data',
                  'L:\Sql\Logs'

            Exec RestoreScriptGenerateFromBak
                  'C:\Paul\SqlDataFromClient\Sopa\Sopa_Feb27.bak',
                  'SopaFeb27',
                  'E:\Data',
                  'F:\Logs'

      DEBUG
            RESTORE FILELISTONLY
            FROM DISK = 'C:\Paul\SqlDataFromClient\Sopa\Sopa_Feb27.bak'
           
*/
SET NOCOUNT ON

DECLARE @Sql varchar(Max),
            @crlf varchar(2),
            @Path varchar(200)
           
SET @crlf = CHAR(13) + CHAR(10)
SET @DataPath = dbo.udfAddBackslashIfNec(@DataPath)
SET @LogPath = dbo.udfAddBackslashIfNec(@LogPath)


CREATE TABLE #FileList(
      LogicalName nvarchar(128) NOT NULL PRIMARY KEY,
      PhysicalName nvarchar(260),
      Type char(1),
      FileGroupName nvarchar(128),
      Size numeric(20,0),
      MaxSize numeric(20,0),
      FileID bigint,
      CreateLSN numeric(22,0),
      DropLSN numeric(25,0),
      UniqueID uniqueidentifier,
      ReadOnlyLSN numeric(25,0),
      ReadWriteLSN numeric(25,0),
      BackupSizeInBytes bigint,
      SourceBlockSize int,
      FileGroupID int,
      LogGroupGUID uniqueidentifier,
      DifferentialBaseLSN numeric(25,0),
      DifferentialBaseGUID uniqueidentifier,
      IsReadOnly bit,
      IsPresent bit,
      TDEThumbprint varbinary(32)
      )

-- Determine version of SQL
DECLARE @Version varchar(10) 
SELECT @Version = Convert(varchar(10), ServerProperty('ProductVersion'))

IF Left(@Version, 2) = '9.'
      ALTER TABLE #FileList DROP COLUMN TDEThumbprint
     
SET @Sql = 'RESTORE FILELISTONLY FROM DISK = ''' + @PathFilename + ''''
     
INSERT INTO #FileList
      Exec (@Sql)

SELECT *
FROM #FileList
ORDER BY FileID

DECLARE @LogicalName nvarchar(128),
            @Filename varchar(300),
            @FileID int,
            @MaxFileID int,
            @Type char(1),
            @NumIterations int

SELECT  @MaxFileID = MAX(FileID),
            @FileID = 0,
            @NumIterations = 0,
            @Sql = 'RESTORE DATABASE [' + @NewDBName + ']
FROM DISK = ''' + @PathFilename + '''
WITH '
FROM #FileList

WHILE @FileID < @MaxFileID
      BEGIN
            SET @NumIterations = @NumIterations + 1
     
            -- Get next file
            SELECT @FileID = MIN(FileID)
            FROM #FileList
            WHERE FileID > @FileID
     
            -- Get other attribs for this FileID
            SELECT  @LogicalName = LogicalName,
                        @Filename = dbo.udfGetFilenameOnly(PhysicalName),
                        @Type = Type
            FROM #FileList
            WHERE FileID = @FileID
     
            IF @NumIterations = 1
                  SET @Sql = @Sql
            ELSE
                  SET @Sql = @Sql + '     '
     
            --SELECT @Type Type, @DataPath DataPath, @LogPath LogPath
                 
            IF @Type = 'D'
                  SET @Path = @DataPath
            ELSE
                  BEGIN
                        SET @Path = @LogPath
           
                        -- Force suffix to be .ldf if a log file
                        SET @Filename = REPLACE(@Filename, '.ndf', '.ldf')
                        SET @Filename = REPLACE(@Filename, '.mdf', '.ldf')
                  END
           
            SET @Sql = @Sql + 'MOVE ''' + @LogicalName + ''' TO ''' +  @Path + @NewDBName + '_' + @Filename + ''',' + @crlf
           
     
      END

-- Remove last comma
SET @Sql = LEFT(@Sql, len(@Sql) - 3)

Print @Sql
     




No comments:

Post a Comment