path manipulation - Page 170

Restoring databases to a set drive and directory

- by okeofs

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.

Search Results

Search found 15811 results on 633 pages for 'path manipulation'.

Page 170/633 | < Previous Page | 166 167 168 169 170 171 172 173 174 175 176 177 | Next Page >

- by okeofs

- by Parhs

- by bozalina

- by Mattousai

- by user560084

- by Totumus Maximus

- by pmneve

- by pmneve

- by Nicklamort

- by Sergei

- by Sergei

- by Martin Deh

- by ybbest

- by ybbest

- by Jeff Victor

- by Madhur Ahuja

- by GeorgeCalm

- by michael

- by Hedge

- by andrzejp

- by Yuval

- by ajsie

- by pstanton

< Previous Page | 166 167 168 169 170 171 172 173 174 175 176 177 | Next Page >