Bu makalede mssql server’da backup komutlarını görmüş olacağız. Hangi komutun ne işe yaracağını ve ne olduğunu komutlarla açıklamaya çalışacağım. Genelde backup işlemlerinde kullanılan job’ların bu job’lar olduğunu belirtmek isterim. Şimdi ilk komutumuzla başlayalım.
Aşağıdaki komut https://ola.hallengren.com sayfasından alınmış olup daha detaylı işlemler için sayfayı ziyaret edebilirsiniz. Bu kod bloklarının çalışabilmesi için ilgili sayfadan [dbo].[DatabaseBackup] stored procedure’ün indirilip backup alacağımız instance altında çalıştırılması gerekmektedir.
Yukarıdaki belirtilen link’e girildikten sonra Downloads sekmesinde DatabaseBackup.sql procedure scriptinin indirilmesi gerekmektedir. İndirdikten sonra ilgili sunucumuza taşıyıp çalıştırılması gerekmektedir.
Master veritabanımızın altında stored procedure oluşmuş oldu. Artık ola.hallengren sayfasındaki kod bloklarını çalıştırarak backup işlemi yapabiliriz.
Aşağıdaki paylaşılan komutlar SSMS arayüzünde new query denilerek veya job oluşturularak çalıştırılabilir. New query ile manuel bir şekilde çalıştırılması sağlanırken job ile periyodik olarak çalıştırılması sağlanır. Aşağıdaki backup komutunu çalıştırdığımızda farklı stored procedure’leride yüklememiz gerektiğini söylüyor. Bunun için ola.hallengren sayfasından tüm procedure’leri kaplayacak bir şekilde MaintenanceSolution.sql scriptini indirerek hata mesajlarının önüne geçmiş oluyoruz.
Yukarıdaki resimde dikkat edersek MaintenanceSolution.sql içinde DatabaseBackup stored procedure’ün olduğunu görmüş oluyoruz. Bu strored procedure’leri çalıştırdıktan sql agent altında bunlarla ilgili joblar oluşmaktadır.
EXECUTE [dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES',
--@AvailabilityGroups='AG_NAME' Sadece ag altındaki bulunan backuplar alınır.
@Directory = 'C:\BACKUP\',
@BackupType = 'FULL',
@Compress = 'Y',
@Verify = 'Y',
@CleanupTime = 240,
@CheckSum = 'Y',
@BufferCount = 50,
@BlockSize= 4096,
@MaxTransferSize = 4194304,
@NumberOfFiles = 8,
@LogToTable = 'Y',
@Execute='N'Bu Komut’ların ne iş yaptığını açıklayalım. DatabaseBackup adlı bir prosedürü çalıştırıyor ve belirli parametrelerle bir yedekleme işlemi gerçekleştiriyor. Aşağıda bu komutun bileşenlerinin açıklaması yer alıyor:
@Databases = ‘USER_DATABASES’: Bu parametre, tüm kullanıcı veritabanlarını yedeklemeyi belirtir.
Not: ‘USER_DATABASES-ABCD’ bu ifade ABCD veritabanının backup’a dahil olmayacağını göstermektedir. Ya da USER_DATABASES yazmayıp herhangi bir database’in tek adını yazabiliriz.
@Directory = ‘C:\BACKUP\’: Yedek dosyalarının kaydedileceği Path burası belirlenmiş.
@BackupType = ‘FULL’: Bu parametre tam yedekleme (Full Backup) işlemi yapılacağını gösterir.
@Compress = ‘Y’: Yedek dosyası sıkıştırılacak anlamına gelir (Y = Yes).
@Verify = ‘Y’: Yedekleme işlemi tamamlandıktan sonra, yedeğin doğrulanacağını belirtir.
@CleanupTime = 240: 240 saat (10 gün) sonra eski yedeklerin silinmesini sağlar.
@CheckSum = ‘Y’: Yedekleme sırasında verilerin bütünlüğünü doğrulamak için CheckSum kullanılır.
@BufferCount = 50: Yedekleme işlemi için kullanılacak buffer sayısını belirler. Yedekleme işlemi sırasında sql server veritabanı dosyasındaki verileri okur. Bellekte tamponlara yani buffer’a yerleştirir. Daha sonra bu tamponları hedef dosyaya yazmaktadır. Kısaca yedekleme için ayırdığı ara bellek. Sistemde boş ram varsa bu değer çıkarılabilir.
@MaxTransferSize = 4194304: Veri transfer boyutunu ayarlar (4 MB = 4194304 byte). Tek bir buffer’ın ne kadar veri bulundurabileceğini göstermektedir.
Not: Toplam bellek kullanımı:@buffercount*@maxtransfersize
@BlockSize= 4096: sql server buffer’daki verileri bloklara bölerek diske yazar. Burada karşımıza diskimizin formatlanma biçimi karşımıza çıkmaktadır. Disklerimiz 64 kb şeklinde formatlandığı için buda backup işleminin fazladan i/o çağrısı yapmasına sebebiyet verecektir. Normalde 64 kb veri taşıyabilecekken 16 defa veri taşıma işlemi yapmaktadır. Diskin daha fazla çalışması yedeklenmeninde yavaşlaması olarak görülmektedir.
@NumberOfFiles = 8: Yedekleme için 8 ayrı dosya kullanılacağını belirtir. Bu komutu belirtmesek tek dosya üzerine backup’ı almaktadır.
@LogToTable = ‘Y’: Yedekleme işlemi sırasında oluşan loglar tabloya kaydedilecek. Bu script, SQL Server’da düzenli ve optimize bir yedekleme prosedürü oluşturmak için kullanılan parametrelerle tasarlanmış bir çözüm olarak görünmektedir.
@Execute=’N’: Test işlemleri için bu ifade rahatlıkla kullanılabilir.
@AvailabilityGroups=’AG_NAME’: Sadece belirli olan bir ag yapısının backup alınması isteniyorsa ilgili ag belirtilebilir.
Bu işlemi job aracılığıyla yapsaydık ekran görüntümüz aşağıdaki gibi olacaktı. Steps kısmından adım tanımlanır.
Not: Tüm veritabanları sıra sıra backup işlemine girmektedir.
Aşağıdaki komut tüm veritabanlarının diff backup’ını almaktadır.
EXECUTE [dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES',
@Directory = 'C:\BACKUP\',
@BackupType = 'DIFF',
@Compress = 'Y',
@Verify = 'Y',
@CleanupTime = 240,
@CheckSum = 'Y',
@BufferCount = 50,
@MaxTransferSize = 4194304,
@NumberOfFiles = 8,
@LogToTable = 'Y'
Not: Sadece 2 veritabanının backup’ını almak istersek @Databases = ‘DB1,DB2’ şeklinde yapmamız gerekmektedir.
Not: @Databases kısmında sadece belirli bir ifade içeren veritabanlarının backuplarını almak istersek tırnak içerisinde ilgili ifadesinin % ifadesiyle belirtilmesi gerekmektedir. Tüm veritabanlarını yazma sorunundan kurtulmuş oluruz. @Databases = ‘Tfs_%’ bu komut tfs ile başlayan tüm veritabanlarının backupını almaktadır.
Bu işlemi job aracılığıyla yapsaydık ekran görüntümüz aşağıdaki gibi olacaktı. Steps kısmından adım tanımlanır.
Aşağıdaki komut tüm veritabanlarının log backup’ını almaktadır.
EXECUTE [dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES',
@Directory = 'C:\BACKUP\',
@BackupType = 'LOG',
@Verify = 'N',
@CleanupTime = 240,
@CheckSum = 'N',
@LogToTable = 'Y'
Bu işlemi job aracılığıyla yapsaydık ekran görüntümüz aşağıdaki gibi olacaktı. Step kısmından adım tanımlanır.
System databaselerin backuplarını almak istersek aşağıdaki kod kullanılmaktadır. Sadece User Database yazan yere SYSTEM_DATABASES yazmamız gerekmektedir. @BackupType kısmında hangi backup türünü almak istiyorsak onu yazmamız gerekiyor. Ben aşağıdaki kod bloğunda FULL backup alacağım için full yazdım. Sizde başka backup türlerini almak istiyorsanız diff veya log yazabilirsiniz.
EXECUTE [dbo].[DatabaseBackup]
@Databases = 'SYSTEM_DATABASES',
@Directory = 'C:\BACKUP_System\',
@BackupType = 'FULL',
@Verify = 'N',
@CleanupTime = 240,
@CheckSum = 'N',
@LogToTable = 'Y'
Bu işlemi bir job aracılığıyla sürekli yapmak istersek job’ın step bölümünde yapabiliriz.
İlgili job’ın Schedule kısmında ise periyodik olarak çalışacağı zaman dilimini yapabiliriz. Sql Agent makalelerinde detaylı bir şekilde hangi bölümün ne işe yaradığı açıklanacaktır.
Yukarıdaki komutlarda FULL,DIFF ve LOG seviyesinde veritabanlarının backuplarını alabiliriz.
2. Backup alma komutları
Aşağıdaki kod bloğu, SQL Server’da bir veritabanı yedeği almak için kullanılan bir SQLCMD komutudur.
Aşağıdaki komut sayesinde sqlcmd aracılığıyla veritabanlarının backuplarını daha hızlı bir şekilde alabiliriz. Ola.hallengren komutlarından farkı sadece komut satırından çalıştırılmasına olanak sağlayan bazı ifadelerin olmasıdır.
Aşağıdaki komutlar bir job aracılığıyla çalışmaktadır. Job çalıştırılacağı zaman sanki komut satırında yazdığımız gibi yan yana olması gerekmektedir. Yoksa aşağıdaki gibi hata mesajıyla karşılaşırız. Job’ı çalıştıracak loginin windows bazında herhangi bir yetkiye gerek yoktur.
Executed as user: JND\user00. Sqlcmd: ‘-Q’: Missing argument. Enter ‘-?’ for help. Process Exit Code 1. The step failed.
sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES’, @Directory = N'C:\BACKUP',@BackupType = 'FULL', @CleanupTime = 144, @CleanupMode = 'AFTER_BACKUP', @Verify = 'Y', @CheckSum = 'Y', @LogToTable = 'Y', @Compress= 'Y', @BlockSize= 4096, @NumberOfFiles = 8,
@BufferCount=50, @MaxTransferSize = 4194304" -bNot: Sadece çalıştırıldığı ortamda primary olan availability groupları ve local’de bulunan veritabanlarının yedeğini almaktadır.
Not: job oluşturulurken Type kısmında Operating system(CmdExec) kısmının seçilmesi gerekmektedir.
Yukarıdaki komut’u job aracılığıyla çalıştırdığınızda job’ın çalışması başarısız olursa aşağıdaki makale size yardımcı olabilir.
The job failed. Unable to determine if the owner (LOGIN_NAME) of job JOB_NAME has server Access
Aşağıdaki diff ve log backup komutlarıda yukarıdaki ekran resmindeki gibi yapılmaktadır.
sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES’, @Directory = N'C:\BACKUP', @BackupType = 'DIFF', @Verify = 'Y', @CleanupTime = 168, @CleanupMode = 'BEFORE_BACKUP', @CheckSum = 'Y', @LogToTable = 'Y', @Compress= 'Y', @BlockSize= 4096, @BufferCount=50, @MaxTransferSize = 4194304" -bsqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES’, @Directory = N'C:\BACKUP', @BackupType = 'LOG', @Verify = 'Y', @CleanupTime = 168, @CleanupMode = 'AFTER_BACKUP', @CheckSum = 'Y', @LogToTable = 'Y'" -bNOT: AG altındaki veritabanları primary ise backup’a dahil olur. primary değil ise backup alınmaz.
Not: @Databases kısmına herhangi bir database veya USER_DATABASES veya SYSTEM_DATABASES yazılmaktadır. Backup alınmasını istemediğimiz bir database olursa USER_DATABASES’den sonra –(tire) işaretinin konulması gerekmektedir.
Not: bat dosyası aracılığıyla sqlcmd komutu çalıştıracağımız zaman Username password ifadelerinin girilmesi gerekmektedir. sqlcmd windows bazında çalıştırıldığı için dosya bazında yetki sorununa takılmaz. bu yüzden sqlcmd tercih edilen bir yöntemdir. Örnek olarak aşağıdaki komutu verebiliriz.
sqlcmd -S localhost -U MyUser -P MyPass -d MyDb -q "SELECT 1"Yukarıdaki komutlar sqlcmd arayüzünden de çalıştırılabilir. Bilgisayarın Run kısmına sqlcmd yazarsanız ilgili ekrana bağlanabilirsiniz.
Yukarıdaki ifadelerin ne işe yaradığını açıklayalım.
1. sqlcmd: SQL Server’a komut satırı üzerinden bağlanıp sorgular çalıştırmak için kullanılan bir araç.
2. -E: Windows kimlik doğrulaması kullanarak oturum açılacağını belirtir.
3. -S $(ESCAPE_SQUOTE(SRVR)): Bağlanılacak sunucu adını tanımlar. Muhtemelen bir betikte tanımlanan sunucu değişkeni kullanılıyor. sql server agent joblarında kullanılan bir değişkendir. Primary olup olmadığı ile bu ilgilenmez.
4. -d master: “master” veritabanını kullanarak sorgunun çalıştırılacağını belirtir.
5. –Q: SQL sorgusunu çalıştırır. Burada EXECUTE [dbo].[DatabaseBackup] ifadesi bir SQL Server saklı yordamını çalıştırıyor. Bu, bir yedekleme işlemi gerçekleştiren özel bir saklı yordama işaret eder.
Parametreler:
@Databases = ‘XXX’: Yedeklenecek veritabanının adı.
@Directory = ‘N:C\BACKUP’: Yedek dosyalarının kaydedileceği dizin.
@BackupType = ‘FULL’: Tam yedekleme türü.
@CleanupTime = 144: 144 saat (6 gün) sonra yedek dosyalarının temizlenmesi gerektiğini belirten süre.
@CleanupMode = ‘AFTER_BACKUP’: Yedekleme işlemi bittikten sonra önceki backup’ın silineceğini gösterir.
@Verify = ‘Y’: Yedekleme işleminin tamamlandıktan sonra doğrulanmasını sağlar.
@CheckSum = ‘Y’: Yedekleme sırasında denetim toplamı oluşturulur.
@LogToTable = ‘Y’: Yedekleme işlemiyle ilgili bilgilerin bir tabloya kaydedileceğini gösterir.
@Compress = ‘Y’: Yedekleme sırasında verilerin sıkıştırılacağını belirtir.
@BlockSize = 4096: Blok boyutunu 4096 byte olarak ayarlar.
@NumberOfFiles = 8: Yedekleme için 8 dosya kullanılacağını belirtir.
@BufferCount = 50: Yedekleme işlemi sırasında kullanılacak tampon sayısı.
@MaxTransferSize = 4194304: Maksimum transfer boyutu 4MB (4194304 byte) olarak ayarlanmıştır.
Sonuç olarak, bu komut bloğu ” xxxxx ” veritabanının tam yedeğini belirtilen dizine alır, bu yedeği doğrular ve çeşitli optimizasyonlarla yedeklemeyi gerçekleştirir.
Sadece Belirlediğimiz AG’nin backup’ını almak istersek aşağıdaki komut kullanılmaktadır.
EXECUTE [dbo].[DatabaseBackup]
@AvailabilityGroups= 'AG_NAME',
@Directory = N'E:\BACKUP',
@BackupType = 'FULL',
@Verify = 'Y',
@CleanupTime = 168,
@CleanupMode = 'AFTER_BACKUP',
@CheckSum = 'Y',
@Compress = 'Y',
@LogToTable = 'Y' 3. Backup alma yöntemi ise aşağıdaki gibidir. Bu yapı hangi sunucu primary ise o sunucudan backup almaktadır.
Şunu belirtmek gerekirse aşağıdaki komut replica kontrolü yaptıktan sonra primary olan sunucu üzerinde backup alma işlemi gerçekleşmektedir. Primary sunucusu üzerinde olan tüm AG yapımızın ve stand alone olan veritabanlarının hepsini almaktadır. Secondary sunucusunda ilgili komutu çalıştırdığınızda kodumuzda sadece tamamlandı diye mesaj görürüz.
Sadece belirttiğimiz bir AG altında veritabanı almak istiyorsak @AvailabilityGroups= ‘AG_NAME’ komutunun aşağıdaki kod parçasına eklenmesi gerekmektedir. İlgili @Databases = ‘USER_DATABASES’ parametresinin yerine koyulur.
Instance altında sadece 1 ag varsa ag kontrol şartı kullanılabilir. Birden fazla Ag varsa aşağıdaki kodumuz çalışmaz. Çünkü ag kontrol şartı en son ag yapımızı dönmektedir.
DECLARE @tsql nvarchar(max)=''
DECLARE @AGName nvarchar(50)
SELECT @AGName=grp.ag_name
FROM sys.dm_hadr_availability_replica_states AS a JOIN
sys.availability_replicas AS b ON b.replica_id = a.replica_id left outer join
sys.dm_hadr_name_id_map as grp on grp.ag_id = a.group_id
WHERE a.role=1
IF @AGName = 'AG_NAME'
BEGIN
EXECUTE [dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES',
@Directory = N'E:\BACKUP',
@BackupType = 'FULL',
@Verify = 'Y',
@CleanupTime = 168,
@CleanupMode = 'AFTER_BACKUP',
@CheckSum = 'Y',
@Compress = 'Y',
@LogToTable = 'Y'
ENDAşağıdaki replica komutunu çalıştırdıktan sonra sunucu üzerinde primary olan AG dönmektedir.
Aynı komudu secondary üzerinde çalıştırmak istersek herhangi bir AG yapımız dönmeyecek backup işlemimiz gerçekleşmemiş olacaktır.
Not: Herhangi bir AG altında bulunan veritabanımızın backup’ını almak istiyorsak backup alma işlemini sadece AG yapımızı belirterek backup almamız gerekmektedir. Ne kadar AG properties ekranından backup alınacak sunucuyu değiştirsek bile secondary sunucusudan backup işlemi gerçekleşmez. Secondary sunucusunda full backup sadece copy only yöntemiyle alınmaktadır. Secondary sunucusunda log backup hem copy only hemde normal bir şekilde alınmaktadır. Diff backup ise ne copy only nede normal bir diff backup şekilde alınmaz. Tabi bunlar ag altında bulunan veritabanları için geçerli bir özelliktir.
Aşağıdaki komut yukarıda belirttiğimiz gibi primary replicayı gördükten sonra çalışmaktadır. Primary replica olmadıktan sonra hata mesajı almaktadır.
DECLARE @IsPrimary int
SET @IsPrimary = 0
SELECT @IsPrimary = 1
FROM sys.availability_groups sag
INNER JOIN sys.dm_hadr_availability_group_states hags ON sag.group_id = hags.group_id
WHERE hags.primary_replica = @@SERVERNAME AND sag.name = 'AG_NAME'
IF @IsPrimary = 1
BEGIN
EXECUTE [dbo].[DatabaseBackup]
@AvailabilityGroups= 'AG_NAME',
@Directory = N'E:\BACKUP',
@BackupType = 'FULL',
@Verify = 'Y',
@CleanupTime = 168,
@CleanupMode = 'AFTER_BACKUP',
@CheckSum = 'Y',
@Compress = 'Y',
@LogToTable = 'Y'
END
ELSE
BEGIN
RAISERROR ('Database is running as secondary', 16, 1)
END
Aşağıdaki komut ile veritabanının anlık tamamlanma yüzdesini bulabiliriz.
select percent_complete,DATEADD(MILLISECOND,estimated_completion_time,getdate())
from sys.dm_exec_requests
--where percent_complete>0
where session_id=1504Bu makalede backup komutlarını ele almış olduk. Başka bir makalede görüşmek dileğiyle.
“İnsan bir sıkıntıya uğradığı zaman yanı üzerine yatarken, otururken, ayakta iken devamlı bize yalvarır durur. Sıkıntısını giderdiğimiz zaman ise, kendisine dokunan o sıkıntı sebebiyle sanki bize hiç yalvarmamış gibi eski inkâr hâline döner gider. İşte ömür ve akıl sermayelerini boşa harcayıp haddi aşanlara yaptıkları şeyler böyle süslenip püslenmektedir.” Yunus-12
