Search code examples
sql-serverbatch-filerestore

Restore SQL Server database without knowing the Logical File Names

I've got a batch file that I use to restore databases. These are .bak files created by other people outside our department, and the database names are predictably unpredictable, as they are usually named for our various customers.

SET servername=XXXXXX
SET mssqldir=C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL
SET datapath=%mssqldir%\DATA
SET dbfile=%~1    

SqlCmd -E -S %servername% -Q "RESTORE DATABASE [MyDatabase] FROM DISK = N'%dbfile%' WITH FILE = 1, MOVE 'Customer' TO N'%datapath%\Customer.mdf', MOVE 'Customer_log' TO '%datapath%\Customer.ldf', NOUNLOAD, STATS = 10"

As stated above, the problem with the above command is that Customer is not always Customer.

So when I run it, I get something like:

Msg 3234, Level 16, State 2, Server XXXXXX, Line 1
Logical file 'Customer' is not part of database 'MyDatabase'. Use RESTORE FILELISTONLY to list the logical file names.

Msg 3013, Level 16, State 1, Server XXXXXX, Line 1
RESTORE DATABASE is terminating abnormally.

If I try to restore without the MOVE clauses, the restore tries to put files "back" where they originally came from--like paths that contain other people's home directory:

Msg 5133, Level 16, State 1, Server XXXXXX, Line 1
Directory lookup for the file "C:\TEMP\Not.Me\WidgetsRUs.mdf" failed with the operating system error 2(The system cannot find the file specified.).

I'm hoping there is a magic way to basically say: MOVE '*.*' TO '"%datapath%'

Any ideas?

Solution

  • As suggested, Use RESTORE FILELISTONLY to get database and log name

    @echo off
SetLocal EnableDelayedExpansion EnableExtensions

set "servername=XXXXXX"
set "mssqldir=C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL"
set "datapath=%mssqldir%\DATA"
set "dbfile=%~1"
set "command=" & set "restore=" 
set "database=" & set "databaselog="

set "command=%command%DECLARE @Table TABLE (LogicalName varchar(128),[PhysicalName] varchar(128),[Type] varchar,[FileGroupName] varchar(128),"
set "command=%command%[Size] varchar(128),[MaxSize] varchar(128),[FileId] varchar(128),[CreateLSN] varchar(128),[DropLSN] varchar(128),"
set "command=%command%[UniqueId] varchar(128),[ReadOnlyLSN] varchar(128),[ReadWriteLSN] varchar(128),[BackupSizeInBytes] varchar(128),"
set "command=%command%[SourceBlockSize] varchar(128),[FileGroupId] varchar(128),[LogGroupGUID] varchar(128),[DifferentialBaseLSN] varchar(128),"
set "command=%command%[DifferentialBaseGUID] varchar(128),[IsReadOnly] varchar(128),[IsPresent] varchar(128),[TDEThumbprint] varchar(128));"
set "command=%command%DECLARE @LogicalNameData varchar(128),@LogicalNameLog varchar(128);"
set "command=%command%INSERT INTO @table EXEC('RESTORE FILELISTONLY FROM DISK='''+'%dbfile%'+''' ');"
set "command=%command%SET @LogicalNameData=(SELECT LogicalName FROM @Table WHERE Type='D');"
set "command=%command%SET @LogicalNameLog=(SELECT LogicalName FROM @Table WHERE Type='L');"
set "command=%command%SELECT @LogicalNameData,@LogicalNameLog;"

set "restore=%restore%RESTORE DATABASE [MyDatabase] FROM DISK = N'%dbfile%' WITH FILE = 1, "
set "restore=%restore%MOVE '%database%' TO N'%datapath%\Customer.mdf', "
set "restore=%restore%MOVE '%databaselog%' TO '%datapath%\Customer.ldf', "
set "restore=%restore%NOUNLOAD, STATS = 10"

for /f "skip=2 usebackq tokens=1,2* delims= " %%a in (`sqlcmd -h-1 -b -E -S %servername% -Q "%command%"`) do if not defined database set "database=%%a" & set "databaselog=%%b"

echo %database%
echo %databaselog%
if not exist "%datapath%" md "%datapath%">nul
sqlcmd -E -S %servername% -Q "%restore%"

EndLocal
exit/B 1