Selamlar,
Bu yazımızda sizlere veritabanından hata sonucu silinen verilerin nasıl kurtarılabileceği hakkında bilgi vereceğim. Bu işlemi yapabiliyor olmamız için veritabanın recovery model bilgisi FULL yada Bulk-Logged olmak zorundadır. Simple modda olan bir veritabanı transaction logs backup’I desteklemediği için bu işlemi uygulayamazsınız.
Öncelikli olarak demo ortamımızı hazırlamak için bir veritabanı ve varsayılan değerler içeren bir tablo oluşturalım. ( Demo ortamımız Windows Server 2012 R2 üzerinde SQL Server 2016 Developer Edition’dır. )
— E:\RecoverDeletedData dosya yolunda RecoverDeletedData isiminde veritabanı oluşturuyoruz. Recovery Mode bilgisi FULL
CREATE DATABASE [RecoverDeletedData]
ON PRIMARY
( NAME = N’RecoverDeletedData’, FILENAME = N’E:\RecoverDeletedData\RecoverDeletedData.mdf’ , SIZE = 8192KB , FILEGROWTH = 524288KB )
LOG ON
( NAME = N’RecoverDeletedData_log’, FILENAME = N’E:\RecoverDeletedData\RecoverDeletedData_log.ldf’ , SIZE = 8192KB , FILEGROWTH = 1048576KB )
GO
ALTER DATABASE [RecoverDeletedData] SET RECOVERY FULL
— Oluşturduğumuz Veritabanı üzerinde Orders isimli bir tablo oluşturuyoruz.
use RecoverDeletedData
go
create table Orders
(
OrderId int not null primary key identity(1,1),
OrderDate DATETIME DEFAULT GETDATE ( ) ,
OrderItem VARCHAR ( 25 ) DEFAULT ‘MyItem’ ,
LineItem VARCHAR ( 25 ) DEFAULT ‘MyLineItem’
)
Demo için kullanacağımız veritabanını oluşturduk. İçerisinde Orders isiminde tablomuz da var. Şimdi bunların aşağıdaki kod ile Full Backup ve Transaction Log Backup alıyoruz.
— E:\RecoverDeletedData diskine RecoverDeletedData_FULL.bak isiminde Full Backup Alıyoruz.
BACKUP DATABASE [RecoverDeletedData]
TO DISK = N’E:\RecoverDeletedData\RecoverDeletedData_FULL.bak’ WITH INIT
— E:\RecoverDeletedData diskine RecoverDeletedData_FULL.bak isiminde Transaction Log Backup Alıyoruz.
BACKUP LOG [RecoverDeletedData]
TO DISK = N’E:\RecoverDeletedData\RecoverDeletedData_LOG_1.trn’ WITH INIT
GO
RecoverDeletedData isimli veritabanında bazı insert işlemler gerçekleştirelim. Aşağıdaki gibi benzer sorgular ile belirli zaman aralıklar sorgu çalıştırıp data oluşturdum.
INSERT INTO Orders DEFAULT VALUES ;
GO 100
waitfor delay ’00:00:10′
GO
Oluşan demo datası üzerinde Delete işlemi gerçekleştiriyorum.
delete from Orders where OrderId in ( 2,10,11,5,7,30)
Şimdi Aşağıdaki fn_dblog sorgusu ile delete işlemin LSN bilgisini ve işlem zamanı bilgisini öğrenelim.
Yukarıdaki TransactionID bilgisini yedekleyelim, aşağıdaki sorguda kullanıp hangi kayıtların silindiği bilgisine ulaşacağız. Yukarıda kullandığım Delete sorgusu sonucunda 6 kayıt silinmişti, aşağıdaki sorgunun sonucuda 6 kayıt dönüyor.
SELECT [Current LSN], [Transaction ID], [Operation], [AllocUnitName]
FROM fn_dblog(NULL, NULL)
WHERE 1=1
and [Transaction ID] = ‘0000:00000398’
and [Operation] = ‘LOP_BEGIN_XACT’
Yukarıdaki gördğümüz Current LSN numarasını bir yere not almamızda fayda var, ihtiyacımız olacaktır. Aşağıdaki sorgu ile mevcut veritabanı farklı isim ile aldığımız full backup’dan restore ediyoruz.
USE RecoverDeletedData
GO
RESTORE DATABASE RecoverDeletedData_COPY FROM
DISK = ‘E:\RecoverDeletedData\RecoverDeletedData_FULL.bak’
WITH
MOVE ‘RecoverDeletedData’ TO ‘E:\RecoverDeletedData\COPY\RecoverDeletedData.mdf’,
MOVE ‘RecoverDeletedData_log’ TO ‘E:\RecoverDeletedData\COPY\RecoverDeletedData_log.ldf’,
REPLACE, NORECOVERY;
GO
Transaction Log Backup restore ederken STOPBEFOREMARK işlemini kullanacağız. STOPBEFOREMARK kullanbilmek için daha önce aldığımız Current LSN numarasının onluk sayı sistemine çevirmemiz gerekiyor. Örneğimizdeki Current LSN numarası ( 00000022:00000193:0001 ) 3 parça olarak alıglıyoruz. Birinci Kısım ( 00000022 ), İkinci Kısım ( 00000193 ) ve üçüncü kısım ( 0001 ) dır. Bu değerleri şimdi bu web sitesi aracılığı ile 10’luk sayı sistemine çeviriyoruz.
00000022 -> 34
00000193 -> 403
0001 -> 1
Yukarıdaki değerleri birleştirip kendimize STOPBEFOREMARK’ın Kabul edeceği decimal bir değer elde ediyoruz. ( 3400000004030000000001 )
STOPBEFOREMARK özelliği için decimal değeri oluşturmak için web sitesini kullanmadan da aşağıdaki sorguyu kullanıp STOPBEFOREMARK’ın decimal değerini elde edebiliriz.
SELECT CAST(CAST(CONVERT(VARBINARY, LEFT([Current LSN], 8), 2 ) AS INT) AS VARCHAR) + RIGHT(‘0000000000’ + CAST(CAST(CONVERT(VARBINARY, SUBSTRING([Current LSN], 10, 8), 2) AS INT) AS VARCHAR), 10 ) + RIGHT(‘00000′ + CAST(CAST(CONVERT(VARBINARY, RIGHT([Current LSN], 4), 2 ) AS INT) AS VARCHAR), 5) AS [Decimal LSN] , [Transaction Name], [Begin Time] FROM fn_dump_dblog (NULL, NULL, N’DISK’, 1, N’E:\RecoverDeletedData\RecoverDeletedData_LOG_2.trn’,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)
WHERE [Transaction Name] IN (‘DELETE’)
Öncelikli olarak ilk aldığımız Transaction Log’u restore edelim.
RESTORE LOG [RecoverDeletedData_COPY] FROM DISK = N’E:\RecoverDeletedData\RecoverDeletedData_LOG_1.trn’ WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
Şimdi ise e lde ettiğimiz Decimal LSN numarasını nihayet STOPBEFOREMARK ile kullanabilir durumdayız.
USE RecoverDeletedData
GO
RESTORE LOG RecoverDeletedData_COPY FROM DISK = N’E:\RecoverDeletedData\RecoverDeletedData_LOG_2.trn’ WITH STOPBEFOREMARK = ‘lsn:34000000040300001‘
Ve nihayet bu kadar işlemin sonunda silinen dataları kuratabilirdik.
Bir sonraki makale de görüşmek üzere.