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.
