SQL Server’da Backup Stratejileri-4 Transaction Log Backup

Merhaba,

SQL Server backup serimize kaldığımız yerden devam ediyoruz. Önceki bölümlerde

• Full Backup
• Differential Backup
• Backup Compression
• Backup ihtiyaç hesaplama

Gibi konular üzerinde durmuştuk.

Hatırlayalım.

Full backup sistem üzerinde anlık olarak tüm database’in yedeğini alır.

Differential backup kendisinden önce alınan full backup ile kendisi arasındaki farkı alır.

Günde 1 kez full backup alırsak riskimiz 1 gün

Günde 4 kez full backup alırsak riskimiz 6 saat

Günde 3-4 kez full backup alır, 30 dakikada bir differential backup alırsak riskimiz 30 dakika demiştik.

Yani aşağıdaki gibi bir backup planımız olsun.

Şimdi burada bir veri kaybı yaşadığımızı düşünelim.

Örneğin saat 16:59’da verimiz silindi.

Elimizde veriyi kurtarabileceğimiz saat olarak en erken 16:30 var. Bu da 29 dakikalık kayıp demek. Şayet siz sürekli akışın olduğu bir e ticaret sistemi iseniz, ya da bant üretimi yapan ve lot takibi yapan bir fabrika iseniz bu süre emin olun çok uzun bir süre. Barkodlarını okutarak kamyona yüklediğiniz ürünlerinizi indirmek zorunda kalabilirsiniz ki bu en kolay senaryo. Bu durumda minimum kayıp durumuna ihtiyacınız var. Örneğin 16:59’dan 1 saniye öncesine 16:58:59’a dönebilmeniz gerekir.

İşte arkadaşlar bu yapıyı sağlayacak yedekleme yöntemi Transaction Log backup.

SQL Server’da veriler iki dosyada tutulur. Biri MDF (Master data file)

Diğeri LDF (Log data file)

SQL Server’da bir veri kaydedilirken önce transaction log dosyasına yani yazılır sonrasında mdf dosyasına yazılır. Bu durum çok basit bir anlatım ile veri bütünlüğünü sağlayabilmek adına  OLTP (Online transactional processing) yapıdaki database’lerin bir özelliğidir.

Yani söyle anlatayım.

Siz saat 16:58:55’de

INSERT INTO SALES diyerek içeriye bir kayıt attınız ve 1 milyon satırlı tablonuz 1 milyon+1 oldu. Bu bilgi transaction log dosyasına yazıldı. Sonra mdf dosyasına yazıldı.

Sonra 16:59:00’da yanlışlıkla DELETE FROM SALES dediniz ve 1 milyon kaydı sildiniz. İşte bu da transaction log dosyasına yazıldı sonra mdf dosyasına yazıldı.

Saat 16:59:01 itibariyle sizin tablonuzda hiç veri yok. Ama 16:59:59 itibariyle tablonuzda 1 milyon+1 kayıt var.

16:59:54 itibariyle 1 milyon kayıt var.

İşte bu saatlarden istediğinize dönebilmenin yolu transaction log backup almaktır.

Şimdi backup planımızı şöyle yapalım. Günde 4 kez full ve yarım saatte bir transaction log backup aldığımızı var sayalım. Bu durumda saat 16:59’da gerçekleşen silme işleminden hemen geriye dönebilmek için saat 12:00’daki full backup ve saat 17:00’daki dahil olmak üzere aradaki transaction log backup dosyalarına ihtiyacımız var. Bu süre içerisinde istediğimiz saate geri dönebiliriz.

Hadi gelin şimdi hep birlikte uygulamamızı yapalım.

Elimizde önceki yazılardan hatırladığımız Sales isimli database’imiz var. Full backup boyutu 275 MB civarında idi.

Her şeyden önce database imizde transaction log backup alabilmek için recovery model’in full olması gerekiyor.

Recovery Model’in full olması ne demek peki?

