15 April 2013 18:18 okeofs

Restoring databases to a set drive and directory

 

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.

Comments

# Dew Drop &ndash; April 16, 2013 (#1,528) | Alvin Ashcraft&#039;s Morning Dew

Pingback from  Dew Drop &ndash; April 16, 2013 (#1,528) | Alvin Ashcraft&#039;s Morning Dew