forked from ktaranov/sqlserver-kit
-
Notifications
You must be signed in to change notification settings - Fork 0
/
dbo.sp_RestoreScript.sql
206 lines (164 loc) · 12.1 KB
/
dbo.sp_RestoreScript.sql
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
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
/*
Original link: https://sqlundercover.com/2017/06/29/undercover-toolbox-sp_restorescript-a-painless-way-to-generate-sql-server-database-restore-scripts
sp_RestoreScript 1.0
Written By David Fowler
29 June 2017
Generate a set of backup commands to restore a database(s) to a specified time
Parameters
==========
@DatabaseName - A comma delimited list of databases to restore - DEFAULT: Current Database
@RestoreAsName - A comma delimited list of name to restore databases as, the number of should correspond to the number of
databases in @DatabaseName.
@RestoreToDate- See @PointInTime for useage of @RestoreToDate. - DEFAULT: GETDATE()
@FirstLogToRestore- If 'LogsOnly' restore option has been selected, this is the date of the first log to be restored. This
can't be left NULL if 'LogsOnly' has been selected. If any other option has been selected it wil be ignored
@RestoreOptions- 'ToLog' - Script will generate statements including full, differential and log backups - DEFAULT
'ToDiff' Script will only include full and differential backups
'ToFull' Script will only include a full backup
'LogsOnly' - Script will only include log backups between @FirstLogToRestore and @RestoreToDate
@PointInTime - 1 - Script will restore to a point in time specified in @RestoreToDate
0 - Script will to last backup before the date\time specified in @RestoreToDate - DEFAULT
@WithMoveDataPath - WITH MOVE Path to move data files to. - DEFAULT: original path
@WithMoveLogPath - WITH MOVE Path to move log files to. - DEFAULT:@ original path
@Replace - 1 - Databases will be restored WITH REPLACE
0 - Database won't be restored WITH REPLACE - DEFAULT
@NoRecovery - 1 - Last file will be restored with NORECOVERY, leaving the database in 'restoring' state
0 - Last file will be restored with RECOVERY and the database brought online - DEFAULT
Full documentation and examples can be found at www.sqlundercover.com
*/
USE MASTER
GO
CREATE PROC sp_RestoreScript
(
@DatabaseName VARCHAR(3000) = NULL,
@RestoreAsName VARCHAR(3000) = NULL,
@RestoreToDate DATETIME = NULL,
@FirstLogToRestore DATETIME = NULL,
@RestoreOptions VARCHAR(11) = 'ToLog',
@PointInTime BIT = 0,
@WithMoveDataPath VARCHAR(3000) = NULL,
@WithMoveLogPath VARCHAR(3000) = NULL,
@Replace BIT = 0,
@NoRecovery BIT = 0
)
AS
BEGIN
DECLARE @WithMove VARCHAR(3000)
SET NOCOUNT ON
--Check that @RestoreOptions is a valid value
IF @RestoreOptions NOT IN ('ToLog','ToDiff','ToFull','LogsOnly')
RAISERROR (N'Invalid Restore Option specified, please use ToLog, ToDiff, ToFull or LogsOnly' , 15, 1)
--Check that both 'WithMove' parameters are either both null or both hold a value (why doens't SQL Server give us an XOR?)
IF ((@WithMoveDataPath IS NULL) AND (@WithMoveLogPath IS NOT NULL))
OR
((@WithMoveDataPath IS NOT NULL) AND (@WithMoveLogPath IS NULL))
RAISERROR (N'The ''WithMove'' parameters either both must be NULL or both must hold a value', 15,1)
--set compatibility mode
DECLARE @compatibility BIT
--set compatibility to 1 if server version includes STRING_SPLIT
SELECT @compatibility = CASE
WHEN SERVERPROPERTY ('productversion') >= '13.0.4001.0' AND Compatibility_Level >= 130 THEN 1
ELSE 0
END
FROM sys.databases
WHERE name = DB_NAME()
--drop temp tables
IF OBJECT_ID('tempdb..#BackupCommands') IS NOT NULL
DROP TABLE #BackupCommands
CREATE TABLE #BackupCommands
(backup_start_date DATETIME, DBName VARCHAR(255), command VARCHAR(1000))
IF OBJECT_ID('tempdb..#BackupCommandsFinal') IS NOT NULL
DROP TABLE #BackupCommandsFinal
CREATE TABLE #BackupCommandsFinal
(backup_start_date DATETIME, DBName VARCHAR(255), command VARCHAR(1000))
--remove any spaces in list of databases
SET @DatabaseName = REPLACE(@DatabaseName, ' ','')
SET @RestoreAsName = REPLACE(@RestoreAsName, ' ','')
--@PointInTime can only be true if @RestoreOptions is either 'ToLog' or 'LogsOnly'
IF (@PointInTime = 1) AND (@RestoreOptions NOT IN ('ToLog','LogsOnly'))
BEGIN
RAISERROR (N'Point in time restore is not possible with selected restore options. @PointInTime has been changed to 0', 15,1)
SET @PointInTime = 0
END
--If @RestoreOptions is 'LogsOnly', a RestoreToDate value must be specified
IF (@RestoreOptions = 'LogsOnly') AND (@FirstLogToRestore IS NULL)
RAISERROR (N'When @RestoreOptions = LogsOnly, a @FirstLogToRestore date must be specified', 15,1)
--Set default value for @RestoreToDate if unspecified
IF (@RestoreToDate IS NULL)
SET @RestoreToDate = GETDATE()
--Set default value for @DatabaseName if unspecified
IF (@DatabaseName IS NULL)
SET @DatabaseName = DB_NAME()
--Declare cursor containing database names
--if compatibility mode = 1 then it's safe to use STRING_SPLIT, otherwise use fn_SplitString
IF (@Compatibility = 1)
BEGIN
--raise an error if there's a mismatch in the number of databases in @DatabaseName and @RestoreAsName
IF ((SELECT COUNT(*) FROM STRING_SPLIT(@DatabaseName,','))
!= (SELECT COUNT(*) FROM STRING_SPLIT(@RestoreAsName,',')))
AND @RestoreAsName IS NOT NULL
RAISERROR (N'There is a mismatch in the number of databases in @DatabaseName and @RestoreAsName', 15,1)
DECLARE DatabaseCur CURSOR FOR
SELECT SourceDatabase.value AS SourceDatabase,DestinationDatabase.value AS DestinationDatabase
FROM
(SELECT value, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RowNumber
FROM STRING_SPLIT(@DatabaseName,',') ) SourceDatabase
LEFT JOIN
(SELECT value, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RowNumber
FROM STRING_SPLIT(@RestoreAsName,',') ) DestinationDatabase
ON SourceDatabase.RowNumber = DestinationDatabase.RowNumber
END
ELSE BEGIN
--raise an error if there's a mismatch in the number of databases in @DatabaseName and @RestoreAsName
IF ((SELECT COUNT(*) FROM fn_SplitString(@DatabaseName,','))
!= (SELECT COUNT(*) FROM fn_SplitString(@RestoreAsName,',')))
AND @RestoreAsName IS NOT NULL
RAISERROR (N'There is a mismatch in the number of databases in @DatabaseName and @RestoreAsName', 15,1)
SELECT SourceDatabase.StringElement AS SourceDatabase,DestinationDatabase.StringElement AS DestinationDatabase
FROM
(SELECT StringElement, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RowNumber
FROM fn_SplitString(@DatabaseName,',') ) SourceDatabase
LEFT JOIN
(SELECT StringElement, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RowNumber
FROM fn_SplitString(@RestoreAsName,',') ) DestinationDatabase
ON SourceDatabase.RowNumber = DestinationDatabase.RowNumber
END
--open cursor
OPEN DatabaseCur
FETCH NEXT FROM DatabaseCur INTO @DatabaseName, @RestoreAsName
WHILE @@FETCH_STATUS = 0
BEGIN
--Get last full backup for required timeframe
IF (@RestoreOptions IN ('PointInTime','ToLog','ToDiff','ToFull'))
BEGIN
INSERT INTO #BackupCommands
SELECT TOP 1 backupset.backup_start_date, @DatabaseName AS DBName,
'RESTORE DATABASE ' + COALESCE(QUOTENAME(@RestoreAsName), QUOTENAME(@DatabaseName)) + ' FROM DISK = ''' + mediafamily.physical_device_name + ''' WITH NORECOVERY, FILE = ' + CAST(position AS VARCHAR) AS Command
FROM msdb.dbo.backupset backupset
INNER JOIN msdb.dbo.backupmediafamily mediafamily ON backupset.media_set_id = mediafamily.media_set_id
WHERE backupset.database_name = @DatabaseName
AND backupset.backup_start_date < @RestoreToDate AND backupset.type = 'D' ORDER BY backupset.backup_start_date DESC END --if Replace is set, add to statement IF @Replace = 1 BEGIN UPDATE #BackupCommands SET command = command + ', REPLACE' WHERE DBName = @DatabaseName END --if WithMove parameters are set, create WITH MOVE statements IF (@WithMoveDataPath IS NOT NULL) AND (@WithMoveLogPath IS NOT NULL) BEGIN --generate MOVE statement DECLARE @WithMoveCmd VARCHAR(3000) SET @WithMoveCmd = ',' SELECT @WithMoveCmd = @WithMoveCmd + STUFF((SELECT ',' + ' MOVE ''' + name + ''' TO ''' + REPLACE(physical_name,REVERSE(SUBSTRING(REVERSE(physical_name),CHARINDEX('\',REVERSE(physical_name),0), LEN(physical_name))),@WithMoveDataPath) + '''' FROM sys.master_files WHERE database_id = DB_ID(@DatabaseName) AND type_desc = 'ROWS' FOR XML PATH('')) ,1,1,'') SET @WithMoveCmd = @WithMoveCmd + ',' SELECT @WithMoveCmd = @WithMoveCmd + STUFF((SELECT ',' + 'MOVE ''' + name + ''' TO ''' + REPLACE(physical_name,REVERSE(SUBSTRING(REVERSE(physical_name),CHARINDEX('\',REVERSE(physical_name),0), LEN(physical_name))),@WithMoveLogPath) + '''' FROM sys.master_files WHERE database_id = DB_ID(@DatabaseName) AND type_desc = 'LOG' FOR XML PATH('')) ,1,1,'') --append MOVE statement to backup command UPDATE #BackupCommands SET command = command + @WithMoveCmd WHERE DBName = @DatabaseName END --Get last diff for required timeframe IF (@RestoreOptions IN ('PointInTime','ToLog','ToDiff')) BEGIN INSERT INTO #BackupCommands SELECT TOP 1 backupset.backup_start_date, @DatabaseName AS DBName, 'RESTORE DATABASE ' + COALESCE(QUOTENAME(@RestoreAsName), QUOTENAME(@DatabaseName)) + ' FROM DISK = ''' + mediafamily.physical_device_name + ''' WITH NORECOVERY, FILE = ' + CAST(position AS VARCHAR) FROM msdb.dbo.backupset backupset INNER JOIN msdb.dbo.backupmediafamily mediafamily ON backupset.media_set_id = mediafamily.media_set_id WHERE backupset.database_name = @DatabaseName AND backupset.backup_start_date >
(SELECT backup_start_date FROM #BackupCommands)
AND backupset.backup_start_date < @RestoreToDate AND backupset.type = 'I' ORDER BY backupset.backup_start_date DESC END --Get all log backups since last full or diff IF (@RestoreOptions IN ('ToLog','LogsOnly')) INSERT INTO #BackupCommands SELECT backupset.backup_start_date, @DatabaseName AS DBName, 'RESTORE LOG ' + COALESCE(QUOTENAME(@RestoreAsName), QUOTENAME(@DatabaseName)) + ' FROM DISK = ''' + mediafamily.physical_device_name + ''' WITH NORECOVERY, FILE = ' + CAST(position AS VARCHAR) FROM msdb.dbo.backupset backupset INNER JOIN msdb.dbo.backupmediafamily mediafamily ON backupset.media_set_id = mediafamily.media_set_id WHERE backupset.database_name = @DatabaseName AND backupset.backup_start_date >
(SELECT COALESCE(MAX(backup_start_date),@FirstLogToRestore) FROM #BackupCommands)
AND backupset.backup_start_date < @RestoreToDate AND backupset.type = 'L' ORDER BY backupset.backup_start_date DESC --Get point in time if enabled IF (@PointInTime = 1) AND (EXISTS (SELECT * FROM #BackupCommands)) BEGIN INSERT INTO #BackupCommands SELECT TOP 1 backupset.backup_start_date, @DatabaseName AS DBName, 'RESTORE LOG ' + COALESCE(QUOTENAME(@RestoreAsName), QUOTENAME(@DatabaseName)) + ' FROM DISK = ''' + mediafamily.physical_device_name + ''' WITH NORECOVERY, FILE = ' + CAST(position AS VARCHAR) + ', STOPAT = ''' + CAST(@RestoreToDate AS VARCHAR) + '''' AS Command FROM msdb.dbo.backupset backupset INNER JOIN msdb.dbo.backupmediafamily mediafamily ON backupset.media_set_id = mediafamily.media_set_id WHERE backupset.database_name = @DatabaseName AND backupset.backup_start_date > @RestoreToDate
AND backupset.type = 'L'
ORDER BY backupset.backup_start_date
END
INSERT INTO #BackupCommandsFinal
SELECT * FROM #BackupCommands
TRUNCATE TABLE #BackupCommands
FETCH NEXT FROM DatabaseCur INTO @DatabaseName, @RestoreAsName
END
CLOSE DatabaseCur
DEALLOCATE DatabaseCur
IF @NoRecovery = 0 --if restore with no recovery is off, remove NORECOVERY from the last restore command
BEGIN
UPDATE #BackupCommandsFinal
SET command = REPLACE(command,'NORECOVERY','RECOVERY')
WHERE backup_start_date = (SELECT MAX(backup_start_date) FROM #BackupCommandsFinal)
END
SELECT backup_start_date, DBName, command
FROM #BackupCommandsFinal
ORDER BY DBName,backup_start_date
END