SQL Server’da BACKUP RESTORE işlemi nasıl yapılır?

Bütün veri tabanı sistemlerinde verilerin yedeklenmesi(BACKUP) ve gerektiği zaman yedeklerden geri dönülmesi(RESTORE) en çok kullanılan servislerinden biridir. Her veri tabanı sisteminin dosya ve veri formatı farklı olduğu kendilerine özgü BACKUP-RESTORE işlemleri sunar. Gerek kullanıcıların(veri tabanı yönetici veya geliştiricilerin) gerekse bilgisayar yazılımlarının(virüs, zararlı scriptler) gerekse doğal felaketlerin neden olacağı beri kaybını azaltmanın ilk süreci, doğru bir yedekleme stratejisinin oluşturulması ve bu strateji doğrultusunda düzenli olarak yedek alınmasıdır. Yedeklerin ne sıklıkla veya ne türde alınacağı veya nereye alınacağı(disk, type)tamamıyla o veri tabanının hangi amaçla ve ne yoğunlukta kullandığıyla ilgilidir. SQL Server, gerek doğrudan script yardımlarıyla gerekse sağladığı araçlarla bu işlemlerin hızlı ve kolayca yapılmasını sağlamaktadır. Aynı zaman bu işlemlerin otomatiğe bağlanıyor olması da önemli bir kolaylık sağlamıştır.
Yedekleme stratejisi kapsamında günümüzde 5 çeşit backup yöntemi kullanılır.
Full backups only
Full backups with differentials
Full backups with transaction logs
Full backups with differentials and transaction logs
File ve File Group backup
Bu yazıda SQL Server sisteminde bu yöntemler seviyesinde nasıl yedekleme ve geri yükleme işlemlerinin yapılacağını işleyeceğiz.
Yedekleme işleminin amacı, istenmeyen bir durum oluştuğunda veya veri kaybı yaşandığında yedek dosyaları üzerinden geriye dönüş yapabilmektir. Bu aşamada yedek alımının kapsamı, “Recovery Model” denilen geri yükleme planıyla ilişkili bir durumdur. Recovery Model, transactionların nasıl loglanacağını, ne kapsamda saklanacağın ve ne tür restore işlemlerinin yapılabileceğini içerir. SQL Server üzerinde yapılan tüm işlemler öncelikle transaction log üzerinde gerçekleştirilir. Bu işlemler daha sonra checkpoint process tarafından data dosyalarına yansıtılır. Değişiklikler onaylandıktan sonra log dosyasında bu işlem bilgilerinin hala tutulup tutulmayacağı recovery model ile ilgilidir.
Recovery Model(Kurtarma Model) Seçimi
Veri tabanları için seçebileceğimiz 3 çeşit recovery modeli bulunur : simple, full ve bulk-logged. Bu modellerin doğr u kavranılması için başarısızlık durumlarında yardıma koşan Transaction Log’un nasıl çalıştığını ve Checkpoint mantığını bilmekte fayda olacaktır.
Her SQL Server üzerinde koşan veri tabanının tüm değişikliklerin sırasıyla kayıt edildiği bir transaction log’u bulunur. SQL Server, transaction log içerisinde her log kaydını birbirinden ayırmak için her kayda özgü LSN(Log Sequence Number) denilen sıra numarası verir.
Bir veri tabanı uygulamasından SQL Server’e değişiklik talebi(DELETE, UPDATE, INSERT) geldiği zaman gönderilen sorguya uygun kayıtların bir kopyası memory alanına(buffer cache) alınır ve değişiklikler dirty pages denilen bu kayıtlar üzerinde yapılır bu arada değişiklik ifadeleri log içine kayıt edilerek başlangıç ve bitiş noktaları(LSN değerleri) belirlenir. Başarılı bir şekilde biten log transaction içerisindeki iş parçacığı checkpoint process veya Lazy Writer processtarafından disk üzerindeki data page’lere eş zamanlı olarak yazılır ve bu noktada checkpoint denilen aşama oluşmuş olur. Checkpoint aşaması, veri tabanı işlem tarihçesinde durum değişikliklerinin diske başarıyla yazıldığı noktadır. Yani SQL Server o noktaya kadar başarılı, sorunsuz bir şekilde gelmiş anlamına gelir. Recovery aşamasında SQL Server her database için transaction log dosyasını gözden geçirir ve log dosyasında gerçekleşmiş olan kayıtların database dosyasında da gerçekleşip gerçekleşmediğini doğrular. Ayrıca sistem başarısız(failure) olmadan önce database’de değişikliğe neden olmuş ancak tümüyle tamamlanmamış transactionların olup(active transaction) olmadığını da log içerisinde denetler. Yani recovery aşamasında log dosyası bir anlamda muaneye edilir ve sistem üzerinde en sonki değişikliklerle ilgili bir sorunun olup olmadığı kontrol edilmiş olur. SQL Server her start olduğunda bu türden bir recovery süreci çalıştırır. Internal recovery denilen bu onarım işleminde log dosyasında en sonki checkpoint noktasına konumlanılır. Bu noktada tamamlanmış olan transactionları(log dosyasında commit edilmiş ama data file’e yazılmamış) database’e yansıtmak üzere ileri gönderir(roll forward) ve tamamlanmamış transactionları da geri döndürür(roll back). Bütün değişiklikler redone veya undone yapıldıktan sonra database checkpoint edilmiş olur ve recovery işlemi bitmiş olur. Bu işlemleri yürüten checkpoint process, işlemler bittikten sonra buffer alanlarını temizler.
Recovery aşamasında taranılacak olan alan Minimum Recovery LSN (MinLSN) denilen numaradan başlanır. MinLSN, checkpoint işleminin başlangıç LSN değerini ve en eski aktif transactiona ait LSN değerini(Oldest Active Transaction) belirtir. Aynı zamanda bu LSN değeri, replikasyon işlemlerinde subscriber’lar tarafından henüz replike edilmemiş transaction kayıtlarının ilkini bildirir.
MinLSN değerinin nasıl bir değer olduğunu aşağıdaki şekilde üzerinde gösterelim.

