PavelPN,
Держите скрипт - с помощью него можно из папки с любым кол-вом bak файлов получить автосгенеренный код на восстановление, который выплюнется этим скриптом. Далее - просто скопировать этот скрипт в новое окно и нажать F5.
Полезно в том, что в одном месте задаете конфигурацию - куда восстанавливать дата-файлы, ку да - логи и это по-сути все. Таким образом с помощью него - можно целые окрцужения накатывать-разворачивать по щелчку пальцев
P.S. этот безумный скрипт писал в свое время человек, который сейчас в Сбербанке Архитектором работает :)
1) Версия для MSSQL 2012 и старше
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.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE WITH OVERRIDE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE WITH OVERRIDE
GO
DECLARE @srcdir varchar(256)
DECLARE @targetdatadir varchar(256), @targetlogdir varchar(256), @targetftxdir varchar(256)
DECLARE @printSqlOnly bit
DECLARE @addDbName bit
DECLARE @dbnamesuffix varchar(256)
----#######################################################################################
SET @srcdir='D:\123\' -- поменять - исходная папка с bak файлами
SET @targetdatadir='D:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\' -- папка для MDF/NDF файлов
SET @targetlogdir='D:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Logs\' --папка для LOG файлов
SET @targetftxdir='D:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\' - можно пропустить или указать как папка для MDF/BDF файлов
SET @printSqlOnly=1
SET @addDbName=0
SET @dbnamesuffix=''
----########################################################################################
DECLARE @dircmd varchar(256)
SET @dircmd='dir /b '+@srcdir+'*.bak'
IF object_id('tempdb..#backups') IS NOT NULL DROP TABLE #backups
create table #backups (backupfile varchar(256), stmnt varchar(8000) DEFAULT '')
IF object_id('tempdb..#stmnts') IS NOT NULL DROP TABLE #stmnts
CREATE TABLE #stmnts (RecId int IDENTITY, stmnt varchar(8000) DEFAULT '')
if object_id('tempdb..#databases') is not null drop table #databases
create table #databases
(BackupName nvarchar(128),
BackupDescription nvarchar(255),
BackupType smallint,
ExpirationDate datetime,
Compressed tinyint,
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) NULL,
RecoveryModel nvarchar(60),
DifferentialBaseLSN numeric(25,0) NULL,
DifferentialBaseGUID uniqueidentifier,
BackupTypeDescription nvarchar(60),
BackupSetGUID uniqueidentifier NULL,
CompressedBackupSize bigint NULL,
Containment NVARCHAR(MAX),
KeyAlgorithm NVARCHAR(MAX),
EncryptorThumbprint NVARCHAR(MAX),
EncryptorType NVARCHAR(MAX))
if object_id('tempdb..#files') is not null drop table #files
create table #files(
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) NULL,
UniqueID 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,
IsReadOnly bit,
IsPresent bit,
TDEThumbprint varbinary(32)
)
set nocount ON
INSERT INTO #backups (backupfile)
EXEC master.sys.xp_cmdshell @dircmd
DELETE FROM #backups WHERE backupfile IS NULL
UPDATE #backups SET backupfile=@srcdir+backupfile
declare @dbname sysname
declare @backupfile varchar(255), @stmnt varchar(max)
DECLARE @stmntId int
declare cur cursor for select backupfile from #backups
open cur
fetch cur into @backupfile
while @@fetch_status=0
begin
truncate table #databases
truncate table #files
set @stmnt='insert into #databases
exec(''restore headeronly from disk='''''+@backupfile+''''''')'
exec(@stmnt)
set @stmnt='insert into #files
exec(''restore filelistonly from disk='''''+@backupfile+''''''')'
exec(@stmnt)
INSERT INTO #stmnts (stmnt)
select
'PRINT ''restoring database ['+DatabaseName+@dbnamesuffix+']''
RESTORE DATABASE ['+DatabaseName+@dbnamesuffix+'] FROM
DISK = N'''+@backupfile+'''
WITH FILE = 1,
' from #databases
select @dbname=''
if @addDbName=1
begin
select top 1 @dbname=DatabaseName+'_' from #databases
end
INSERT INTO #stmnts (stmnt)
select
' MOVE N'''+
LogicalName+
''' TO N'''+case Type when 'L' then @targetlogdir when 'D' then @targetdatadir when 'F' then @targetftxdir else @targetdatadir end+
@dbname+LogicalName+'.'+case Type when 'L' then 'ldf' when 'D' then 'mdf' when 'F' then 'FTX' else '' end+''',
'
from #files
INSERT INTO #stmnts (stmnt)
select
' NOUNLOAD, STATS = 1'
WHILE EXISTS (SELECT TOP 1 1 FROM #stmnts)
BEGIN
SELECT TOP 1 @stmntId = RecId, @stmnt = stmnt FROM #stmnts ORDER BY RecId ASC
UPDATE #backups SET stmnt=stmnt+@stmnt WHERE backupfile=@backupfile
DELETE FROM #stmnts WHERE RecId=@stmntId
END
fetch cur into @backupfile
end
close cur
deallocate cur
IF @printSqlOnly = 1 SELECT stmnt FROM #backups
ELSE
BEGIN
SET @stmnt=''
WHILE EXISTS (SELECT TOP 1 1 FROM #backups)
BEGIN
SELECT TOP 1 @backupfile=backupfile, @stmnt=stmnt FROM #backups ORDER BY backupfile ASC
PRINT '----------------------------------------------------------------------------------------------------'
PRINT @stmnt
PRINT '----------------------------------------------------------------------------------------------------'
EXEC(@stmnt)
DELETE FROM #backups WHERE backupfile=@backupfile
END
END
set nocount OFF
IF object_id('tempdb..#backups') IS NOT NULL DROP TABLE #backups
IF object_id('tempdb..#stmnts') IS NOT NULL DROP TABLE #stmnts
if object_id('tempdb..#databases') is not null drop table #databases
if object_id('tempdb..#files') is not null drop table #files
GO
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE WITH OVERRIDE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 0
GO
-- To update the currently configured value for this feature.
RECONFIGURE WITH OVERRIDE
GO
|
