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
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
|
drop procedure if exists Restore_from_folder
go
Create procedure Restore_from_folder
/* This procedure is intend to deal with backup folder made by maintenance plan without using backup history.
The common way to use it is to have a freshest copy of a production DB to preprod server.
Nb: There is only one destination datafile folder because
-I don't want deal with table parameter
-It's usual for a preprod server
-If you set null, no path change
Provided by Michel Priori (Fr)
Script version : 20181020
@@version >= SQL Server 2014 Service Pack 1
*/
@Backup_Path_Folder nvarchar(255)
,@databasename nvarchar(255)
,@Restore_Datafile_Folder nvarchar(255) = null
,@Restore_Logfile_Folder nvarchar(255) = null
,@Execution_Mode int = 1 /* 1:Default. List All finded backupset
2:Script Verifyonly most recent backup sequence
3:Script Restore most recent backup sequence
4:Execute Verifyonly most recent backup sequence
5:Execute Restore most recent backup sequence
*/
As
begin
set nocount on
select @Backup_Path_Folder = case when right(@Backup_Path_Folder,1)=char(92)
then left(@Backup_Path_Folder, len(@Backup_Path_Folder)-1)
else @Backup_Path_Folder
end
select @Restore_Datafile_Folder = case when right(@Restore_Datafile_Folder,1)=char(92)
then left(@Restore_Datafile_Folder, len(@Restore_Datafile_Folder)-1)
else @Restore_Datafile_Folder
end
select @Restore_Logfile_Folder = case when right(@Restore_Logfile_Folder,1)=char(92)
then left(@Restore_Logfile_Folder, len(@Restore_Logfile_Folder)-1)
else @Restore_Logfile_Folder
end
/*
** Catch list of existing backupfiles
**
*/
DECLARE @DirectoryTree TABLE (
id int IDENTITY(1,1)
,subdirectory nvarchar(512)
,depth int
,isfile bit);
DECLARE
@RESTORE_headeronly as TABLE
(Backup_entry int IDENTITY(1,1)
,backupFilename nvarchar(512)
,BackupName nvarchar(128)
,BackupDescription nvarchar(255)
,BackupType smallint
,ExpirationDate datetime
,Compressed bit
,Position smallint
,DeviceType tinyint
,UserName nvarchar(128)
,ServerName nvarchar(128)
,DatabaseName nvarchar(128)
,DatabaseVersion int
,DatabaseCreationDate datetime
,BackupSize numeric(20, 0)
,FirstLSN numeric(25, 0)
,LastLSN numeric(25, 0)
,CheckpointLSN numeric(25, 0)
,DatabaseBackupLSN numeric(25, 0)
,BackupStartDate datetime
,BackupFinishDate datetime
,SortOrder smallint
,[CodePage] smallint
,UnicodeLocaleId int
,UnicodeComparisonStyle int
,CompatibilityLevel tinyint
,SoftwareVendorId int
,SoftwareVersionMajor int
,SoftwareVersionMinor int
,SoftwareVersionBuild int
,MachineName nvarchar(128)
,Flags int
,BindingId uniqueidentifier
,RecoveryForkId uniqueidentifier
,Collation nvarchar(128)
,FamilyGUID uniqueidentifier
,HasBulkLoggedData bit
,IsSnapshot bit
,IsReadOnly bit
,IsSingleUser bit
,HasBackupChecksums bit
,IsDamaged bit
,BeginsLogChain bit
,HasIncompleteMetaData bit
,IsForceOffline bit
,IsCopyOnly bit
,FirstRecoveryForkID uniqueidentifier
,ForkPointLSN numeric(25, 0)
,RecoveryModel nvarchar(60)
,DifferentialBaseLSN numeric(25, 0)
,DifferentialBaseGUID uniqueidentifier
,BackupTypeDescription nvarchar(60)
,BackupSetGUID uniqueidentifier
,CompressedBackupSize bigint
,Containment tinyint
,KeyAlgorithm nvarchar(32)
,EncryptorThumbprint varbinary(20)
,EncryptorType nvarchar(32)
);
Declare
@C1_subdirectory nvarchar(512)
,@File_to_Restore nvarchar(512)
,@STR_SQL nvarchar(max)
INSERT INTO @DirectoryTree (subdirectory,depth,isfile)
EXEC master.sys.xp_dirtree @Backup_Path_Folder,1,1;
/*
** Catch list of backusets of claim database in backupfiles
**
*/
Declare C1 cursor for
SELECT subdirectory
FROM @DirectoryTree
WHERE isfile = 1
and right(subdirectory,3) in ('bak','trn')
ORDER BY id;
Open C1;
Fetch C1 into @C1_subdirectory;
WHILE @@fetch_Status = 0
BEGIN
set @File_to_Restore = @Backup_Path_Folder+char(92)+@C1_subdirectory
set @STR_SQL = 'RESTORE headeronly from disk = ''' + @File_to_Restore+'''';
insert into @RESTORE_headeronly
(BackupName ,BackupDescription ,BackupType ,ExpirationDate
,Compressed ,Position ,DeviceType ,UserName
,ServerName ,DatabaseName ,DatabaseVersion ,DatabaseCreationDate
,BackupSize ,FirstLSN ,LastLSN ,CheckpointLSN
,DatabaseBackupLSN ,BackupStartDate ,BackupFinishDate ,SortOrder
,[CodePage] ,UnicodeLocaleId ,UnicodeComparisonStyle,CompatibilityLevel
,SoftwareVendorId ,SoftwareVersionMajor ,SoftwareVersionMinor ,SoftwareVersionBuild
,MachineName ,Flags ,BindingId ,RecoveryForkId
,Collation ,FamilyGUID ,HasBulkLoggedData ,IsSnapshot
,IsReadOnly ,IsSingleUser ,HasBackupChecksums ,IsDamaged
,BeginsLogChain ,HasIncompleteMetaData,IsForceOffline ,IsCopyOnly
,FirstRecoveryForkID ,ForkPointLSN ,RecoveryModel ,DifferentialBaseLSN
,DifferentialBaseGUID,BackupTypeDescription,BackupSetGUID ,CompressedBackupSize
,Containment ,KeyAlgorithm ,EncryptorThumbprint ,EncryptorType
)
exec (@str_sql);
update @RESTORE_headeronly
set backupFilename = @C1_subdirectory
where backupFilename is null
fetch C1 into @C1_subdirectory;
END
close C1
deallocate C1
delete @RESTORE_headeronly where DatabaseName <> @databasename;
/*
Produce script or execute depending of @execution_mode value
1:Default. List All finded backupset
2:Script Verifyonly most recent backup sequence
3:Script Restore most recent backup sequence
4:Execute Verifyonly most recent backup sequence
5:Execute Restore most recent backup sequence
*/
if @execution_mode = 1 /* 1:Default. List All finded backupset */
begin
select
backupFilename ,BackupType ,position ,ServerName
,DatabaseName ,BackupStartDate ,FirstLSN ,LastLSN
,DifferentialBaseLSN
from @RESTORE_headeronly
where BackupType in (1,2,5)
order by BackupStartDate;
end
else
/* 2:Script Verifyonly most recent backup sequence
3:Script Restore most recent backup sequence
4:Execute Verifyonly most recent backup sequence
5:Execute Restore most recent backup sequence
*/
begin
declare @backup table
( Sql# int
, Backup_entry int
, DatabaseName nvarchar(128)
, backupFilename nvarchar(512)
, BackupType smallint
, Position smallint
, ServerName nvarchar(512)
, BackupStartDate datetime
, BackupFinishDate datetime
, FirstLSN numeric(25, 0)
, LastLSN numeric(25, 0)
, DifferentialBaseLSN numeric(25, 0)
);
with
LastBkpFull as
(select top 1
1 as sql#, R.Backup_entry, R.DatabaseName, r.backupFilename, r.BackupType, r.Position, r.ServerName, r.BackupStartDate, r.BackupFinishDate
, r.FirstLSN, r.LastLSN, r.DifferentialBaseLSN
from @RESTORE_headeronly as R
where R.BackupType = 1
order by R.BackupStartDate desc
)
,LastBkpDiff as
(select top 1
2 as sql#, R.Backup_entry, R.DatabaseName, r.backupFilename, r.BackupType, r.Position, r.ServerName, r.BackupStartDate, r.BackupFinishDate
, r.FirstLSN, r.LastLSN, r.DifferentialBaseLSN
from @RESTORE_headeronly as R
where R.BackupType = 5
and DifferentialBaseLSN = (select FirstLSN from LastBkpFull)
order by R.BackupStartDate desc
)
,LastBkpDB as
(select top 1 *
from (select * from LastBkpFull
union all
select * from LastBkpDiff) as BckDB
order by BackupStartDate desc
)
,BckLogSerie as
(select top 1 (select sql# from LastBkpDB) +1 as sql#
, R.Backup_entry, R.DatabaseName, r.backupFilename, r.BackupType, r.Position, r.ServerName, r.BackupStartDate, r.BackupFinishDate
, r.FirstLSN, r.LastLSN, r.DifferentialBaseLSN
from @RESTORE_headeronly as R
where r.BackupType = 2
and r.LastLSN > (select FirstLSN from LastBkpDB)
order by r.BackupStartDate asc
Union ALL
Select BLS.sql#+1
, R.Backup_entry, R.DatabaseName, r.backupFilename, r.BackupType, r.Position, r.ServerName, r.BackupStartDate, r.BackupFinishDate
, r.FirstLSN, r.LastLSN, r.DifferentialBaseLSN
from BckLogSerie as BLS
inner join @RESTORE_headeronly as R
on BLS.LastLSN=R.FirstLSN
where r.BackupType = 2
)
insert into @backup
select * from LastBkpFull
union all
select * from LastBkpDiff
union all
select * from BckLogSerie
Declare @RestoreFilelist as table
(LogicalName nvarchar(128)
,PhysicalName nvarchar(260)
,Type char(1)
,FileGroupName nvarchar(128) NULL
,Size numeric(20,0)
,MaxSize numeric(20,0)
,FileID bigint
,CreateLSN numeric(25,0)
,DropLSN numeric(25,0) NULL
,DUniqueID uniqueidentifier
,ReadOnlyLSN numeric(25,0) NULL
,ReadWriteLSN numeric(25,0) NULL
,BackupSizeInBytes bigint
,SourceBlockSize Int
,FileGroupID Int
,LogGroupGUID uniqueidentifier NULL
,DifferentialBaseLSN numeric(25,0) NULL
,DifferentialBaseGUID uniqueidentifier NULL
,IsReadOnly bit
,IsPresent bit
,TDEThumbprint varbinary(32) NULL
,SnapshotURL nvarchar(360) NULL
);
select @STR_SQL = 'Restore filelistOnly from disk = '''
+ @Backup_Path_Folder+char(92)+ backupFilename
+''' with file =' + convert(varchar(2), Position)
from @backup
where Sql# =1
insert into @RestoreFilelist
exec(@str_sql);
declare @Move_to varchar(max) =''
select @Move_to = @Move_to
+ ', Move '''
+ LogicalName
+ ''' to '''
+ case when Type = 'L' then
IIF(@Restore_Logfile_Folder is null
, PhysicalName
, @Restore_Logfile_Folder
+ Right (PhysicalName, charindex(char(92),reverse(physicalname)))
)
else
IIF(@Restore_Datafile_Folder is null
, PhysicalName
, @Restore_Datafile_Folder
+ Right (PhysicalName, charindex(char(92),reverse(physicalname))))
end
+ ''''
from @RestoreFilelist
Declare @Result_Set table
(sql# int
,Backup_entry int
,BackupStartDate datetime
,Sql_command varchar(max)
,DatabaseName varchar(250)
)
insert into @Result_Set
select sql#
, Backup_entry
, BackupStartDate
/* @Execution_Mode
2:Script Verifyonly most recent backup sequence
3:Script Restore most recent backup sequence
4:Execute Verifyonly most recent backup sequence
5:Execute Restore most recent backup sequence
*/
, case when @Execution_Mode in (2,4) then 'restore verifyonly from disk ='''
when @Execution_Mode in (3,5) then 'restore '
+ IIF(BackupType = 2,'Log ','Database ')
+ DatabaseName
+ ' from disk ='''
else null
end
+ @Backup_Path_Folder+char(92)+ backupFilename
+''' with file =' + convert(varchar(2), Position)
+ @Move_to
+ iif(@Execution_Mode in (3,5),', NORECOVERY','')
As Sql_Command
,DatabaseName
from @backup
order by sql# asc
if @Execution_Mode in (2,3)
select * from @Result_Set
else
begin
declare @recovery varchar(max)
declare C2 cursor for select SQL_command from @Result_Set
open C2
fetch c2 into @STR_SQL
while @@fetch_Status = 0
BEGIN
Print 'Executing : ' + @STR_SQL
execute (@STR_SQL)
fetch c2 into @STR_SQL
END
close C2
deallocate C2
if @Execution_Mode = 5
begin
set @recovery = 'Restore database '+ @databasename + ' with recovery'
execute (@recovery)
end
end
end
return 0
end |
Restauration sur un serveur de préprod
)
Répondre avec citation
Partager