Bu listede LSN 148, Transaction Log’un en son kaydıdır. Aynı zamanda Checkpoint işleminin LSN 147’de gerçekleştiğini görüyoruz. Bu aşamada Tran 1, COMMIT edilmiş durumda ve Tran 2 hala açık işlem(active transaction) olarak duruyor. Bu durumda Tran 2’nin ilk log kaydı, en son ki checkpoint’in başlangıcını belirler. Bu da LSN 142’dir. Checkpoint işlemin MinLSN olan LSN 142’den itibaren yapılmaya başlanır.
Checkpoint,
CHECKPOINT ifadesi kullanıldığı zaman,
ALTER DATABASE ile veri tabanı özelliğinde bir değişiklik yapıldığı zaman,
SQL Server, SHUTDOWN ifadesiyle veya doğrudan servis kapatmasıyla kapanacağı zaman
çalışır. Bununla birlikte SQL Server, otomatik olarak belli aralıkla sistemin sağlığı açısından checkpoint işlemini gerçekleştirebilir.
Recovery işlemine örnek olarak etutorials.org’dan alınmış aşağıdaki şekli inceleyelim.

T1 isimli transaction, en sonki checkpoint işleminden önce başlamış ve COMMIT edilmiş. Bu transaction’ın recovery edilmesine gerek kalmadığı için es geçilir.
T2 isimli transaction, en sonki checkpoint işleminden önce başlamış ancak sistemin crash olduğu zaman itibariyle henüz tamamlanmamıştır. Bu yüzden bu transaction neden olduğu değişiklikler checkpoint process tarafından roll back edilir.
T3 isimli transaction, en sonki checkpoint işleminden önce başlamış ve checkpoint’ten sonra ve sistem crash olmadan önce COMMIT edilmiş. Yani log tarafında COMMIT edilmiş ama henüz database’e aktarılmamış bu yüzden Checkpoint’ten sonra yapılmış log değişiklikleri database’e yansıtılmak üzere roll forward edilir.
T4 isimli transaction, en sonki checkpoint işleminden sonra başlamış ve COMMIT edilmiş. Bu transaction’ın sonuçları roll forward edilir.
T5 isimli transaction, en sonki checkpoint’ten son başlamış fakat log tarafında herhangi bir data değişikliği yapmamış dolayısıyla database’e yazılacak herhangi bir veri değişikliği bulunmamaktadır. Bu transaction için herhangi bir undo işlemi gerçekleşmez.
Yeri gelmişken söyleyelim. Bir veri tabanı üzerindeki açık transactionları görmek için DBCC OPENTRAN kullanılır.
DBCC OPENTRAN
[
( [ 'database_name' | database_id | 0 ] ) ]
{ [ WITH TABLERESULTS ]
[ , [ NO_INFOMSGS ] ]
}
]
Bir transaction işleminde bulunalım ve transaction bitmeden açık transaction listesini sorgulayalım.

