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

HenrikSHenrikS Posts: 5 New member

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
,move logfiles to [E:\MSSQL\MSSQL13.MSSQLSERVER\MSSQL\Logs\]

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:


Best Answer


  • sam.blackburnsam.blackburn Posts: 216 Gold 1
    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, 
    	ReadOnlyLSN INT, 
    	ReadWriteLSN INT, 
    	BackupSizeInBytes BIGINT, 
    	SourceBlockSize INT, 
    	FileGroupID INT, 
    	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
Sign In or Register to comment.