DECLARE @BAK_FILE_PATH VARCHAR(300)
DECLARE @DB_TO_RESTORE_TO VARCHAR(300)
DECLARE @SQL_SERVER_DATA_FOLDER VARCHAR(300)
 
/* [BAK_FILE_PATH] : Path of backup file. e.g; D:\DatabaseBackup\MyOldDB.bak */
SET @BAK_FILE_PATH = 'D:\DatabaseBackup\MyOldDB.bak'
 
/* [DB_TO_RESTORE_TO] : Database to restore to. Should already exist. */
SET @DB_TO_RESTORE_TO = 'MyNewDB'
 
/* [SQL_SERVER_DATA_FOLDER] : Sql server data folder. Usually C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data */
SET @SQL_SERVER_DATA_FOLDER = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data'
 
 
/*
  Nothing to edit below this line
*/
 
DECLARE @LOGICAL_NAME_Data VARCHAR(300)
DECLARE @LOGICAL_NAME_Log VARCHAR(300)
DECLARE @query VARCHAR(3000)
 
/* Step 1 : Get the logical file names */
SET @query = 'RESTORE FILELISTONLY FROM DISK = ' + QUOTENAME(@BAK_FILE_PATH , '''')
 
CREATE TABLE #restoretemp
(
LogicalName nvarchar(128)
,Old_PhysicalName nvarchar(128)
,[TYPE] CHAR(1)
,FileGroupName nvarchar(128)
,[SIZE] NUMERIC(20,0)
,[MaxSize] NUMERIC(20,0)
,FileID BIGINT
,CreateLSN NUMERIC(25,0)
,DropLSN NUMERIC(25,0) NULL
,UniqueID uniqueidentifier
,ReadOnlyLSN NUMERIC(25,0)
,ReadWriteLSN NUMERIC(25,0)
,BackupSizeInByte BIGINT
,SourceBlockSize INT
,FilegroupID INT
,LogGroupGUID uniqueidentifier NULL
,DifferentialBaseLSN NUMERIC(25,0)
,DifferentialbaseGUID uniqueidentifier
,IsReadOnly bit
,IsPresent bit
)
 
INSERT #restoretemp EXEC (@query)
 
SELECT @LOGICAL_NAME_Data = LogicalName FROM #restoretemp WHERE [TYPE] = 'D'
SELECT @LOGICAL_NAME_Log = LogicalName FROM #restoretemp WHERE [TYPE] = 'L'
 
PRINT @LOGICAL_NAME_Data
PRINT @LOGICAL_NAME_Log
 
TRUNCATE TABLE #restoretemp
DROP TABLE #restoretemp
 
/* Step 2 : Restore the database */
SET @query = 'ALTER DATABASE ' + @DB_TO_RESTORE_TO + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
EXEC (@query)
 
SET @query = 'RESTORE DATABASE ' + @DB_TO_RESTORE_TO 
+ ' FROM DISK = ' + QUOTENAME(@BAK_FILE_PATH , '''') 
+ ' WITH REPLACE, ' 
+ ' MOVE ' + QUOTENAME(@LOGICAL_NAME_Data, '''') + ' TO ' + QUOTENAME((@SQL_SERVER_DATA_FOLDER+'\'+@DB_TO_RESTORE_TO+'.mdf') , '''')
+ ', MOVE ' + QUOTENAME(@LOGICAL_NAME_Log, '''') + ' TO ' + QUOTENAME((@SQL_SERVER_DATA_FOLDER+'\'+@DB_TO_RESTORE_TO+'_log.ldf'), '''') 
EXEC (@query)
 
SET @query = 'ALTER DATABASE ' + @DB_TO_RESTORE_TO + ' SET MULTI_USER'
EXEC (@query)