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

HenrikSHenrikS Posts: 5 New member
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
D:\MSSQL\MSSQL13.MSSQLSERVER\MSSQL\DATA\Henrik_Test_Ticket_6175_Dest2.mdf







Tagged:

Best Answer

Answers

  • 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, 
    	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
Sign In or Register to comment.