How to restore SQL Server database just by dbName and physical path

I have several database backups and I want to restore all of them to a server.

Can someone point me in the right direction with the SQL script to modify the logical file names to the correct path and not the ones carried over with the database??

Restore FILELISTONLY FROM DISK='E:\New folder\Thursday - DB_Order.bak'

RESTORE DATABASE Business_Data_TSQL
FROM DISK='E:\New folder\Thursday - DB_Order.bak'
WITH
MOVE 'DBOrder_Data' TO 'E:\New folder\Business_Data.mdf',
MOVE 'DBOrder_Log' TO 'E:\New folder\Business_Data_log.ldf'

Using query 1 I get the logical name then those names need to manually be set for query 2, but it’s really difficult when there is more than a single database.

Please help me to write a script where I just pass the physical path of backup file and new database name, then a restore of that database occurs.

If have any questions, please ask. Thanks in advance.

Here is something that you can use. It is not fully automated but this is definitely very useful piece of script.

SET NOCOUNT ON;

DECLARE 
    @DBName NVarchar(100),
    @BackupFile NVarchar(1000),
    @DeviceFrom NVarchar(1000),
    @DeviceTo NVarchar(1000),
    @LogicalName NVarchar(1000),
    @PhysicalName NVarchar(1000),
    @SQL NVarchar(MAX),
    @RowsToProcess integer,
    @CurrentRow integer,
    @Comma NVarchar(25);

SET @DBName = 'DB_Order'; --  Change this for each database
SET @BackupFile = 'E:\New folder\Thursday - DB_Order.bak'; --  Change this for each database

SELECT @DeviceFrom = SUBSTRING(physical_name, 1,
CHARINDEX(@DBName + '.mdf',
physical_name) - 1) 
FROM master.sys.master_files
WHERE name = @DBName AND FILE_ID = 1;

SET @DeviceTo = 'E:\New folder\'; -- Change this if you are changing your restore location
SET @SQL = 'RESTORE DATABASE ' + @DBName + ' FROM DISK = ''' + @BackupFile + ''' WITH ';
SET @CurrentRow = 0;
SET @Comma = ',';

DECLARE @FileList TABLE (
    RowID int not null primary key identity(1,1)
    ,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 BIGINT 
    ,FilegroupId BIGINT 
    ,LogGroupGUID uniqueidentifier 
    ,DifferentialBaseLSN numeric(25) 
    ,DifferentialBaseGUID uniqueidentifier 
    ,IsReadOnly BIGINT 
    ,IsPresent BIGINT
    ,TDEThumbprint VARBINARY(32) -- Remove this line for SQL Server 2005
    );

INSERT INTO @FileList
EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @BackupFile + '''')
SET @RowsToProcess = @@RowCount;

WHILE @CurrentRow < @RowsToProcess
BEGIN
    SET @CurrentRow= @CurrentRow + 1;
    BEGIN
    IF @CurrentRow = @RowsToProcess
        SET @Comma = ',STATS=1';
    END
    SELECT @LogicalName = LogicalName,@PhysicalName = PhysicalName FROM @FileList WHERE RowID=@CurrentRow;
    SET @PhysicalName = Replace(@PhysicalName,@DeviceFrom,@DeviceTo);
    SET @SQL = @SQL + 'MOVE ''' + @LogicalName + ''' TO ''' + @PhysicalName + '''' + @Comma + '';
END

SELECT @SQL;
--EXEC(@SQL); -- Execute when you are ready.

You can use SQLCMD / Batch file to pass in the parameters of your DB name and physical file name and try to automate it.

StackExchange Database Administrators Q#9565, answer score: 3