SQL Server'da Backup ve Restore: Recovery Model, Full/Differential/Log Backup, Point-in-Time Restore ve Log'un Büyümesi
FULL recovery'de log'u yalnız log backup boşaltır: iki full backup'tan sonra log hâlâ %19,6 dolu ve LOG_BACKUP'ı bekliyordu, 400 MB'lık log sonunda Msg 9002 verdi. Full, differential ve log zinciri, COPY_ONLY'siz bir backup'ın differential'ı nasıl bozduğu (Msg 3136), ZSTD sıkıştırma, yanlışlıkla silinen 618 siparişi STOPAT ve STOPBEFOREMARK ile yan veritabanından geri getirmek ve güvenilmeyen .bak dosyaları.
Sürüm notu: Bu yazıdaki T-SQL örnekleri SQL Server 2025 CU10 (17.0.5035.5, Enterprise Developer Edition) üzerinde, Linux container'da serinin Bookstore laboratuvar veritabanıyla çalıştırılmıştır. Komutlar Windows'taki SQL Server'da aynıdır; değişen yalnız dosya yollarıdır.
Önce Tahmin Et: Full Backup Aldım, Log Neden Hâlâ Dolu?
Serinin laboratuvar veritabanı (sitedeki serilerin ortak örnek uygulaması Bookstore'un, bir online kitapçının veritabanı) 1 milyon sipariş ve 2,5 milyon sipariş kalemi taşıyor. Recovery model'ı FULL yapıp her adımdan sonra log'un durumuna bakan küçük bir view yazdım:
CREATE OR ALTER VIEW dbo.LogState
AS
SELECT d.recovery_model_desc, d.log_reuse_wait_desc,
CAST(u.used_log_space_in_percent AS decimal(5,1)) AS log_used_pct,
CAST(u.total_log_size_in_bytes / 1048576 AS int) AS log_file_mb
FROM sys.databases AS d
CROSS JOIN sys.dm_db_log_space_usage AS u
WHERE d.database_id = DB_ID();Sonra şu sırayla ilerledim: FULL'e geç ve 300.000 ödeme satırını güncelle, full backup al, 600.000 satır daha güncelle, ikinci bir full backup al, en son bir log backup al:
ALTER DATABASE sqls_20 SET RECOVERY FULL;
UPDATE dbo.Payments SET Amount = Amount + 0.01 WHERE PaymentId <= 300000;
CHECKPOINT;
SELECT '0. FULL, no backup yet' AS step, * FROM dbo.LogState;
BACKUP DATABASE sqls_20 TO DISK = '/var/opt/mssql/backup/sqls_20_full_a.bak' WITH CHECKSUM, COMPRESSION, INIT;
SELECT '1. full backup' AS step, * FROM dbo.LogState;
UPDATE dbo.Payments SET Amount = Amount - 0.01 WHERE PaymentId <= 300000;
UPDATE dbo.Payments SET Amount = Amount + 0.01 WHERE PaymentId <= 300000;
CHECKPOINT;
SELECT '2. 600k row updates' AS step, * FROM dbo.LogState;
BACKUP DATABASE sqls_20 TO DISK = '/var/opt/mssql/backup/sqls_20_full_b.bak' WITH CHECKSUM, COMPRESSION, INIT;
CHECKPOINT;
SELECT '3. second full backup' AS step, * FROM dbo.LogState;
BACKUP LOG sqls_20 TO DISK = '/var/opt/mssql/backup/sqls_20_log_b.trn' WITH CHECKSUM, COMPRESSION, INIT;
SELECT '4. log backup' AS step, * FROM dbo.LogState;Üçüncü satırda log kullanımı ne olur? İkinci full backup log'u boşaltır mı? Gerçek çıktı:
step recovery_model_desc log_reuse_wait_desc log_used_pct log_file_mb
---------------------- ------------------- ------------------- ------------ -----------
0. FULL, no backup yet FULL NOTHING 1.0 327
1. full backup FULL NOTHING 1.0 327
2. 600k row updates FULL LOG_BACKUP 19.6 327
3. second full backup FULL LOG_BACKUP 19.6 327
4. log backup FULL NOTHING 2.6 327Full backup log'a dokunmadı. Log'u yalnız log backup boşalttı. "Her gece full backup alıyoruz" diyen bir ekibin FULL recovery'deki veritabanında log dosyası bu yüzden haftalar içinde diski doldurur. İlk satır da ayrıca ilginç: FULL'e geçtikten sonra ilk full backup alınana kadar log hâlâ SIMPLE gibi davranıyor, çünkü henüz başlatılacak bir log zinciri yok. Bu yazıda recovery model'ları, backup türlerini, bir DELETE'i dakikası dakikasına geri almayı, log'un neden büyüdüğünü ve backup'ın nerede, ne sıklıkla, nasıl test edilerek alınacağını gerçek komutlarla inceliyoruz.
Recovery Model ve Log Truncation
Serinin 9. yazısında log'un VLF'lere (transaction log dosyasının iç bölümleri) bölündüğünü ve içindeki kayıtlar gereksiz hâle gelince yeniden kullanıldığını (log truncation) gördük. Recovery model, bir log kaydının ne zaman gereksiz sayılacağını ve buna bağlı olarak hangi restore seçeneklerinin olduğunu belirler:
| Recovery model | Log ne zaman truncate edilir | Log backup | Point-in-time restore |
|---|---|---|---|
SIMPLE | Transaction bitip checkpoint geçince | Yok | Yok; yalnız son full/differential'a |
FULL | Kayıt bir log backup'ına alınınca | Zorunlu | Var, istenen ana kadar |
BULK_LOGGED | FULL gibi | Zorunlu | Toplu işlem içeren log backup'ının içine durulamaz |
Karar kuralı basit: veritabanında son full ya da differential backup'tan sonraki her şeyi kaybetmeyi kabul edebiliyorsan SIMPLE, edemiyorsan FULL. Geliştirici veritabanı, raporlama için her gece baştan yüklenen bir kopya ya da staging veritabanı SIMPLE'da durabilir. Bookstore'un sipariş veritabanı duramaz. BULK_LOGGED büyük bir index rebuild'i ya da toplu yüklemeyi (23. yazıda göreceğiz) minimal log'la yapmak için kısa süre geçilip geri dönülen bir moddur, sürekli kullanılmaz.
Log'un neden truncate edilemediğini her zaman sys.databases.log_reuse_wait_desc söyler. Açılıştaki LOG_BACKUP, 9. yazıdaki ACTIVE_TRANSACTION, replication ya da availability group'ta REPLICATION ve AVAILABILITY_REPLICA en sık görülenleridir. Log büyüdüğünde ilk bakılacak yer burasıdır.
Backup Türleri: Full, Differential, Log ve COPY_ONLY
Full backup veritabanının tamamını ve tutarlı hâle getirmek için gereken log parçasını alır. Differential, son full backup'tan bu yana değişen extent'leri (sekiz page'lik bloklar) alır; SQL Server bunu her data dosyasındaki bir bitmap'ten bilir ve her full backup bu bitmap'i sıfırlar. Log backup, bir önceki log backup'tan bu yana yazılan log kayıtlarını alır ve ardışık log backup'ları kopmayan bir LSN (transaction log'daki her kaydın sıra numarası) zinciri oluşturur. Bu zincir full backup'lardan bağımsızdır: açılıştaki iki full backup log zincirini bozmadı.
Full backup'ın differential bitmap'ini sıfırlaması, pratikte sık yapılan bir hataya yol açar. Haftalık full backup'tan sonra bir geliştirici, test ortamına götürmek için araya kendi full backup'ını alıyor ve işi bitince dosyayı siliyor. Gece de her zamanki differential alınıyor:
BACKUP DATABASE sqls_20 TO DISK = '/var/opt/mssql/backup/sqls_20_full_weekly.bak' WITH CHECKSUM, COMPRESSION, INIT;
UPDATE dbo.StockItems SET QuantityOnHand = QuantityOnHand + 1 WHERE BookId <= 1000;
BACKUP DATABASE sqls_20 TO DISK = '/var/opt/mssql/backup/sqls_20_for_dev.bak' WITH CHECKSUM, COMPRESSION, INIT;
UPDATE dbo.StockItems SET QuantityOnHand = QuantityOnHand - 1 WHERE BookId <= 1000;
BACKUP DATABASE sqls_20 TO DISK = '/var/opt/mssql/backup/sqls_20_diff_nightly.bak' WITH DIFFERENTIAL, CHECKSUM, COMPRESSION, INIT;Ertesi sabah haftalık full ile gecenin differential'ını restore etmeye çalışıyoruz:
RESTORE DATABASE sqls_20_test FROM DISK = '/var/opt/mssql/backup/sqls_20_full_weekly.bak'
WITH MOVE 'sqls_20' TO '/var/opt/mssql/data/sqls_20_test.mdf',
MOVE 'sqls_20_log' TO '/var/opt/mssql/data/sqls_20_test_log.ldf',
NORECOVERY;
RESTORE DATABASE sqls_20_test FROM DISK = '/var/opt/mssql/backup/sqls_20_diff_nightly.bak' WITH RECOVERY;Msg 3136, Level 16, State 1, Server sql1, Line 5
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Server sql1, Line 5
RESTORE DATABASE is terminating abnormally.Nedenini msdb'deki backup geçmişi gösteriyor. Her backup'ın LSN aralığı ve differential'ın hangi full'e dayandığı orada kayıtlı:
SELECT RIGHT(m.physical_device_name, CHARINDEX('/', REVERSE(m.physical_device_name)) - 1) AS file_name,
b.type, b.is_copy_only, b.first_lsn, b.last_lsn, b.differential_base_lsn
FROM msdb.dbo.backupset AS b
JOIN msdb.dbo.backupmediafamily AS m ON m.media_set_id = b.media_set_id
WHERE b.database_name = 'sqls_20'
AND b.backup_start_date >= DATEADD(MINUTE, -10, SYSDATETIME())
ORDER BY b.backup_set_id;file_name type is_copy_only first_lsn last_lsn differential_base_lsn
------------------------ ---- ------------ ----------------- ----------------- ---------------------
sqls_20_full_a.bak D 0 67000000484000001 67000000486400001 NULL
sqls_20_full_b.bak D 0 74000001566400001 74000001568800001 NULL
sqls_20_log_b.trn L 0 67000000484000001 74000001576800001 NULL
sqls_20_full_weekly.bak D 0 74000001586400001 74000001588800001 NULL
sqls_20_for_dev.bak D 0 74000001623200001 74000001625600001 NULL
sqls_20_diff_nightly.bak I 0 74000001658400001 74000001660800001 74000001623200001Differential'ın differential_base_lsn değeri haftalık full'ün değil, silinen sqls_20_for_dev.bak'in first_lsn'i. Bu differential artık yalnız o dosyayla işe yarar. Çözüm COPY_ONLY: copy-only bir full backup bitmap'i sıfırlamaz, copy-only bir log backup da log'u truncate etmez ve zinciri etkilemez. Planlı backup takviminin dışında alınan her backup COPY_ONLY olmalı.
CHECKSUM, COMPRESSION ve RESTORE VERIFYONLY
Yukarıdaki bütün backup'larda iki seçenek var. WITH CHECKSUM backup sırasında her page'in checksum'ını doğrular ve backup dosyasının tamamı için bir checksum yazar; bozuk bir page bulursa backup'ı durdurur. Varsayılanı kapalıdır, yani yazmazsan bozuk bir page'i sessizce backup'a koyabilirsin. COMPRESSION'ın da varsayılanı kapalıdır. SQL Server 2025 yeni bir algoritma getirdi, Zstandard (ZSTD). Aynı veritabanını dört şekilde, zinciri bozmamak için COPY_ONLY ile yedekledim:
BACKUP DATABASE sqls_20 TO DISK = '/var/opt/mssql/backup/sqls_20_c0.bak' WITH COPY_ONLY, CHECKSUM, NO_COMPRESSION, INIT;
BACKUP DATABASE sqls_20 TO DISK = '/var/opt/mssql/backup/sqls_20_c1.bak' WITH COPY_ONLY, CHECKSUM, COMPRESSION, INIT;
BACKUP DATABASE sqls_20 TO DISK = '/var/opt/mssql/backup/sqls_20_c2.bak' WITH COPY_ONLY, CHECKSUM, COMPRESSION (ALGORITHM = ZSTD), INIT;
BACKUP DATABASE sqls_20 TO DISK = '/var/opt/mssql/backup/sqls_20_c3.bak' WITH COPY_ONLY, CHECKSUM, COMPRESSION (ALGORITHM = ZSTD, LEVEL = HIGH), INIT;SELECT RIGHT(m.physical_device_name, 14) AS file_name, b.compression_algorithm,
CAST(b.backup_size / 1048576 AS decimal(8,1)) AS backup_mb,
CAST(b.compressed_backup_size / 1048576 AS decimal(8,1)) AS on_disk_mb,
CAST(b.backup_size / b.compressed_backup_size AS decimal(4,2)) AS ratio
FROM msdb.dbo.backupset AS b
JOIN msdb.dbo.backupmediafamily AS m ON m.media_set_id = b.media_set_id
WHERE b.database_name = 'sqls_20' AND m.physical_device_name LIKE '%sqls_20_c_.bak'
ORDER BY m.physical_device_name;file_name compression_algorithm backup_mb on_disk_mb ratio
-------------- --------------------- --------- ---------- -----
sqls_20_c0.bak NULL 407.1 407.1 1.00
sqls_20_c1.bak MS_XPRESS 407.1 111.9 3.64
sqls_20_c2.bak ZSTD 407.1 123.6 3.29
sqls_20_c3.bak ZSTD 407.1 92.1 4.42Algoritma belirtmeyen COMPRESSION eski MS_XPRESS'i kullanıyor. ZSTD'nin varsayılan seviyesi (LOW) bu veride MS_XPRESS'ten biraz büyük dosya üretti, HIGH seviyesi ise dosyayı 407 MB'tan 92 MB'a indirdi. Oran veriye bağlıdır. Microsoft'a göre ZSTD daha hızlı ve daha iyi sıkıştırmak için geldi; HIGH seviyesinin CPU maliyetini kendi sunucunda, gece penceresinde ölç.
RESTORE VERIFYONLY backup dosyasının eksiksiz ve okunabilir olduğunu kontrol eder. Bir kopyanın ortasına sekiz byte yazıp ikisini doğruladım:
RESTORE VERIFYONLY FROM DISK = '/var/opt/mssql/backup/sqls_20_c1.bak' WITH CHECKSUM;
RESTORE VERIFYONLY FROM DISK = '/var/opt/mssql/backup/sqls_20_damaged.bak' WITH CHECKSUM;The backup set on file 1 is valid.
Msg 3203, Level 16, State 1, Server sql1, Line 2
Read on "/var/opt/mssql/backup/sqls_20_damaged.bak" failed: 13(The data is invalid.)
Msg 3013, Level 16, State 1, Server sql1, Line 2
VERIFY DATABASE is terminating abnormally.Faydalı bir kontrol, ama sınırı var: VERIFYONLY veritabanını açmaz, içindeki verinin mantıksal olarak sağlam olduğunu söylemez. Bir backup'ın gerçekten işe yaradığını göstermenin tek yolu onu restore edip üzerinde DBCC CHECKDB çalıştırmaktır (21. yazı).
Point-in-Time Restore: Yanlışlıkla Silinen Siparişleri Geri Getirmek
Bookstore'un takvimi şöyle: haftada bir full, her gece differential, beş dakikada bir log backup. Laboratuvarda saatler yerine saniyelerle aynı sırayı kurdum. Full ve differential arasında, differential ile ilk log backup arasında ve sonrasında birer yeni sipariş geliyor. Son iyi anı not ediyoruz:
BACKUP DATABASE sqls_20 TO DISK = '/var/opt/mssql/backup/sqls_20_full.bak' WITH CHECKSUM, COMPRESSION, INIT;
WAITFOR DELAY '00:00:03';
INSERT dbo.Orders (OrderNumber, CustomerId, Status, CreatedAt, TotalAmount, Currency)
VALUES ('BK-01000001', 42, 'Pending', SYSUTCDATETIME(), 0, 'TRY');
BACKUP DATABASE sqls_20 TO DISK = '/var/opt/mssql/backup/sqls_20_diff.bak' WITH DIFFERENTIAL, CHECKSUM, COMPRESSION, INIT;
WAITFOR DELAY '00:00:03';
INSERT dbo.Orders (OrderNumber, CustomerId, Status, CreatedAt, TotalAmount, Currency)
VALUES ('BK-01000002', 43, 'Pending', SYSUTCDATETIME(), 0, 'TRY');
BACKUP LOG sqls_20 TO DISK = '/var/opt/mssql/backup/sqls_20_log_1.trn' WITH CHECKSUM, COMPRESSION, INIT;
WAITFOR DELAY '00:00:03';
INSERT dbo.Orders (OrderNumber, CustomerId, Status, CreatedAt, TotalAmount, Currency)
VALUES ('BK-01000003', 44, 'Pending', SYSUTCDATETIME(), 0, 'TRY');
WAITFOR DELAY '00:00:02';
SELECT SYSUTCDATETIME() AS last_good_moment;
WAITFOR DELAY '00:00:02';last_good_moment
---------------------------
2026-11-14 23:46:47.9355486Sonra ödemesi tamamlanmamış eski siparişleri temizlemesi gereken bir script çalışıyor. Tarih koşulu ters yazılmış: eskileri değil, 20 Ekim'den sonraki bütün bekleyen siparişleri siliyor. Ekipte riskli toplu değişiklikleri isimli ve işaretli (marked) bir transaction içinde çalıştırma kuralı var; bunun neden işe yaradığını birazdan göreceğiz:
BEGIN TRANSACTION PendingCleanup WITH MARK 'pending order cleanup';
DELETE i FROM dbo.OrderItems AS i JOIN dbo.Orders AS o ON o.OrderId = i.OrderId
WHERE o.Status = 'Pending' AND o.CreatedAt >= '2026-10-20';
DELETE p FROM dbo.Payments AS p JOIN dbo.Orders AS o ON o.OrderId = p.OrderId
WHERE o.Status = 'Pending' AND o.CreatedAt >= '2026-10-20';
DELETE FROM dbo.Orders WHERE Status = 'Pending' AND CreatedAt >= '2026-10-20';
SELECT @@ROWCOUNT AS deleted_orders, SYSUTCDATETIME() AS deleted_at;
COMMIT;deleted_orders deleted_at
-------------- ---------------------------
618 2026-11-14 23:46:50.2721927615 eski sipariş ve az önce gelen üç yeni sipariş gitti. Hatayı fark edene kadar bir sipariş daha geliyor (BK-01000004) ve beş dakikalık log backup çalışıyor:
WAITFOR DELAY '00:00:03';
INSERT dbo.Orders (OrderNumber, CustomerId, Status, CreatedAt, TotalAmount, Currency)
VALUES ('BK-01000004', 45, 'Pending', SYSUTCDATETIME(), 0, 'TRY');
BACKUP LOG sqls_20 TO DISK = '/var/opt/mssql/backup/sqls_20_log_2.trn' WITH CHECKSUM, COMPRESSION, INIT;Üretim veritabanının üzerine restore etmek BK-01000004'ü ve DELETE'ten sonraki her şeyi kaybettirir. Doğru yol, zinciri yan bir veritabanına DELETE'ten hemen önceki ana kadar açıp yalnız silinen satırları geri taşımaktır.
İlk deneme sık yapılan hatayla:
RESTORE DATABASE sqls_20_pitr FROM DISK = '/var/opt/mssql/backup/sqls_20_full.bak'
WITH MOVE 'sqls_20' TO '/var/opt/mssql/data/sqls_20_pitr.mdf',
MOVE 'sqls_20_log' TO '/var/opt/mssql/data/sqls_20_pitr_log.ldf';
RESTORE DATABASE sqls_20_pitr FROM DISK = '/var/opt/mssql/backup/sqls_20_diff.bak' WITH NORECOVERY;Msg 3117, Level 16, State 1, Server sql1, Line 4
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Server sql1, Line 4
RESTORE DATABASE is terminating abnormally.RESTORE'un varsayılanı RECOVERY'dir: full backup restore edildi, açık transaction'lar geri alındı ve veritabanı kullanıma açıldı. Recovery'den sonra zincire başka backup eklenemez; baştan başlamak gerekir. Zincirin her halkası NORECOVERY ile restore edilir, yalnız sonuncusu RECOVERY ile. İkinci deneme bir halkayı atlıyor:
RESTORE DATABASE sqls_20_pitr FROM DISK = '/var/opt/mssql/backup/sqls_20_full.bak'
WITH MOVE 'sqls_20' TO '/var/opt/mssql/data/sqls_20_pitr.mdf',
MOVE 'sqls_20_log' TO '/var/opt/mssql/data/sqls_20_pitr_log.ldf',
NORECOVERY, REPLACE;
RESTORE DATABASE sqls_20_pitr FROM DISK = '/var/opt/mssql/backup/sqls_20_diff.bak' WITH NORECOVERY;
RESTORE LOG sqls_20_pitr FROM DISK = '/var/opt/mssql/backup/sqls_20_log_2.trn' WITH NORECOVERY;Msg 4305, Level 16, State 1, Server sql1, Line 6
The log in this backup set begins at LSN 74000001714400001, which is too recent to apply to the database. An earlier log backup that includes LSN 74000001712800001 can be restored.
Msg 3013, Level 16, State 1, Server sql1, Line 6
RESTORE LOG is terminating abnormally.Hata mesajı eksik halkayı LSN'iyle söylüyor: differential'dan sonra log_1 gelmeli. Bu sefer veritabanı NORECOVERY'de beklediği için baştan başlamaya gerek yok, eksik log'u ekleyip son log'da STOPAT ile duruyoruz:
RESTORE LOG sqls_20_pitr FROM DISK = '/var/opt/mssql/backup/sqls_20_log_1.trn' WITH NORECOVERY;
RESTORE LOG sqls_20_pitr FROM DISK = '/var/opt/mssql/backup/sqls_20_log_2.trn'
WITH STOPAT = '2026-11-14T23:46:47.93', RECOVERY;Processed 0 pages for database 'sqls_20_pitr', file 'sqls_20' on file 1.
Processed 86 pages for database 'sqls_20_pitr', file 'sqls_20_log' on file 1.
RESTORE LOG successfully processed 86 pages in 0.004 seconds (167.968 MB/sec).
Processed 0 pages for database 'sqls_20_pitr', file 'sqls_20' on file 1.
Processed 145 pages for database 'sqls_20_pitr', file 'sqls_20_log' on file 1.
RESTORE LOG successfully processed 145 pages in 0.005 seconds (226.562 MB/sec).SELECT 'production' AS db, COUNT(*) AS orders_since_1020, MAX(OrderNumber) AS last_order
FROM sqls_20.dbo.Orders WHERE CreatedAt >= '2026-10-20'
UNION ALL
SELECT 'restored copy', COUNT(*), MAX(OrderNumber)
FROM sqls_20_pitr.dbo.Orders WHERE CreatedAt >= '2026-10-20';db orders_since_1020 last_order
------------- ----------------- -----------
production 26 BK-01000004
restored copy 643 BK-01000003Yan kopyada 643 sipariş var ve son sipariş BK-01000003; üretimde 26 sipariş kalmış ama BK-01000004 orada. STOPAT'ın zamanı sunucu saatine göre yorumlanır; container UTC (Coordinated Universal Time; saat dilimsiz evrensel saat) ile çalıştığı için örnekte de UTC kullandım. Şimdi yalnız eksik satırları, aynı kimlik değerleriyle geri taşıyoruz:
SET NOCOUNT ON;
SET XACT_ABORT ON;
BEGIN TRANSACTION;
SET IDENTITY_INSERT dbo.Orders ON;
INSERT dbo.Orders (OrderId, OrderNumber, CustomerId, Status, CreatedAt, TotalAmount, Currency)
SELECT r.OrderId, r.OrderNumber, r.CustomerId, r.Status, r.CreatedAt, r.TotalAmount, r.Currency
FROM sqls_20_pitr.dbo.Orders AS r
WHERE r.CreatedAt >= '2026-10-20'
AND NOT EXISTS (SELECT 1 FROM dbo.Orders AS o WHERE o.OrderId = r.OrderId);
PRINT CONCAT('orders: ', @@ROWCOUNT);
SET IDENTITY_INSERT dbo.Orders OFF;
SET IDENTITY_INSERT dbo.OrderItems ON;
INSERT dbo.OrderItems (OrderItemId, OrderId, BookId, Quantity, UnitPrice)
SELECT r.OrderItemId, r.OrderId, r.BookId, r.Quantity, r.UnitPrice
FROM sqls_20_pitr.dbo.OrderItems AS r
WHERE r.OrderId IN (SELECT OrderId FROM sqls_20_pitr.dbo.Orders WHERE CreatedAt >= '2026-10-20')
AND NOT EXISTS (SELECT 1 FROM dbo.OrderItems AS i WHERE i.OrderItemId = r.OrderItemId);
PRINT CONCAT('order items: ', @@ROWCOUNT);
SET IDENTITY_INSERT dbo.OrderItems OFF;
SET IDENTITY_INSERT dbo.Payments ON;
INSERT dbo.Payments (PaymentId, OrderId, Amount, Currency, Method, Status, CreatedAt)
SELECT r.PaymentId, r.OrderId, r.Amount, r.Currency, r.Method, r.Status, r.CreatedAt
FROM sqls_20_pitr.dbo.Payments AS r
WHERE r.OrderId IN (SELECT OrderId FROM sqls_20_pitr.dbo.Orders WHERE CreatedAt >= '2026-10-20')
AND NOT EXISTS (SELECT 1 FROM dbo.Payments AS p WHERE p.PaymentId = r.PaymentId);
PRINT CONCAT('payments: ', @@ROWCOUNT);
SET IDENTITY_INSERT dbo.Payments OFF;
COMMIT;orders: 618
order items: 1587
payments: 615Karşılaştırma sorgusunu tekrar çalıştırınca üretimde 644 sipariş görünüyor: geri gelen 618 ve sonradan gelen BK-01000004. Geri taşıma tek bir transaction'da ve parent tablodan child'a doğru yapıldı; foreign key'ler sırayı zaten zorluyor.
Saati tam bilmiyorsan iki seçenek var. Birincisi log'u STANDBY ile, yani her adımdan sonra salt okunur açarak, farklı STOPAT değerleriyle adım adım ilerlemek. İkincisi yukarıdaki işaretli transaction: WITH MARK ile açılan transaction'ın adı msdb.dbo.logmarkhistory'ye yazılır ve restore saate değil o işarete göre durabilir:
RESTORE DATABASE sqls_20_mark FROM DISK = '/var/opt/mssql/backup/sqls_20_full.bak'
WITH MOVE 'sqls_20' TO '/var/opt/mssql/data/sqls_20_mark.mdf',
MOVE 'sqls_20_log' TO '/var/opt/mssql/data/sqls_20_mark_log.ldf',
NORECOVERY;
RESTORE DATABASE sqls_20_mark FROM DISK = '/var/opt/mssql/backup/sqls_20_diff.bak' WITH NORECOVERY;
RESTORE LOG sqls_20_mark FROM DISK = '/var/opt/mssql/backup/sqls_20_log_1.trn' WITH NORECOVERY;
RESTORE LOG sqls_20_mark FROM DISK = '/var/opt/mssql/backup/sqls_20_log_2.trn'
WITH STOPBEFOREMARK = 'PendingCleanup', RECOVERY;
SELECT COUNT(*) AS orders_since_1020 FROM sqls_20_mark.dbo.Orders WHERE CreatedAt >= '2026-10-20';
SELECT mark_name, description, mark_time FROM msdb.dbo.logmarkhistory WHERE database_name = 'sqls_20';orders_since_1020
-----------------
643
mark_name description mark_time
-------------- --------------------- -----------------------
PendingCleanup pending order cleanup 2026-11-14 23:46:50.283Sonuç aynı 643 sipariş, ama bu sefer milisaniyeyi tahmin etmeye gerek kalmadı. Veritabanı tamamen kaybolduysa (disk gitti, sunucu açılmıyor) zincirin sonuna bir halka daha gerekir: son log backup'tan sonra yazılanları almak için tail-log backup (BACKUP LOG ... WITH NORECOVERY, data dosyaları hasarlıysa NO_TRUNCATE). Log diski sağlamsa RPO (kabul edilebilir en fazla veri kaybı süresi) sıfıra iner.
Log Neden Büyür ve Shrink Neden Çözüm Değil?
Açılıştaki durumu biraz daha ileri götürdüm: log dosyasına 400 MB üst sınır koyup log backup almadan güncellemeye devam ettim:
ALTER DATABASE sqls_20 MODIFY FILE (NAME = 'sqls_20_log', MAXSIZE = 400MB);
DECLARE @i int = 1, @used int, @total int;
WHILE @i <= 20
BEGIN
UPDATE dbo.Payments SET Amount = Amount + 0.01 WHERE PaymentId <= 300000;
SELECT @used = used_log_space_in_bytes / 1048576, @total = total_log_size_in_bytes / 1048576
FROM sys.dm_db_log_space_usage;
RAISERROR('round %d: log used %d MB of %d MB', 0, 1, @i, @used, @total) WITH NOWAIT;
SET @i += 1;
END;round 1: log used 42 MB of 327 MB
round 2: log used 73 MB of 327 MB
...
round 9: log used 286 MB of 391 MB
round 10: log used 317 MB of 391 MB
Msg 9002, Level 17, State 2, Server sql1, Line 5
The transaction log for database 'sqls_20' is full due to 'LOG_BACKUP' and the holdup lsn is (74:17152:2).9002, production'da sabahın üçünde gelen hatadır: veritabanı yazma kabul etmiyor. Mesaj nedeni de söylüyor: LOG_BACKUP. Gerçek hayatta üst sınır MAXSIZE değil, diskin kendisidir. İlk refleks genellikle log dosyasını küçültmektir:
SELECT log_reuse_wait_desc, log_used_pct, log_file_mb FROM dbo.LogState;
DBCC SHRINKFILE ('sqls_20_log', 64);log_reuse_wait_desc log_used_pct log_file_mb
------------------- ------------ -----------
LOG_BACKUP 91.1 399
Cannot shrink log file 2 (sqls_20_log) because the logical log file located at the end of the file is in use.Shrink, log'un kullanılmayan kısmını işletim sistemine geri verir; kullanılan kısmı silemez. Log'u boşaltan şey nedeni ortadan kaldırmaktır, burada log backup:
BACKUP LOG sqls_20 TO DISK = '/var/opt/mssql/backup/sqls_20_log_3.trn' WITH CHECKSUM, COMPRESSION, INIT;
SELECT log_reuse_wait_desc, log_used_pct, log_file_mb FROM dbo.LogState;log_reuse_wait_desc log_used_pct log_file_mb
------------------- ------------ -----------
NOTHING .3 399Dosya 399 MB'ta kaldı ve öyle kalmalı. Log bir kez o boyuta ihtiyaç duyduysa yine duyacak; her gece shrink edip ertesi gün yeniden büyütmek, her büyümede yeni VLF'ler ve büyüme anında beklemeler demektir (log dosyası instant file initialization'dan yararlanamaz, büyüyen kısım sıfırlanarak yazılır; SQL Server 2022'den beri 64 MB'a kadar olan büyümeler bunun istisnası). Shrink'i yalnız bir kerelik bir olaydan sonra (unutulmuş bir açık transaction, bir defalık büyük bir yükleme) kullan, sonra log'a gerçekçi bir başlangıç boyutu ve MB cinsinden sabit bir büyüme adımı ver. SIMPLE'a geçip geri dönmek de bir "çözüm" olarak önerilir; log zincirini kırar ve bir sonraki full backup'a kadar point-in-time restore imkânını yok eder.
Backup Nereye ve Ne Sıklıkla?
Backup'ın veritabanıyla aynı diskte durması, o disk gittiğinde ikisinin birlikte gitmesi demektir. Container'da bu daha da belirgin: volume bağlanmamış bir container'da /var/opt/mssql/backup container'ın kendi katmanındadır ve docker rm ile kaybolur. Okurun Windows makinesinde de aynı komut çalışır:
docker volume create bookstore-sql-data
docker run -d --name sql1 -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=$env:MSSQL_SA_PASSWORD" -e "MSSQL_PID=Developer" -p 1433:1433 -v bookstore-sql-data:/var/opt/mssql mcr.microsoft.com/mssql/server:2025-latest
docker exec sql1 mkdir -p /var/opt/mssql/backup
docker cp sql1:/var/opt/mssql/backup/sqls_20_full.bak D:\backup\Backup klasörünü container'ın içinde mssql kullanıcısı oluşturuyor; Docker'ın kendi açtığı bir mount noktası root'a ait olur ve SQL Server oraya yazamaz. Volume yalnız container'dan bağımsızlık sağlar, aynı makinenin diskindedir; docker cp ilk adımdır, asıl kopya başka bir yerde olmalı. SQL Server 2022'den beri BACKUP ... TO URL Azure Blob Storage'a ek olarak S3 (Amazon'un object storage API'si) uyumlu depolamaya (MinIO, Ceph, bulut sağlayıcılar) da yazabiliyor:
CREATE CREDENTIAL [s3://s3.example.com:9000/bookstore-backups]
WITH IDENTITY = 'S3 Access Key', SECRET = '<access-key-id>:<secret-key>';
BACKUP DATABASE BookstoreDb
TO URL = 's3://s3.example.com:9000/bookstore-backups/BookstoreDb_full.bak'
WITH CHECKSUM, COMPRESSION (ALGORITHM = ZSTD), FORMAT;Endpoint'in SQL Server'ın güvendiği bir TLS (ağ trafiğini şifreleyen protokol) sertifikasıyla HTTPS konuşması şart ve bucket'ı SQL Server oluşturmaz. Bu laboratuvarda S3 endpoint'i kurmadığım için bu komutu çalıştırmadım; söz dizimi Microsoft Learn'deki S3 belgesinden. SQL Server 2025 Azure'da immutable (silinemeyen) blob storage'a backup'ı da destekliyor; fidye yazılımının backup'ları da şifrelediği senaryoya karşı değerli bir katman. Windows'ta varsayılan backup klasörü C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Backup, Linux'ta mssql-conf'un filelocation.defaultbackupdir ayarıdır; SSMS'te (SQL Server'ın masaüstü yönetim aracı) aynı işler veritabanına sağ tıklayıp Tasks → Back Up ve Tasks → Restore → Database menülerinden yapılır.
Takvim, RPO ve RTO'dan (geri dönüş için kabul edilebilir en uzun süre) çıkar. Bu kavramları ve Bookstore'un hedeflerini Veritabanı Performansı ve Scaling serisinin 5. yazısı "High Availability ve Failover" belirledi: felaket senaryosunda RPO beş dakika, RTO bir saat. Beş dakikalık RPO, beş dakikada bir log backup demek; ama yalnız backup'ın kopyası da beş dakika içinde başka bir yere ulaşıyorsa. RTO ise restore süresidir: full'ün boyutu, tek bir differential ve o günün log backup'larının sayısı. Gece differential'ı bu yüzden var; yoksa bir hafta önceki full'ün üzerine 2.000 log backup uygulamak gerekir. Aynı serinin 10. yazısındaki restore tatbikatının söylediği gibi, bir backup'ı test etmenin tek yolu restore etmektir: her çeyrek, bu yazıdaki zinciri boş bir sunucuda baştan açıp süresini ölçmek ve DBCC CHECKDB çalıştırmak. Azure SQL Database'de bu işlerin çoğunu platform yapar; otomatik backup ve point-in-time restore farkları Cloud Temelleri serisinin "Managed Database'ler" yazısında.
SQL Server 2019/2022'de farkı: recovery model'lar, zincir mantığı, COPY_ONLY, STOPAT ve STOPBEFOREMARK üç sürümde aynı. S3 uyumlu storage'a backup ve COMPRESSION (ALGORITHM = ...) söz dizimi 2022'de geldi (2022'de seçenekler MS_XPRESS ve Intel QAT (QuickAssist Technology; sıkıştırmayı donanıma yaptıran Intel hızlandırıcısı) ile çalışan QAT_DEFLATE). ZSTD, immutable blob storage ve availability group secondary'lerinde copy-only olmayan full ve differential backup 2025'te geldi. 2019'da yalnız COMPRESSION vardır ve backup to URL yalnız Azure Blob'a yazar.
Güvenilmeyen Bir .bak Dosyasını Restore Etmek
Bir tedarikçi ya da müşteri "sorunu bizim veritabanımızda görün" diye bir .bak dosyası gönderebilir. Restore'u çoğu zaman sysadmin yetkili biri yapar ve dosyadaki bütün kod sunucuya gelir. Microsoft Learn de bilinmeyen kaynaktan gelen veritabanlarının istenmeyen T-SQL çalıştırabilecek kod içerebileceği konusunda uyarır. SQL Server'ın bir koruması var: TRUSTWORTHY (veritabanındaki kodun sunucu seviyesinde yetki kullanabilmesini sağlayan ayar) restore sırasında kapanır. Bunu denedim:
SET NOCOUNT ON;
ALTER DATABASE sqls_20 SET TRUSTWORTHY ON;
BACKUP DATABASE sqls_20 TO DISK = '/var/opt/mssql/backup/sqls_20_vendor.bak' WITH COPY_ONLY, CHECKSUM, COMPRESSION, INIT;
ALTER DATABASE sqls_20 SET TRUSTWORTHY OFF;
RESTORE DATABASE sqls_20_vendor FROM DISK = '/var/opt/mssql/backup/sqls_20_vendor.bak'
WITH MOVE 'sqls_20' TO '/var/opt/mssql/data/sqls_20_vendor.mdf',
MOVE 'sqls_20_log' TO '/var/opt/mssql/data/sqls_20_vendor_log.ldf',
RESTRICTED_USER;
SELECT name, is_trustworthy_on, user_access_desc, SUSER_SNAME(owner_sid) AS owner
FROM sys.databases WHERE name = 'sqls_20_vendor';name is_trustworthy_on user_access_desc owner
-------------- ----------------- ---------------- -----
sqls_20_vendor 0 RESTRICTED_USER saKaynakta açık olan TRUSTWORTHY restore'da kapandı. Ama bu tek başına yetmez: veritabanının sahibi restore eden login oldu, ve sen veritabanının içindeki bir procedure'ü sysadmin olarak çalıştırırsan o kod senin yetkinle çalışır. Güvenilmeyen dosyayı önce RESTORE HEADERONLY ve FILELISTONLY ile incele, üretimden ayrı bir sunucuda RESTRICTED_USER ile aç, sahibini yetkisiz bir login'e çevir (17. yazı) ve içindeki kodu çalıştırmadan önce oku.
Üretimde Dikkat
Backup job'larının başarılı bitmesini değil, backup'ların varlığını izle. msdb.dbo.backupset'te her veritabanının son full, differential ve log backup zamanına bakan ve eşik aşılınca alarm veren bir sorgu, sessizce duran bir job'ı günler değil dakikalar içinde yakalar. Job'ları 22. yazıda kuracağız.
log_reuse_wait_desc ve log kullanım yüzdesini izle. 9002 hatasından önce log saatlerce büyür; LOG_BACKUP görüyorsan log backup job'ı çalışmıyor, ACTIVE_TRANSACTION görüyorsan açık bir transaction var.
Backup dosyaları da hassas veridir. Diskte şifreli değillerse onları okuyan veritabanının tamamını okur; WITH ENCRYPTION ya da storage tarafında şifreleme kullan ve sertifikanın kendisini ayrıca yedekle, yoksa restore edemezsin.
Sık Düşülen Tuzaklar
FULL recovery'de log backup almamak. Açılıştaki tablo bunu gösteriyor: full backup log'u boşaltmaz, log diski doldurur ve RPO pratikte son full backup kadar, yani 24 saat olur.
Backup'ı veritabanıyla aynı diske ya da aynı container'ın katmanına koymak. Disk ya da container giderse backup da gider.
Planlı takvimin dışında COPY_ONLY'siz full backup almak. Sonraki differential'lar senin aldığın ve belki sildiğin dosyaya bağlanır; Msg 3136 restore sırasında, yani en kötü anda ortaya çıkar.
Üretim veritabanının üzerine point-in-time restore yapmak. DELETE'ten sonra gelen siparişleri de kaybedersin; yan veritabanına restore edip satırları geri taşı.
Log büyüyünce her gece shrink etmek ya da SIMPLE'a geçip dönmek. Nedeni çözmez, VLF sayısını artırır, log zincirini kırar.
Küçük Bir Deneme
★☆☆ Kendi veritabanlarının recovery model'ını ve log_reuse_wait_desc'ini listele. FULL'de olup son 24 saatte log backup'ı olmayan var mı? msdb.dbo.backupset ile kontrol et.
★★☆ Point-in-time senaryosunu STOPAT yerine STANDBY ile tekrarla: son log'u önce DELETE'ten bir saniye sonrasına, sonra bir saniye öncesine kadar uygula ve her adımda yan veritabanında sipariş sayısına bak.
★★★ Tail-log senaryosunu kur: full ve bir log backup al, birkaç sipariş ekle, sonra BACKUP LOG ... WITH NORECOVERY ile tail-log backup alıp veritabanını yeniden restore et. Son eklenen siparişler geri geldi mi? Data dosyası hasarlı olsaydı hangi seçenek gerekirdi?
Backup'ın bir veritabanını korumanın yalnız bir parçası olduğunu gördük; restore ettiğimiz şeyin içinin sağlam olduğunu da bilmek gerekiyor. Bir sonraki yazıda index bakımını, istatistik güncellemeyi, DBCC CHECKDB'yi ve bu işleri bir arada yöneten Ola Hallengren'in Maintenance Solution'ını ölçerek kuracağız.