database not restoring to original physical names ndf files in backup
-
Hi All,
I have a backup db and runningRESTORE FILELISTONLY FROM DISK = 'G:\Prod Backups\EDW.bak'
gives me the logical and physical paths of the ndf files, however on restore through SSMS it does not want to restore to the exact physical paths in the backup but create brand new the physical names for the ndf with the concat of db name and a sequential number suffix i.e d:\edw_01.ndf, d:\edw_02.ndf, d:\edw_03.ndf
I know I can write a restore script with the move command to do it, but there are loads of ndf files. Any solutions apart from writing the script
-
I've read about similar issues when using the GUI to restore a database with secondary file. Not sure how it can be easily addressed in the GUI but even if there are a lot of files, it seems that you could pretty easily write a script by just copying the output from restore filelistonly. You can also copy the output into excel and make changes there which can be done fairly quickly for the same changes to a large set of data - just drag a quote or comma down the column to autofill...that type of thing. It may be more doable than you think.
Sue
-
Hi Sue,
Thanks for your help..gone for the jugular and writing a script to do it...will post it when done
-
HI Sue,
Found the solution...just use direct sql in the query manager instead
i.eRESTORE DATABASE [edw] FROM DISK = 'C:\RobinWork\Backups\dbBackup_Source\.EDW.bak' WITH REPLACE, STATS = 20
this will do restore to the actual NDF physical filenames within the bak file .However in SSMS it just wants to use the concat of the dbname with a numeric sequential suffix instead...must be a bug. See image below you can clearly see the ndf names it generates
- Weird things like that is part of the reason I never use the GUI for backups, restores and plenty of other things. There are other odd things like that it does since it's trying to make it easier for anyone to perform certain tasks for every possible combination of every scenario.
T-SQL should/did use the locations from the backup file - have no idea what the GUI does to change it around.You could always run a trace while you try to do the restore with the GUI and see what it is doing.Sue
-
Hi Sue,
FYI here is my script quick and dirty ..put it in your toolbox..didn't need it, but did the work anyway
--============================================================================
-- Restore file list from from bak file to different paths
-- table to hold file list
DECLARE @tmpFileList TABLE
(
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
, SourceBlockSize INT
, FileGroupId INT
, LogGroupGUID UNIQUEIDENTIFIER
, DifferentialBaseLSN NUMERIC(25, 0)
, DifferentialBaseGUID UNIQUEIDENTIFIER
, IsReadOnly BIT
, IsPresent BIT
, TDEThumbptint VARBINARY
)
DECLARE @debug int = 0
DECLARE @restoreFromFileList varchar(8000)
DECLARE @quote varchar(10) = ''''
DECLARE @bakFilePath varchar(4000) = 'C:\RobinWork\Backups\dbBackup\EDW.bak'
set @restoreFromFileList = 'RESTORE FILELISTONLY FROM DISK = '+ @quote + @bakFilePath + + @quote
IF @debug =1
print @restoreFromFileList
-- insert into temp table
INSERT INTO @tmpFileList EXEC (@restoreFromFileList)
IF @debug =1
select * from @tmpFileList
DECLARE @dbName varchar(255) = 'mydb'
DECLARE @restoredb varchar(8000)
DECLARE @mdfLogicalName varchar(255)
DECLARE @mdfPhysicalname varchar(255) = 'C:\temp\' + @dbName + '.mdf'
DECLARE @ldfLogicalName varchar(255)
DECLARE @ldfPhysicalname varchar(255) = 'C:\temp\' + @dbName + '_log.ldf'
SELECT @mdfLogicalName = logicalname
FROM @tmpFileList
WHERE physicalname LIKE '%.mdf'
SELECT @ldfLogicalName = logicalname
FROM @tmpFileList
WHERE physicalname LIKE '%.ldf'
if @debug =1
BEGIN
print @mdfLogicalName
print @mdfPhysicalname
print @ldfLogicalName
print @ldfPhysicalname
END
SET @restoredb = ''
SET @restoredb = @restoredb + ' RESTORE DATABASE ['+ @dbName + ']'
SET @restoredb = @restoredb + ' FROM DISK = '+ @quote + @bakFilePath + @quote
SET @restoredb = @restoredb + ' WITH MOVE '+ @quote + @mdfLogicalName + @quote + ' TO '+ @quote + @mdfPhysicalname + @quote + ','
SET @restoredb = @restoredb + ' MOVE '+ @quote + @ldfLogicalName + @quote + ' TO '+ @quote + @ldfPhysicalname + @quote + ','
if @debug=1
print @restoredb
-- table to hold ndf files
DECLARE @tmpNDFFiles TABLE
(
LogicalName varchar(255)
, Physicalname varchar(255)
, rowid int identity
)
INSERT @tmpNDFFiles(LogicalName,Physicalname)
SELECT LogicalName , PhysicalName
FROM @tmpFileList
WHERE physicalname LIKE '%.ndf'
if @debug=1
select * from @tmpNDFFiles
DECLARE @restoreNDF varchar(8000) = ''
DECLARE @counter int
DECLARE @ndfLogicalName varchar(255)
DECLARE @ndfPhysicalPath varchar(255) = 'c:\temp'
SET @counter = 0
WHILE @counter < (SELECT COUNT(*) from @tmpNDFFiles)
BEGIN
SET @counter = @counter + 1
SELECT @ndfLogicalName = logicalname
FROM @tmpNDFFiles
WHERE rowid = @counter
SET @restoreNDF = @restoreNDF + ' MOVE '+ @quote + @ndfLogicalName + @quote + ' TO '+ @quote + @ndfPhysicalPath + '\' + @ndfLogicalName + '.ndf' + @quote + ',' + CHAR(13) + CHAR(10)
END
-- Add the tail
SET @restoreNDF = @restoreNDF + ' REPLACE, STATS = 20 '
if @debug= 1
print @restoreNDF
DECLARE @totalRestore varchar(max) = @restoredb + @restoreNDF
print @totalRestore
exec(@totalRestore)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply