Create Database from SQL Server backup.

restore-database.sql

USE master;
SET 
  NOCOUNT ON;
DECLARE @pathBackup VARCHAR (500) = N'C:\User\Files\my_backup.bak'; -- Set your backup path
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) -- remove this column if using SQL 2005
  ) INSERT INTO @fileListTable EXEC (
  'RESTORE FILELISTONLY FROM DISK = N''' + @pathBackup + ''''
);
DECLARE @nameDb VARCHAR (100);
DECLARE @nameLog VARCHAR (100);
SELECT 
  @nameDb = LogicalName 
FROM 
  @fileListTable 
where 
  FileID = 1;
SELECT 
  @nameLog = LogicalName 
FROM 
  @fileListTable 
where 
  FileID = 2;

-- CREATE DB --
EXEC (
  'CREATE DATABASE [' + @nameDb + '];'
);

-- read files path --
DECLARE @fileDbTable TABLE (
  [FileID] BIGINT, 
  [PhysicalName] NVARCHAR(260)
);
INSERT INTO @fileDbTable EXEC (
  'SELECT f.file_id, f.physical_name FROM [' + @nameDb + '].sys.database_files f;'
);
DECLARE @targetMdf VARCHAR (600);
DECLARE @targetLdf VARCHAR (600);
SELECT 
  @targetMdf = PhysicalName 
FROM 
  @fileDbTable 
where 
  FileID = 1;
SELECT 
  @targetLdf = PhysicalName 
FROM 
  @fileDbTable 
where 
  FileID = 2;
EXEC (
  'ALTER DATABASE [' + @nameDb + '] SET OFFLINE WITH ROLLBACK IMMEDIATE'
);
EXEC (
  'ALTER DATABASE [' + @nameDb + '] SET ONLINE WITH ROLLBACK IMMEDIATE'
);
DECLARE @query VARCHAR(1200);
SET 
  @query = 'RESTORE DATABASE [' + @nameDb + '] FROM DISK=N''' + @pathBackup + ''' ';
SET 
  @query += 'WITH MOVE ''' + @nameDb + ''' TO N''' + @targetMdf + ''',';
SET 
  @query += 'MOVE ''' + @nameLog + ''' TO N''' + @targetLdf + ''', REPLACE, RECOVERY;';
EXEC (@query);

PRINT 'DATABASE ' + @nameDb + ' created successfully!';