July, 2016 | Kaizen - Continuous Improvement

Dynamically Restore SQL Server Database from Backup

Posted on July 21, 2016 by

Raju Venkataraman Posted in SQL Server, Technology — No Comments ↓

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)

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

/******************************************************************************************************

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

Month: July 2016

AWS SQL Server RDS Native Backup Restore

Dynamic SQL Search Stored Procedure

Caching SQL Server Tables After Restart

Dynamically Restore SQL Server Database from Backup

Launching AWS SQL Server RDS

Change SQL Server Authentication login password

Recent Posts

Categories

Recent Posts

Archives