SQL Server Restore Generate Script'i
Merhaba Arkadaşlar, herhangi bir SQL Server Instance'dan bir dizine alınan backup’ları başka bir SQL Server Instance'a toplu olarak restore edebilmek için klasik yöntemleri kullanabilir, instance’ın altındaki backup’ların herbirini teker teker restore edebiliriz. Ancak restore etmeniz gereken çok fazla veritabanı backup'ı varsa, bu durumda klasik yöntemler sizin için bir iş yükü oluşturacaktır. Bizde böyle bir durumla karşılaştık ve bu konu hakkında script yazma ihtiyacımız oluştu. Aşağıda bu iş için oluşturduğumuz restore generate scripti bulunmaktadır.
Bu script, set edilen bir dizinde bulunan backup dosyalarını alarak, herbir backup dosyası için, bize restore scripti oluşturmaktadır. Sizlerin de işine yarayabileceğini düşündüğüm bu script ek özellikler eklenerek geliştirilebilir.
--Ön hazirlik
--sp_configure 'xp_cmdshell',1
--reconfigure
-- parametre girişi
-- değer olarak verilen dizinden, toplu olarak okunan backupların atıldığı değişken
DECLARE @backupfolder varchar(256) = 'C:\Users\194495\Desktop\COPY_ONLY\20.05.2013'
-- bu iki parametre hedef SQL Server instance üzerinde restore sırasında kullanılacak data ve log folder bilgisini içerir
-- daha sonra belirtilen instance'ın default data ve log dizini paremetre olarak alınacak şekilde değişiklik yapılabilir.
DECLARE @RestoreDataFolder varchar(256) = 'K:\SQLDATA'
DECLARE @RestoreLogFolder varchar(256) = 'J:\LOG'
-- parametre olarak alınacak değişkenlerin tanımı
DECLARE
@BackupFile nvarchar(260), -- backup dosyası
@Restore_DatabaseName sysname = NULL, -- restore edilmesi sirasında kullanılacak database ismi
@Backup_DatabaseName sysname = NULL, -- backup set içindeki database ismi
@tmp varchar(4000), -- temp parametre
@physical_device_name varchar(1000), -- veritabanı dosyasinin physical_device_name'i
@backup_set_id_Full INT, -- id
@RESTOREDBName varchar(1000), -- restore database ismi
@CumulativeMDFRestore varchar(8000), -- mdf,ndf
@CumulativeLogRestore varchar(8000), -- log
@CumulativeOTHERRestore varchar(8000) -- filestream
;
-- Aşağıda tanımlanan tablo değişkeni, aşağıdaki örnek olarak verilmiş olan komut sonucu elde edilecek seti içerecektir.
-- RESTORE FILELISTONLY FROM DISK=N'P:\BACKUP\deneme.bak'
DECLARE @DBFileList TABLE
(LogicalName nvarchar(128) NOT NULL,
PhysicalName nvarchar(260) NOT NULL,
Type char(1) NOT NULL,
FileGroupName nvarchar(120) NULL,
Size numeric(20, 0) NOT NULL,
MaxSize numeric(20, 0) NOT NULL,
FileID bigint NULL,
CreateLSN numeric(25,0) NULL,
DropLSN numeric(25,0) NULL,
UniqueID uniqueidentifier NULL,
ReadOnlyLSN numeric(25,0) NULL ,
ReadWriteLSN numeric(25,0) NULL,
BackupSizeInBytes bigint NULL,
SourceBlockSize int NULL,
FileGroupID int NULL,
LogGroupGUID uniqueidentifier NULL,
DifferentialBaseLSN numeric(25,0)NULL,
DifferentialBaseGUID uniqueidentifier NULL,
IsReadOnly bit NULL,
IsPresent bit NULL,
TDEThumbprint varbinary(32) NULL
);
-- Aşağıda tanımlanan tablo değişkeni, aşağıdaki örnek olarak verilmiş olan komut sonucu elde edilecek seti içerecektir.
-- RESTORE HEADERONLY FROM DISK=N'P:\BACKUP\COPY_ONLY\20.05.2013\deneme.bak'
DECLARE @DBHeaderOnly table
(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) NULL,
RecoveryModel nvarchar(60) ,
DifferentialBaseLSN numeric(25,0) NULL,
DifferentialBaseGUID uniqueidentifier ,
BackupTypeDescription nvarchar(60) ,
BackupSetGUID uniqueidentifier NULL,
CompressedBackupSize numeric(20,0)
)
-- işletim sisteminden okuyacağımız backup dosyalarının ismi
DECLARE @OSFileList TABLE (FileNumber INT IDENTITY,FileName VARCHAR(256))
--restore edilecek veritanları ve restore komutu tutulacaktır
DECLARE @OSRestoreList TABLE (FileNumber INT IDENTITY, FileName VARCHAR(256), DBNAME VARCHAR(256), Command varchar(8000) )
--bu işletim sitemindeki dosyaları listeleceyektir
DECLARE @Command varchar(1024) = 'dir '+ @backupfolder + ' /A-D /B'
--işletim sisteminden okunan backup dosyaları @OSFileList değişkenine atanır.
INSERT INTO @OSFileList
EXEC MASTER.dbo.xp_cmdshell @Command
DECLARE @FileNumber int
DECLARE @FileName varchar(1000)
DECLARE @RestoreFileName varchar(1000)
--cursor tanımla
DECLARE db_cursor CURSOR FOR
SELECT Distinct FileNumber, FileName
FROM @OSFileList
WHERE FileName is not null
and FileName like '%BAK'
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @FileNumber, @FileName
DECLARE @DbName varchar(100)
WHILE @@FETCH_STATUS = 0
BEGIN
-- restore edilecek backup dosyası dizin adı ile birlikte alıyoruz
SET @RestoreFileName = @backupfolder+'\'+ @FileName
SET @RESTOREDBName = ''
--Bakup dosyasının HeaderOnly bilgisini @DBHeaderOnly tablo değişkenine atıyoruz.
SET @tmp = N'RESTORE HEADERONLY FROM DISK=N''' + @RestoreFileName + ''' WITH FILE=1' ;
INSERT INTO @DBHeaderOnly
EXEC(@tmp);
--Restore edilecek veritabanını "restore headeronly from ..." komutundan oluşturduğumuz tablodan okuyoruz
SELECT @RESTOREDBName = DatabaseName
from @DBHeaderOnly
--Backup dosyasından içerisindeki data file listesini okuyup @DBFileList tablosuna aktarıyoruz
SET @tmp = N'RESTORE FILELISTONLY FROM DISK=N''' + @RestoreFileName + ''' WITH FILE=1' ;
INSERT INTO @DBFileList
EXEC(@tmp);
--Data dosyalarının logical name'lerini kullanarak yeni lokasyonlarını MOVE-taşıma sciptini oluşturuyoruz
SET @CumulativeMDFRestore = '';
SELECT @CumulativeMDFRestore = @CumulativeMDFRestore +' MOVE '+' '''+ LogicalName + ''' TO '''+@RestoreDataFolder+'\'+ REVERSE(SUBSTRING(REVERSE(PhysicalName), 1, CHARINDEX('\', REVERSE(PhysicalName)) - 1)) +''', '
from @DBFileList
where [Type]='D';
--LOG dosyalarının logical name'lerini kullanarak yeni lokasyonlarını MOVE-taşıma sciptini oluşturuyoruz
SET @CumulativeLogRestore = '';
SELECT @CumulativeLogRestore = @CumulativeLogRestore +' MOVE '+' '''+ LogicalName + ''' TO '''+@RestoreDataFolder+'\'+ REVERSE(SUBSTRING(REVERSE(PhysicalName), 1, CHARINDEX('\', REVERSE(PhysicalName)) - 1)) +''', '
from @DBFileList
where [Type]='L';
--Data ve LOG dosyaları dışında veritabanında başka dosyalar varsa onların logical name'lerini kullanarak yeni lokasyonlarını MOVE-taşıma sciptini oluşturuyoruz
SET @CumulativeOTHERRestore = '';
SELECT @CumulativeOTHERRestore = @CumulativeOTHERRestore +' MOVE '+ ' '''+ LogicalName + ''' TO '''+@RestoreDataFolder+'\'+ REVERSE(SUBSTRING(REVERSE(PhysicalName), 1, CHARINDEX('\', REVERSE(PhysicalName)) - 1)) +''', '
from @DBFileList
where [Type] not in ('L', 'D');
-- Restore komutu için script oluşturuyoruz
IF @RESTOREDBName <>''
BEGIN
SET @tmp = 'RESTORE DATABASE [' + @RESTOREDBName+']'+
' FROM DISK = '''+@RestoreFileName+''''+
' WITH '+ @CumulativeMDFRestore
+ @CumulativeLogRestore
+@CumulativeOTHERRestore+
' RECOVERY, STATS = 10';
END
ELSE
BEGIN
SET @tmp = '******HATA***** '+@RestoreFileName
END
--Oluşturduğumuz RESTORE komutunun sciriptini backup dosya adi, restore edilecek veritabanı adi ve restore komutu olarak sonuç tablosuna ekliyoruz
INSERT INTO @OSRestoreList(FileName, DBNAME, Command )
SELECT @RestoreFileName,@RestoreFileName,@tmp
delete from @DBFileList
SET @tmp = ''
FETCH NEXT FROM db_cursor INTO @FileNumber, @FileName
END
CLOSE db_cursor
DEALLOCATE db_cursor
--Oluşturduğumuz Restore komutlarını listeliyoruz
SELECT Command+char(13)+char(10)+'GO'+char(13)+char(10)
FROM @OSRestoreList