BEGIN TRAN
	DELETE FROM Musteri WHERE MusteriId=1
	DBCC OPENTRAN("Deneme")
ROLLBACK TRAN

(1 row(s) affected)
Transaction information for database 'Deneme'.
Oldest active transaction:
SPID (server process ID): 53
UID (user ID) : -1
Name : user_transaction
LSN : (38:250:35)
Start time : Jun 1 2007 5:20:49:373PM
SID : 0x010500000000000515000000a837d6653b245e6907e53b2bf4010000
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Şimdi SQL Server’in sunduğu recovery modellerine geçebiliriz.
Simple Recovery Model
Bu seçenekte, log dosyası düzenli olarak truncate edilerek minimum düzeyde loglama yapılır. Her chekpoint işleminden sonra aktif olmayan transaction kayıtları, Transaction Log dosyası içinden silinir. Bu recovery modeli, çok kritik olmayan veri tabanları, üzerinde fazla güncellemelerin yapılmadığı veri tabanları, read-only veri tabanları ve raporlama amaçlı olup OLTP kaynaktan beslenen OLAP veri tabanları için tercih edilebilir. Simple recovery’de en son alınmış yedekten bu yana yapılan değişiklikler kayıt altına alınmadığı için o aradaki değişiklikler geri alınamaz.
Full Recovery Model
Bu seçenekte SELECT INTO, CREATE INDEX, bcp ve bulk loading data gibi toplu operasyonlar dahil olmak üzere tüm transaction’lar loglanır, kayıt altına alınır. Veri kaybı riskinin büyük olduğu üretim tabanlı veri tabanlarında tercih edilen bu modelde, recovery durumunda herhangi bir ana geri dönülebilir. Böylece sistem üzerinde yapılan değişiklikler son ana kadar kurtarılabilir. Log dosyasının büyümesine neden olan seçenektir.
Bulk-Logged Recovery Model
Simple ve Full recovery seçenekleri arasında duran bu seçenekte, bulk operasyonları dışındaki tüm işlemler loglanır. Bulk işlemlerinde sadece hayati durumlar loglandığı için bulk operasyonlarda performansı artıran bir seçenektir. BULK operasyonlar tek bir transaction gibi işlendiği için bu operasyon esnasında istenmeyen bir durum oluştuğu zaman bu süreçte yapılmış olan değişiklikler kurtarılamaz.
Bir veri tabanının recovery model’ini T-SQL’de “ALTER DATABASE” ile değiştirebileceğimiz gibi Management Studio üzerinden de değiştirebiliriz.

--Simple recovery model
ALTER DATABASE Deneme SET RECOVERY SIMPLE

--Full recovery model
ALTER DATABASE Deneme SET RECOVERY FULL

--Bulk-Logged recovery model
ALTER DATABASE Deneme SET RECOVERY BULK_LOGGED

Management Studio’da değişiklik yapacağımız veri tabanının sağ tıklayıp Properties bölümündeki Options sekmesinden yapılır.
Veri tabanının hangi recovery modunda olduğu aşağıdaki gibi okunabilir.

SELECT DATABASEPROPERTYEX(‘VeriTabaniAdi’,’Recovery’)

