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

HenrikS

Hi,

i have to restore some databases for development reasons on a regular base. 
i dont want to script the "with move" for every database. i search for a generic script. Actual i have:


EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE [Henrik_Test_Ticket_6175_Dest2]
FROM DISK = [D:\MSSQL\MSSQL13.MSSQLSERVER\MSSQL\Backup\FULL_(local)_Henrik_Test_Ticket_6175_*.sqb]
SOURCE = [Henrik_Test_Ticket_6175] LATEST_ALL
with move DATAFiles to [D:\MSSQL\MSSQL13.MSSQLSERVER\MSSQL\DATA\]
,move logfiles to [E:\MSSQL\MSSQL13.MSSQLSERVER\MSSQL\Logs\]
,Replace
"'

But this brings me an error message:

SQL error 3156: File 'Henrik_Test_Ticket_6175_log' cannot be restored to 'E:\MSSQL\MSSQL13.MSSQLSERVER\MSSQL\Logs\Henrik_Test_Ticket_6175_log.ldf'. Use WITH MOVE to identify a valid location for the file.
SQL error 1834: The file 'E:\MSSQL\MSSQL13.MSSQLSERVER\MSSQL\Logs\Henrik_Test_Ticket_6175_log.ldf' cannot be overwritten.  It is being used by database 'Henrik_Test_Ticket_6175'.

What can i do, to create or replace a destination database with filenames like:
E:\MSSQL\MSSQL13.MSSQLSERVER\MSSQL\Logs\Henrik_Test_Ticket_6175_Dest2_log.ldf 
and
\MSSQL\MSSQL13.MSSQLSERVER\MSSQL\DATA\Henrik_Test_Ticket_6175_Dest2.mdf

HenrikS

i was wrong

sam.blackburn

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!