Dynamically Restore SQL Database

SQLBak-Dynamic-Restore.sql

/******************************************************************************************************
** 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)