SQL Server Restore Database with new file names

Question

Is it possible to restore SQL Server database from a backup and at the same time create *.mdf and *.ldf files with new names?

Assumed that I know that there are only two files per backup (one mdf and one ldf), but I don't know the exact names of these files and unfortunately I can't derive them from database names.

The purpose of all this is to create batch file which would restore databases from a set of backup files. At the same time I must solve filename conflicting issue.

Currently I have this command, which doesn't work for every backup because some database files are named differently from variable %DATABASENAME%:

...
RESTORE DATABASE [%NewDB%] 
FROM DISK = N'%BACKUPFILENAME%' 
WITH RECOVERY,  
MOVE N'%DATABASENAME%' TO N'C:\%NewDB%.mdf',  
MOVE N'%DATABASENAME%_Log' TO N'C:\%NewDB%_Log.ldf'
...

Answer 1

It's possible to get the details of the datafiles in a backup by using RESTORE FILELISTONLY.

You can get this information into a table you can use for building a restore statement by inserting it into a temp table or table variable. To integrate this into your existing code:

DECLARE @fileListTable TABLE
(
    LogicalName          NVARCHAR(128),
    PhysicalName         NVARCHAR(260),
    [Type]               CHAR(1),
    FileGroupName        NVARCHAR(128),
    SIZE                 NUMERIC(20,0),
    MaxSize              NUMERIC(20,0),
    FileID               BIGINT,
    CreateLSN            NUMERIC(25,0),
    DropLSN              NUMERIC(25,0),
    UniqueID             UNIQUEIDENTIFIER,
    ReadOnlyLSN          NUMERIC(25,0),
    ReadWriteLSN         NUMERIC(25,0),
    BackupSizeInBytes    BIGINT,
    SourceBlockSize      INT,
    FileGroupID          INT,
    LogGroupGUID         UNIQUEIDENTIFIER,
    DifferentialBaseLSN  NUMERIC(25,0),
    DifferentialBaseGUID UNIQUEIDENTIFIER,
    IsReadOnly           BIT,
    IsPresent            BIT,
    TDEThumbprint        VARBINARY(32)
)

--This schema works from SQL 2008 to SQL 2014. 
--SQL 2005 didn't have the TDEThumbprint column, but is otherwise the same.

INSERT INTO @fileListTable EXEC('restore filelistonly 
FROM DISK = N''%BACKUPFILENAME%''')

DECLARE @datafile NVARCHAR(128), @logfile NVARCHAR(128)

SELECT @datafile = LogicalName FROM @fileListTable WHERE Type = 'D'

SELECT @logfile = LogicalName FROM @fileListTable WHERE Type = 'L'

RESTORE DATABASE [%NewDB%] 
FROM DISK = N'%BACKUPFILENAME%' 
WITH RECOVERY,  
MOVE @datafile TO N'C:\%NewDB%.mdf',  
MOVE @logfile TO N'C:\%NewDB%_Log.ldf'

In a scenario with more data/log files, the code would need to be correspondingly more complex.

One way to resolve the potential for conflicting filenames would be by appending a timestamp, GUID or other reasonably unique identifier to the new filenames.