SQL Server’de Yedekleme İşlemi
SQL Server üzerinde backup işlemi yapılmadan önce backupların konulacağı sürücülerin tanımlanması gerekir. Permanent backup file veya backup device olarak tanımlanan bu sürücüler, sp_addumpdevice stored procedure veya Management Studio aracılığıyla oluşturulur. Bir device oluşturulurken sürücünün tipi belirtilir ve sürücüye mantıksal ve fiziksel bir isim verilir. Sürücünün tipi, disk, pipe veyatape olabilir. Mantıksal isim, kendisine erişilirken kullanılan rumuzdur, fiziksel isim ise yedeğin saklanacağı diskte veya teypin yolunu bildirir.
sp_addumpdevice [@devtype =] 'device_type',
[@logicalname =] 'logical_name',
[@physicalname =] 'physical_name'
[, {
[@cntrltype =] controller_type
|
[@devstatus =] 'device_status'
}
]
Oluşturulan sürücüler, master veri tabanını altındaki sysdevices tablosunda tutulur.

--Disk üzerinde bir backup dosyası oluşturmak için
EXEC sp_addumpdevice 'disk', 'mydiskdevice', 'c:\Yedek.bak';

--Network üzerindeki bir paylaşımda backup dosyası oluşturmak için
EXEC sp_addumpdevice 'disk', 'mynetworkdevice',

Aynı işlemi Management Studio üzerinde “Object Explorer içerisindeki” “Server Objects” altındaki “Backup Devices” bölümü sağ tıklanarak yapılabilir.

Şimdiye tanımladığımız yedek dosyaları, “permanent backup file” olarak yani sistemde kalıcı olacak şekilde oluşturuldu. Eğer yedek dosyaları sadece bir kereliğine kullanılacaksa yedekleme esnasında “temporary backup file” olarak yani geçici dosyalar olarak ta oluşturulabilir. Bunu da yedekleme işlemini başlatmak için kullandığımız “BACKUP DATABASE” komutuyla yapabiliriz.
Yedekleme işlemini başlatmak için “BACKUP DATABASE”, restore işlemleri için “RESTORE DATABASE” komutları kullanılır.
BACKUP DATABASE { database_name | @database_name_var }
TO [,.n]
[WITH
[[,] FORMAT]
[[,] {INIT | NOINIT}]
[[,] RESTART]
]
. . . .

INIT veya NOINIT : Bu parametreler, alınan yedeğin var olan yedekleme dosyasına ekleneceği veya üzerine yazılacağını belirtir. NOINIT seçeneğinde, alınan yedek, yedekleme dosyasının sonuna eklenir. Bu seçenek, defaulttur. INIT seçeneğinde ise yedek, yedekleme dosyasının üstüne yazılır.
FORMAT : seçeneği, yedekleme dosyasının içeriğinin üzerine yazmak için kullanılır. Daha önce alınmış yedekler geçersiz olur.
RESTART : seçeneği, SQL Serverin yedekleme işlemini kesildiği yerden devam ettirmesini sağlar.
SQL Server üzerinde 5 çeşit backup alınabilir:
Full Backup(Tam Yedekleme)
Veri tabanının o andaki tüm yedeğinin alındığı yedekleme şeklidir. Bu yedekleme türünden geri dönmek için tüm backup dosyasını restore etmemiz yeterlidir. Fulll backup alındığı zaman o anda onaylanmamış transactionlar da yedeklenir.
Aşağıdaki örnekte dvcDeneme isimli bir permanent backup file oluşturulup Deneme veri tabanının yedeği bu yedekleme dosyasının üzerine yazılmıştır.

EXEC sp_addumpdevice 'disk', 'dvcDeneme', 'C:\Deneme.bak' 

BACKUP DATABASE Deneme TO dvcDeneme

Aşağıdaki örnekte bir temporary backup file oluşturulup Deneme veri tabanının yedeği bu yedekleme dosyasının üzerine yazılmıştır.

BACKUP DATABASE Deneme TO DISK = 'C:\Deneme.bak'

