RestoreScriptGenerateFromBak

The syntax for restoring a database file is simple if you have the same paths for the file locations between the source and destination servers. 

However if the database file locations in the source and destination servers are not the same, the RESTORE DATABASE syntax will have to include WITH MOVE which requires that you know the logical name of each file.

The logical name can be retrieved with this command.  Note it will also indicate whether the file is a database or log file.

RESTORE FILELISTONLY

FROM Disk = 'E:\Backups\CCS.bak'

Here’s a stored procedure which will generate the RESTORE DATABASE script when you pass in the location of the backup file.

It has one dependency which is easy to install:

        udfGetFilenameOnly

This script is especially handy when you have a large number of data or log files underlying your database.  The script allows you to pass in a path for Data and Log files.  If you need them to be on different paths, simply change them manually after the script is generated.

After running the proc, the script is shown on the Messages pane.  

Use Admin

GO

CREATE PROC dbo.RestoreScriptGenerateFromBak

      @PathFilename varchar(300),

      @NewDBName varchar(100),

      @DataPath varchar(200),

      @LogPath varchar(200)

AS

/*    DATE        AUTHOR            REMARKS

      2/5/12            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 useful when making a dev copy of a multi-file

                  production database.

     

      DEPENDENCIES

            Admin.dbo.udfGetFilenameOnly()

            RESTORE FILELISTONLY

     

      USAGE

            Exec RestoreScriptGenerateFromBak

                  'D:\Backup\ccs.bak',

                  'CCS',

                  'D:\',

                  'C:\Paul\Sql'

            Exec RestoreScriptGenerateFromBak

                  'K:\Backups\Prod01\MyDB_2012-02-03.bak',

                  'MyDB',

                  'M:\Data',

                  'L:\Logs'

      DEBUG

            RESTORE FILELISTONLY

            FROM DISK = 'E:\Sql\Backup\MyDB_2012-02-03.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