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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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) |
Leave a Reply