Full database backup stratejisi, daha çok küçük veri tabanlarında, yoğun transactionların olmadığı veri tabanlarında, kısa süreli veri kaybının önemsenmediği veri tabanlarında, yedeklemenin veri tabanlarında veya read-only veri tabanlarında uygulanır.
Differential Backup (Fark Yedeği)
En son alınmış olan full backuptan bu yana değişmiş kayıtların alındığı yedekleme şeklidir. Bu yedekleme türünü kullanabilmek için daha önce bir full backup almış olmak gerekir. Differential backup yöntemi, full backup yöntemine göre daha hızlı ve kısa sürede gerçekleştiği için daha çok büyük veri tabanlarında tercih edilir. Bu yedekleme türünden geri dönmek için öncelikle ilk başta alınmış full backup restore edilir ardından alınmış differential backup restore edilir. Differential backup yönteminde full backup’tan sonra bir nesne üzerinde birçok kez değişiklik yapılmışsa differential backup, o nesnenin son değerini içerir.
BACKUP DATABASE {database_name | @database_name_var}
TO [,.n]
[WITH
[[,]DIFFERENTIAL]
]
Aşağıdaki satırlarda temporary backup file üzerinde differential backup alınmıştır.

BACKUP DATABASE Deneme
	TO DISK='C:\Deneme.bak' WITH DIFFERENTIAL

SQL Server, bilindiği gibi her veri tabanı için varsayılan olarak MDF(Data dosyası) ve LDF (Log dosyası) dosyalarını oluşturur. MDF dosyaları 64 K boyutunda olan extent denilen bloklardan oluşur. Her extent içerisinde 8 Page bulunur. Sistem üzerinde full backup alındıktan sonra veri tabanındaki tüm extentlerin değişip değişmediği flag bilgisine 0 atar. Daha sonra herhangi bir extent değişirse onun flag değerini 1 yapar. Differential backup alınacağı zaman bu flag bilgilerine bakılır ve sadece değişime uğraşmış extent’ler yedek dosyasına konulur.
Differential backup aldığımız zaman en son alınmış full backup’tan bu yana alınmış log backuplar birleştirilmiş olur. Böylece restore işlemlerindeki adımlar kısaltılmış olur. Ayrıca Differential backup alındığı zaman transaction log düzeyinde bir bilgi tutulmaz bu yüzden bu yöntemle birlikte transaction logların düzenli olarak backuplanması veri kaybını azaltacaktır.
Transaction Log Backup (İşlem Günlüğü Yedeği)
Veri tabanındaki tüm değişiklikleri yedeklemek için kullanılır. Bu yedekleme türünü de kullanmak için daha önce en azından bir kere full backup alınmış olması gerekir. Transaction Log backup yönteminin en önemli özelliği belli bir andaki değişikliğe geri dönülmesini desteklemesidir. “BACKUP LOG” komutu kullanılır.
Aşağıdaki örnekte temporary backup file oluşturulmuş ve Deneme veri tabanının transaction logu yedeklenmiştir.

BACKUP LOG Deneme TO DISK='C:\Deneme.bak'

Veri kaybı riskinin fazla olduğu, yoğun sayıda transactionların olduğu veri tabanlarında transaction log’un belli peryotlarda yedeğinin alınması gerekir. Recovery aşamasında bu log yedekler sırasıyla yüklenir. Bu da doğal olarak zaman alıcı bir durumdur bu yüzden sistemin yoğun olmadığı durumlarda full backup ile log backuplar arasına differential backup oluşturmak transaction log backup dosyalarının sayısını azaltacaktır.
Transaction Log backup, Simple recovery model’de alınamaz yalnızca Full ve Bulk-Load recovery model’lerinde alınabilir. Recovery modellerinin backup kapsamları aşağıdaki şekilde gösterilmiştir.

Full backup, diğer backup yöntemleriyle alınmış yedekleri geri yüklemek için başlangıç noktası teşkil eder. Yani öncelikle elimizdeki full backup recovery edilir. Dolayısıyla hangi backup yöntemini kullanırsak kullanalım öncelikle bir full backup almamız gerekecek.
Kurumsal ortamlarda, production veri tabanlarında öncelikle bir full backup alınır ve transaction log backupların sayısının fazla olmasından dolayı sık sık differential backup alınır ve istenmeyen bir durum oluştuğu zaman veya sistem crash olduğu zaman hemen bir log backup alınır. Restore işlemi de alınmış backupların sırasına göre yapılır. En son log backup recovery edilirken istenilen ana geri dönülür.
File ve File Group backup
Birden fazla file veya filegroup’a sahip veri tabanlarında file veya filegroupların full veya differential yedeği alınabilir. Çok büyük veri tabanlarında(very large database) tüm veri tabanı yedeğinin alınması saatler sürebileceği için data file bazında yedek almak daha mantıklı olacaktır.

