Restoring databases to a set drive and directory
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
DropLSN numeric(25, 0),
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.
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
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
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
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)
IF (@@FETCH_STATUS <> -2) -- As long as there are no rows missing
select @RestoreString =
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' +''''
--Increment the counter
set @counter = @counter + 1
FETCH NEXT FROM MY_CURSOR INTO @LogicalName
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.
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.