Dynamically Restore SQL Server Database from Backup

Dynamically Restore SQL Server Database from Backup
We always get requests to restore databases from production to lab environment.This script dynamically restores the backup file and moves the data and log files to default locations.

/******************************************************************************************************
** Project: Operations
** Issue: Restore Database from Backup file
** ShortDesc: Restore Database From Backup File and Dynamically move data and log files to Default Location.This is intended for single data and log file Databases.
** Auth: EMETRIC\rvenkataraman
** Date: 2016-07-25 Created
** Description: Restore Database From Backup File and Dynamically move data and log files to Default Location.This is intended for single data and log file Databases.
**************************
** Change History
**************************
** CR Date Author Description
** ----- ----------– ----------- ------------------------------------------------------------
** 1  2016-07-25 Raju Venkataraman Restore Database From Backup File and Dynamically move data and log files to Default Location.This is intended for single data and log file Databases.
********************************************************************************************************/
SET NOCOUNT ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
SET XACT_ABORT  ON

-----------BEGIN SQL SCRIPT HEADER-------------------
DECLARE @DB_NAME VARCHAR(128)
SET @DB_NAME = ( SELECT DB_NAME(dbid)
                 FROM   master..sysprocesses
                 WHERE  spid = @@SPID
               )
PRINT '-----------------------------------------------------------------------'
PRINT '-----START RAPID DEVELOPEMENT SQL SCRIPT--------'
PRINT '-----SCRIPT RAN ON DB: ' + @DB_NAME
PRINT '-----SCRIPT START TIME: '
    + CONVERT(VARCHAR, CONVERT(DATETIME, GETDATE()), 121)
PRINT '-----MachineName : ' + CAST(SERVERPROPERTY('MachineName') AS VARCHAR)
PRINT '-----SQL Instance : ' + CAST(@@SERVERNAME AS VARCHAR)
PRINT '-----DB User : ' + CURRENT_USER
PRINT '-----System User : ' + SYSTEM_USER
PRINT '-----Host : ' + HOST_NAME()
PRINT '-----Application : ' + APP_NAME()
PRINT '-----TranCount : ' + CAST (@@trancount AS VARCHAR)
PRINT '-----------------------------------------------------------------------'
----------------END SQL SCRIPT HEADER---------------------

-- Script Specific Variables Declarations

DECLARE @StartTime DATETIME2 , @EndTime DATETIME2
SELECT @StartTime = GETDATE()

-- Script Block Start
BEGIN TRY

DECLARE @pBackupFile NVARCHAR(550);
DECLARE @pDatabaseName sysname;


SELECT  @pDatabaseName = 'AdventureWorks';
SELECT  @pBackupFile = '\\FileShare\AdventureWorks.BAK';

DECLARE @pDSQL NVARCHAR(MAX);
DECLARE @pDataFileLocation NVARCHAR(550);
DECLARE @pLogFileLocation NVARCHAR(550);
DECLARE @pLogicalDataFileName NVARCHAR(550);
DECLARE @pLogicalLogFileName NVARCHAR(550);
DECLARE @pDataFileName NVARCHAR(550);
DECLARE @pLogFileName NVARCHAR(550);

SELECT  @pDataFileName = @pDatabaseName + '.mdf' ,
        @pLogFileName = @pDatabaseName + '.ldf';
SELECT  @pDataFileLocation = CONVERT(VARCHAR(550), SERVERPROPERTY('instancedefaultdatapath')) ,
        @pLogFileLocation = CONVERT(VARCHAR(550), SERVERPROPERTY('instancedefaultlogpath'));


IF  SERVERPROPERTY('productversion') >= '13.0.1400.361'  -- SQL Server 2016 and Above Has SnapshotUrl
BEGIN
DECLARE @FileLists TABLE
    (
      [LogicalName] NVARCHAR(128) ,
      [PhysicalName] NVARCHAR(260) ,
      [Type] NCHAR(1) ,
      [FileGroupName] NVARCHAR(128) ,
      [Size] BIGINT ,
      [MaxSize] BIGINT ,
      [FileId] BIGINT ,
      [CreateLSN] DECIMAL(25, 0) ,
      [DropLSN] DECIMAL(25, 0) ,
      [UniqueId] UNIQUEIDENTIFIER ,
      [ReadOnlyLSN] DECIMAL(25, 0) ,
      [ReadWriteLSN] DECIMAL(25, 0) ,
      [BackupSizeInBytes] BIGINT ,
      [SourceBlockSize] INT ,
      [FileGroupId] INT ,
      [LogGroupGUID] UNIQUEIDENTIFIER ,
      [DifferentialBaseLSN] DECIMAL(25, 0) ,
      [DifferentialBaseGUID] UNIQUEIDENTIFIER ,
      [IsReadOnly] BIT ,
      [IsPresent] BIT ,
      [TDEThumbprint] VARBINARY(20) ,
      [SnapshotUrl] NVARCHAR(336)
    );

END

ELSE
BEGIN
  RAISERROR ('SnapshotUrl is Only Avialble for SQL Server 2016 and Above. Modify the Definition of Table Variable and Remove SnapShotURL.', -- Message text.
               16, -- Severity.
               1 -- State.
               );

END



SELECT  @pDSQL = ' RESTORE FILELISTONLY FROM DISK =  ''' + @pBackupFile + '''';
SELECT  @pDSQL;
INSERT  @FileLists
        EXEC(@pDSQL);

SELECT  @pLogicalDataFileName = LogicalName
FROM    @FileLists
WHERE   Type = 'D';

SELECT  @pLogicalLogFileName = LogicalName
FROM    @FileLists
WHERE   Type = 'L';


SELECT  @pDataFileLocation ,
        @pLogFileLocation ,
        @pDatabaseName;

SELECT  @pDSQL = '	RESTORE DATABASE ' + @pDatabaseName + '
FROM DISK =  ''' + @pBackupFile + '''
WITH MOVE  ''' + @pLogicalDataFileName + ''' TO ''' + @pDataFileLocation
        + @pDataFileName + ''', MOVE ''' + @pLogicalLogFileName + ''' TO '''
        + @pLogFileLocation + @pLogFileName + '''';

SELECT  @pDSQL;

EXEC(@pDSQL);
END TRY
BEGIN CATCH
    SELECT  ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage
END CATCH
-- Script Block End

SELECT @EndTime  = GETDATE()

-----------BEGIN SQL SCRIPT FOOTER--------------------------------------
PRINT '----------------------------------------------------------------'
PRINT '---FINISHED SQL SCRIPT--'
PRINT '---COMPLETED TIME:' + CONVERT(VARCHAR, CONVERT(DATETIME, GETDATE()), 121)
PRINT '---TranCount : ' + CAST (@@trancount AS VARCHAR)