restore database sql script in sql server

 In this article, we are going to see, how to restore database sql script in sql server

USE MASTER

GO

/***********************  INPUT PARAMETERS *************************/

SET NOCOUNT ON;

DECLARE @DBPath           VARCHAR(200) = 'D:\DatabaseBackUp\ClientDb\AdventureWorks2019.bak'; -- local system database full path

DECLARE @RLocation        VARCHAR(200) = 'D:\DatabaseRestore\'; -- restore database location path

DECLARE @RDBName          VARCHAR(50)  = 'AdventureWorks2019'; -- database name

DECLARE @RDBPreFix        VARCHAR(50)  = ''; -- db add prefix (optional)

DECLARE @DBName           VARCHAR(100) = @RDBName + @RDBPreFix; -- concate name prefix

DECLARE @ISRestoreDB      BIT          = 1; -- to set flage if restore database

/************************ INPUT PARAMETERS **************************/

/************* RESTORE DB SCRIPT DO NOT CHANGE ANYTHING *************/

DECLARE @TEMPDB 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)    -- comment this column if using SQL 2005

,[SnapshotURL]           NVARCHAR(360)    -- comment this column if using less than SQL 2016 (13.x)

)

DECLARE @DLogicalName  VARCHAR(100) = '';

DECLARE @LLogicalName  VARCHAR(100) = '';

DECLARE @RCOUNT        INT          =  0;

DECLARE @RDBString     VARCHAR(500) = '';

DECLARE @RDB    VARCHAR(200) = @DBPath;

DECLARE @RDBMDF VARCHAR(200) = @RLocation + @DBName   + '.mdf';

DECLARE @RDBLDF VARCHAR(200) = @RLocation + @DBName   + '.ldf';

IF @ISRestoreDB = 1

BEGIN

DELETE FROM @TEMPDB;

INSERT INTO @TEMPDB  EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @RDB + '''')

--SELECT * FROM @TEMPDB

SELECT @RCOUNT = COUNT(1) FROM @TEMPDB;

IF (@RCOUNT = 2 )

BEGIN

SELECT @DLogicalName = LogicalName FROM @TEMPDB WHERE [Type] = 'D';

SELECT @LLogicalName = LogicalName FROM @TEMPDB WHERE [Type] = 'L';

IF EXISTS (SELECT NAME FROM MASTER.DBO.SYSDATABASES WHERE name = @DBName)

BEGIN

--EXEC('DROP DATABASE '+ @DBName + '')

PRINT 'Database name already exists : '+ @DBName + ''

RETURN

END

SET @RDBString = 'RESTORE DATABASE ' + @DBName + ' FROM DISK = ''' + @RDB + '''

WITH 

MOVE ''' + @DLogicalName + ''' TO ''' + @RDBMDF + ''',

MOVE ''' + @LLogicalName + ''' TO ''' + @RDBLDF + ''',REPLACE, STATS=10;';

EXEC (@RDBString);

END

END

/************* RESTORE DB SCRIPT DO NOT CHANGE ANYTHING *************/