SQL Server ASP.NET C# Kitabı

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,

Recovery iÅŸlemine örnek olarak etutorials.org’dan alınmış aÅŸağıdaki ÅŸekli inceleyelim.

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('', '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',
    '\\\\
\.bak';

--Sistemde bir teyp yedek dosyası oluşturmak için
EXEC sp_addumpdevice 'tape', 'tapedump1', '\\.\tape0';

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 ( ‘‘ | , 0 | 1 | 2 | 3 | 4 | -1 )komutu kullanılabilir.