BACKUP DATABASE Deneme
   FILE = 'Deneme_data'
   TO DISK = 'C:\Dnm_File.bak'

Processed 176 pages for database 'Deneme', file 'Deneme_data' on file 1.
Processed 2 pages for database 'Deneme', file 'Deneme_log' on file 1.
BACKUP DATABASE...FILE= successfully processed 178 pages in 0.743 seconds (1.961 MB/sec).

BACKUP DATABASE Deneme
   FILEGROUP = 'PRIMARY'
   TO DISK = 'C:\Dnm_Filegroup.bak'

Processed 176 pages for database 'Deneme', file 'Deneme_data' on file 1.
Processed 1 pages for database 'Deneme', file 'Deneme_log' on file 1.
BACKUP DATABASE...FILE= successfully processed 177 pages in 0.720 seconds (2.013 MB/sec).
Mirrored Backup
Yukarıda kullandığımız BACKUP komut satırları, yedeğin yalnızca 1 kopyasını oluşturuyordu. SQL Server 2005 Enterprise Edition ile birlikte sunulan MIRROR TO parametresi kullanılarak, alınan yedeğin en fazla 4 olmak üzere farklı konumlara kopyalanması sağlanabilir. Aşağıdaki örnekte Deneme veri tabanının yedeği 2 farklı yere alınmıştır.

BACKUP DATABASE Deneme TO DISK='C:\Deneme.Bak'
            MIRROR TO DISK='D:\Deneme.Bak'
WITH FORMAT

Buradaki her mirror backup’ın aynı türden(disk, type, pipe) olması gerekir.
Aynı şekilde yedeği eş zamanlı olarak birden fazla device’a yazdırabiliriz.

BACKUP DATABASE Deneme TO device1, device2, device3

NO_TRUNCATE ve TRUNCATE_ONLY seçenekleri
Bozulmuş veri tabanlarında tercih edilen NO_TRUNCATE seçeneği, veri tabanındaki tüm işlemleri yedekler. Herhangi bir nedenden dolayı data dosyası bozulmuş veya erişilemez durumda(örneğin suspect moduna düşmesi) ama log dosyası ayakta olan veri tabanının, NO_TRUNCATE seçeneği kullanılarak tüm transaction log’unun yedeği alınabilir. NO_TRUNCATE seçeneği, veri tabanınına erişemezse bile en sonki güncel log dosyasının yedeğini alır.

BACKUP LOG Deneme TO DISK='C:\Deneme.bak' WITH NO_TRUNCATE

Eğer database’in yedeğini almak istemiyor yalnızca transaction log’u temizlemek istiyorsak TRUNCATE_ONLY parametresi kullanılır. Transaction Log’un temizleniyor olması, aktif olmayan transaction parçalarının siliniyor olmasıdır. Ayrıca NO_LOG parametresi de bu işlem için tercih edilebilir.
Transaction Log dosyasının dolma riskinden dolayı belli aralıklarla transaction logu temizlemek gerekir. Bu amaç için trunc. log on chkpt. seçeneği aktifleştirilebilir(true yapılır). Bu seçenek, her checkpoint sonrası transaction log otomatik olarak kısaltılır. Bu durumda transaction log, son Full backup’dan beri yapılan değişiklikleri içermeyecektir bu da Production veri tabanlarında tavsiye edilen bir durum değildir. Bu tür sistemlerde transaction log’a yeterince yer tahsis edilmelidir. Transaction log dolarsa veri tabanı üzerinde değişiklik yapılamaz.
Herhangi bir veri tabanının logu hakkında bilgi almak için DBCC LOGINFO (‘‘) veya daha detaylı bilgi veren DBCC LOG ( ‘veri tabanı adı’ | dbid , 0 | 1 | 2 | 3 | 4 | -1 )komutu kullanılabilir.