Restore a Database and give the filenames like the database name?

Answers

• Options

  sam.blackburn Redgate › Posts: 224 Gold 2

  July 25, 2018 4:31PM edited July 25, 2018 4:31PM

  I work on SQL Compare, so I'm not exactly a Backup expert, but our test framework restores an enormous number of backups.  
  
  The filegroup paths are handled by first calling SQL Backup with `RESTORE HEADERLISTONLY`, which will list the files without doing an actual restore.  We use the result of this command to craft a `RESTORE WITH MOVE` script. 
  
  Unfortunately the code is difficult to post here because it's baked into the C# test framework, with loads of special cases for escaping filenames, multipart backups, SQB versus native, Linux versus Windows, network flakiness, caching layers and so on.  
  
  I'd be really interested to know if anyone has found a succinct way to do it.  Here's a quick stab I made at doing it in SQL:
  

  DECLARE @backupFile AS NVARCHAR(MAX) = 'C:\path\to\backup.sqb'
  DECLARE @pathOnServer AS NVARCHAR(MAX) = 'C:\Foo\Bar\Baz'
  DECLARE @listCommand AS NVARCHAR(MAX) = 'RESTORE FILELISTONLY FROM DISK=''' + @backupFile + ''''
  CREATE TABLE ##files (
  	LogicalName NVARCHAR(MAX), 
  	PhysicalName NVARCHAR(MAX), 
  	Type CHAR, 
  	FileGroupName NVARCHAR(MAX), 
  	Size BIGINT, 
  	MaxSize BIGINT, 
  	FileID INT, 
  	CreateLSN INT, 
  	DropLSN INT, 
  	UniqueID UNIQUEIDENTIFIER, 
  	ReadOnlyLSN INT, 
  	ReadWriteLSN INT, 
  	BackupSizeInBytes BIGINT, 
  	SourceBlockSize INT, 
  	FileGroupID INT, 
  	LogGroupGUID UNIQUEIDENTIFIER, 
  	DifferentialBaseLSN BIGINT, 
  	DifferentialBaseGUID UNIQUEIDENTIFIER, 
  	IsReadOnly BIT, 
  	IsPresent BIT
  )
  INSERT INTO ##files EXEC master.dbo.sqlbackup @listCommand
  -- SELECT * FROM ##files
  SELECT 'MOVE N''' + LogicalName + ''' TO ''' + @pathOnServer + '\' + LogicalName + (CASE Type WHEN 'D' THEN '.mdf' WHEN 'L' THEN '.ldf' ELSE '' END) + '''' FROM ##files
  DROP TABLE ##files
  

  Hope that helps!

  
  

  Software Developer
  Redgate Software

  0 · Share on Twitter