SQL SERVER’da Silinen Verileri nasıl kurtarılır ?

By | 20 February 2017

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.

Share

Leave a Reply

Your email address will not be published. Required fields are marked *