Log a yazılan her şey mdf dosyasına yazıldıktan sonra da burada kalmaya devam eder. Bu da log un zamanla şişmesine ve fazlaca disk aktivitesine sebep olur. Şayet siz transaction log backup alırsanız işte log dosyası burada temizlenir ve tekrar küçülür.

Her şeyden önce database imizin bir full backup ını alalım.

Burada boyut biraz büyük olsun diye özellikle varchar yerine char veritipini kullandım.

Şimdi bu tablomuza döngü ile kayıt gireceğiz. Kayıt girerken araya biraz zaman girmesi adına her seferinde 100 ms bekleterek kayıt giriyorum.

Evet şu anda tablomuzda full backup aldıktan sonra 13:46:20 ile 13:48:09 saatleri arasında tam 1.000 satırlık kayıt var.

Log dosyamıza baktığımızda da yaklaşık 57 MB görünüyor.

Şimdi transaction log backup alalım.

Ya da script ile bu şekilde alabiliriz.

Görüldüğü gibi log dosyası küçüldü.

Backup dosyamız da aşağıdaki gibi.

Şimdi datamızı tamamen uçuralım.

Gördüğümüz gibi tabloda kayıt yok.

Şimdi yedekten dönelim.

Sistem otomatik olarak backup history den alınan backupları gördü. Burada device diyerek elle biz de ekleyebilirdik.

Timeline diyerek backup’dan dönmek istediğimiz saati seçiyoruz.

Hiçbir şey seçmezsek sistem son Transaction log backup alınan zamanı seçer.

Biz örneğin 13:48:01’i yani 1 dakika öncesini seçelim.

Tekrar lazım olacağı için scriptini oluşturup script üzerinden döneceğim.

Backup’tan döndük. Şimdi bakalım.

Toplamda 922 satır döndü ve son satır 13:48:00.975 yani bizim dediğimiz 13:48:01’i almadan dönmüş oldu.

Şimdi başka bir zaman dönelim. Örneğin 13:45:24’ saatine.

Bu saatte henüz biz böyle bir tabloyu oluşturmadığımız için hata verdi.

Şimdi de 13:46:24’e dönelim.

Aşağıda görüldüğü gibi toplamda 36 satır kayıt döndü ve son satır 13:46:23

Yani biz burada sadece 1 backup dosyası ile ilgili aralıkta istediğimiz zamana dönebildik.

Peki şimdi içeriye biraz daha kayıt atalım.

Şimdi bir kez daha transaction log backup alalım.

Şimdi backuptan dönmek istediğimizde bakalım sistem bize ne diyecek?

Üçünü de gördü.

Şimdi saat seçelim. Örneğin ben 13:48:34 seçince

Full backup + ilk aldığım tlog backup ı getirdi.

Oysa saat 14:20:25’i seçersem,

Hem full hem de 2 transaction log backup’ı getirdi.

Yani buradan çıkaracağımız anlam şu;

Differential backupta sadece bir differential backup ve onun full backup’ı yeterliyken yani aradaki differential backup’a ihtiyacımız yokken transaction log backup’ta aradaki tüm backuplara ihtiyacımız bulunur.

Şimdi buradan restore yapalım.

Görüldüğü gibi 1.036 satır kayıt geldi ve biz istediğimiz en son kayda ulaştık.

Sonuç olarak transaction log backup kritik databaselerde rahatlıkla kullanılabilecek bir backup alma türüdür. Log dosyasını büyüttüğü için ve her hareketin tlog içerisinde işlendiği için sistemi yoran bir backup türüdür.

İlerleyen yazılarımızda bu türleri birbiri ile karışık şekilde nasıl kullanacağımızı ve nasıl otomatize edeceğimizi anlatıyor olacağız.

Umarım faydalı bir yazı olmuştur.

Serinin diğer yazılarında buluşmak üzere.

Sağlıcakla….