Restoring databases to a set drive and directory

Introduction

Often people say that necessity is the mother of invention. In this case I was faced with the dilemma of having to restore several databases, with multiple ‘ndf’ files, and having to restore them with different physical file names, drives and directories on servers other than the servers from which they originated.

As most of us would do, I went to Google to see if I could find some code to achieve this task and found some interesting snippets on Pinal Dave’s website.

Naturally, I had to take it further than the code snippet, HOWEVER it was a great place to start.

Creating a temp table to hold database file details

First off, I created a temp table which would hold the details of the individual data files within the database. Although there are a plethora of fields (within the temp table below), I utilize LogicalName only within this example. The temporary table structure may be seen below:

create table #tmp

(

LogicalName nvarchar(128)

,PhysicalName nvarchar(260)

,Type char(1)

,FileGroupName nvarchar(128)

,Size numeric(20,0)

,MaxSize numeric(20,0),

Fileid tinyint,

CreateLSN numeric(25,0),

DropLSN numeric(25, 0),

UniqueID uniqueidentifier,

ReadOnlyLSN numeric(25,0),

ReadWriteLSN numeric(25,0),

BackupSizeInBytes bigint,

SourceBlocSize int,

FileGroupId int,

LogGroupGUID uniqueidentifier,

DifferentialBaseLSN numeric(25,0),

DifferentialBaseGUID uniqueidentifier,

IsReadOnly bit,

IsPresent bit,

TDEThumbPrint varchar(50)

)

We now declare and populate a variable(@path), setting the variable to the path to our SOURCE database backup.

declare @path varchar(50)

set @path = 'P:\DATA\MYDATABASE.bak'

From this point, we insert the file details of our database into the temp table. Note that we do so by utilizing a restore statement HOWEVER doing so in ‘filelistonly’ mode.

insert #tmp

EXEC ('restore filelistonly from disk = ''' + @path + '''')

At this point, I depart from what I gleaned from Pinal Dave.

I now instantiate a few more local variables. The use of each variable will be evident within the cursor (which follows):

Declare @RestoreString as Varchar(max)

Declare @NRestoreString as NVarchar(max)

Declare @LogicalName  as varchar(75)

Declare @counter as int

Declare @rows as int

set @counter = 1

select @rows = COUNT(*) from #tmp  -- Count the number of records in the temp

                                   -- table

Declaring and populating the cursor

At this point I do realize that many people are cringing about the use of a cursor. Being an Oracle professional as well, I have learnt that there is a time and place for cursors.

I would remind the reader that the data that will be read into the cursor is from a local temp table and as such, any locking of the records (within the temp table) is not really an issue.

DECLARE MY_CURSOR Cursor

FOR

Select LogicalName

From #tmp

Parsing the logical names from within the cursor.

A small caveat that works in our favour,  is that the first logical name (of our database) is the logical name of the primary data file (.mdf). Other files, except for the very last logical name, belong to secondary data files. The last logical name is that of our database log file.

I now open my cursor and populate the variable @RestoreString

Open My_Cursor

set @RestoreString =

'RESTORE DATABASE [MYDATABASE] FROM DISK = N''P:\DATA\ MYDATABASE.bak''' + ' with  '

We now fetch the first record from the temp table.

Fetch NEXT FROM MY_Cursor INTO @LogicalName

While there are STILL records left within the cursor, we dynamically build our restore string. Note that we are using concatenation to create ‘one big restore executable string’.

Note also that the target physical file name is hardwired, as is the target directory.

While (@@FETCH_STATUS <> -1)

BEGIN

IF (@@FETCH_STATUS <> -2) -- As long as there are no rows missing

select @RestoreString =

case

when @counter = 1 then -- This is the mdf file

   @RestoreString + 'move  N''' + @LogicalName + '''' + ' TO N’’X:\DATA1\'+ @LogicalName + '.mdf' + '''' + ', '

-- OK, if it passes through here we are dealing with an .ndf file

-- Note that Counter must be greater than 1 and less than the number of rows.

when @counter > 1 and @counter < @rows then -- These are the ndf file(s)

   @RestoreString + 'move  N''' + @LogicalName + '''' + ' TO N’’X:\DATA1\'+ @LogicalName + '.ndf' + '''' + ', '

-- OK, if it passes through here we are dealing with the log file

When @LogicalName like '%log%' then

   @RestoreString + 'move  N''' + @LogicalName + '''' + ' TO N’’X:\DATA1\'+ @LogicalName + '.ldf' +''''

end

--Increment the counter

set @counter = @counter + 1

FETCH NEXT FROM MY_CURSOR INTO @LogicalName

END

At this point we have populated the varchar(max) variable @RestoreString with a concatenation of all the necessary file names. What we now need to do is to run the sp_executesql stored procedure, to effect the restore.

First, we must place our ‘concatenated string’ into an nvarchar based variable. Obviously this will only work as long as the length of @RestoreString is less than varchar(max) / 2.

set @NRestoreString = @RestoreString

EXEC sp_executesql @NRestoreString

Upon completion of this step, the database should be restored to the server.

I now close and deallocate the cursor, and to be clean, I would also drop my temp table.

CLOSE MY_CURSOR

DEALLOCATE MY_CURSOR

GO

Conclusion

Restoration of databases on different servers with different physical names and on different drives are a fact of life. Through the use of a few variables and a simple cursor, we may achieve an efficient and effective way to achieve this task.