AG(Availability Group) ‘a dahil olan veritabanlarının backup’ını almak

Normalde tüm veritabanlarının backup’larını bir yazılım kullanarak ya da Maintenance Plan kullanarak alıyor olabilirsiniz. Ama bazen AG bazında backup senaryonuzdan ayrı olarak backup almaya ihtiyacınız olabilir. Tek tek AG’ye dahil olan veritabanlarını bulup tek tek backup’larını almak işimizi baya uzatacaktır. Aşağıdaki script yardımıyla Cursor kullanarak bir AG’ye dahil olan veritabanlarının backup’ı nı alabilirsiniz. AGNAME yazan yere hangi AG’ye ait veritabanlarının backup’ını alacaksanız o AG’nin ismini yazmanız gerekiyor.  SET @path = ‘C:\Backup’  ‘taki C:\Backup yazan yere de hangi path’e backup almak istiyorsanız o path’i yazmalısınız.

DECLARE @name VARCHAR(50) -- veritabanı ismi  
DECLARE @path VARCHAR(256) -- backup dosyaları için path  
DECLARE @fileName VARCHAR(256) -- backup için file ismi 
DECLARE @fileDate VARCHAR(20)  --backup dosyasına eklenecek tarih
 
SET @path = 'C:\Backup'  
 
 
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 
 
 
DECLARE db_cursor CURSOR FOR  
SELECT DISTINCT
dbcs.database_name AS name
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
   ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
   ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
   ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
   ON arstates.replica_id = dbcs.replica_id
LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs
   ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id
WHERE ISNULL(arstates.role, 3) = 1 AND ISNULL(dbcs.is_database_joined, 0) = 1 AND AG.name='AGNAME'
ORDER BY  dbcs.database_name
 
   
 
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   
 
 
WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName  WITH COMPRESSION,COPY_ONLY
 
 
       FETCH NEXT FROM db_cursor INTO @name   
END   
 
 
CLOSE db_cursor   

DEALLOCATE db_cursor

Instance üzerindeki tüm veritabanlarının backup’ını almak

Normalde backup alma işlemleri için Ola Hallengren’in script’lerini kullanmanızda fayda var. Ama bazen normal backup senaryosu dışında instance üzerindeki tüm veritabanlarının backup’ını ayrı bir yere almanız gerekebilir.

Aşağıdaki script yardımıyla Instance üzerindeki tempdb hariç tüm veritabanlarının backup’ını alabilirsiniz.

SET @path = ‘C:\Backup’  ‘taki C:\Backup yazan yere de hangi path’e backup almak istiyorsanız o path’i yazmalısınız.

DECLARE @name VARCHAR(50) -- veritabanı ismi  
DECLARE @path VARCHAR(256) -- backup dosyaları için path  
DECLARE @fileName VARCHAR(256) -- backup için file ismi 
DECLARE @fileDate VARCHAR(20)  --backup dosyasına eklenecek tarih


-- specify database backup directory
SET @path = 'C:\Backup'  


-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 


DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('tempdb')  -- exclude these databases


OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   


WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @fileName = @path + '['+@name + '_' + @fileDate + '].BAK'  
       BACKUP DATABASE @name TO DISK = @fileName WITH COPY_ONLY


       FETCH NEXT FROM db_cursor INTO @name   
END   


CLOSE db_cursor   
DEALLOCATE db_cursor

Son backup alma tarihini bulmak

Aşağıdaki script yardımıyla o instance üzerindeki her veritabanının son backup alma tarihini bulabilirsiniz. ‘L’ yazan yer Log Backup olduğunu gösteriyor. Eğer bu kısma D yazarsanız alınan en son full backup tarihini, G yazarsanız alınan en son differential backup tarihini görebilirsiniz. Eğer veritabanınız AG’ye dahilse her iki node üzerinde bu script’i çalıştırdığınızda doğru sonucu görebilirsiniz. Çünkü backup diğer node üzerinden alınmış olabilir.

SELECT   sd.name,
         MAX(bs.backup_finish_date) AS backup_finish_date
FROM     master.sys.sysdatabases sd
         LEFT OUTER JOIN msdb..backupset bs
         ON       bs.database_name = sd.name
         AND      bs.type          = 'L'
GROUP BY sd.name
ORDER BY backup_finish_date DESC

Backup Restore İşlemleri

SQL Server veritabanlarının yedeğini alma işlemine backup denir. Bu yedeği SQL Server Instance üzerine yükleme işlemine de restore denir. Backup Restore işlemini bir çok işlem için kullanabiliriz. Aşağıda en çok kullanma sebeplerimizi bulabilirsiniz.

1. Düzenli bir yedek almak için
2. Anlık bir seferlik yedek almak için
3. Bozulmuş bir veritabanını backup’ı kullanarak yeniden yüklemek için
4. Veritabanını başka bir instance’a ya da başka bir diske taşımak için

3 tip backup alabiliriz.

Full Backup: Genel anlamda backup alma ifadesinin karşılığıdır diyebiliriz. Veritabanının tamamının backup’ını alır.

Differential Backup: En son alınan Full Backup’tan sonraki fark backup’ı alır.

Log Backup: Transaction Log dosyasının backup’ını alır.

Örnek bir senaryo ile Bu backup çeşitlerini nasıl kullanabileceğimizi anlatayım.

Senaryomuzda haftada bir kere full backup alındığını, her akşam 20:00’de differential backup alındığını, 2 saatte bir kere de log backup alındığını düşünün.

Çarşamba günü öğlen 3 de bir sıkıntı yaşandığını ve veritabanının yanlışlıkla silindiğini söylediler. Sizden en son backup’a dönüş yapmanızı istiyorlar.

Dönüşümüz şu şekilde olacaktır. En son alınmış full backup’ı norecovery mode’da restore ederiz. Sonra Salı akşam 20:00’daki differential backup’ı restore ettiğimiz veritabanının üzerine yine norecovery’i mode’da restore ederiz. Sonra da Differential Backup’tan sonra alınmış tüm log backup’ları norecovery mode’da restore ettikten sonra son log backup’ı recovery mode’da restore ederek işlemi tamamlarız.

Backup ve Restore işlemlerini SSMS üzerinden de ya da script aracılığı ile yapabilirsiniz.

Full Backup için: SSMS üzerinden veritabanına sağ tıklayıp Task->Backup diyerek Destination kısmında Sağ tarafta Add yazan yere tıklıyoruz. Ve aşağıdaki gibi backup’a .BAK uzantılı bir isim veriyoruz ve ok’e tıklıyoruz.

Daha sonra aşağıdaki gibi Media Options’dan Verify backup when finished’ı tikliyoruz. Bu işlem backup bittikten sonra backup’ın doğru alındığı Restore HeaderOnly ifadesi ile teyit edecektir. Korkmayın teyit ederken restore işleminin kendisini yapmıyor. Fakat backup bittikten sonra yaklaşık restore süresi kadar işleminizi uzatacaktır.

Sp_configure üzerinden backup compression default ayarını yapmadıysanız aşağıdaki gibi Backup Options üzerinden Set backup compression’ı Compress Backup seçmenizde fayda var. Bu şekilde alacağınız backup boyutunu küçültebilirsiniz.

Sp configure ile ilgili detayları “sp_configure(SQL Server’da Server Seviyesinde Konfigurasyonlar)” isimli makalemde bulabilirsiniz.

Bütün bu işlemleri yaptıktan sonra ok’e tıklarsanız backup’ı alacaktır. Ya da yukarıdaki resimde sol üstte gördüğünüz Script’e tıklarsanız size yapacağı işlemlerin script’ini verecektir. Ben bütün işlemleri script’ini alarak ve daha sonra bu script’i çalıştırarak gerçekleştiriyorum size de aynısını tavsiye ederim.

Yaptığımız işlemlerin script’i de aşağıdaki gibi.

BACKUP DATABASE [testDB] TO  DISK = N'C:\Backup\testDB.BAK' WITH NOFORMAT, NOINIT,  NAME = N'testDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'testDB' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'testDB' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''testDB'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM  DISK = N'C:\Backup\testDB.BAK' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
GO

Differential ve Log Backup için:  Birkaç farkla aynı işlemleri gerçekleştiriyoruz. Task->Backup dedikten sonra Backup Type kısmından Log Backup için Transaction Log’u, Differential Backup için Differential’ı seçiyoruz.

Birde backup ismi verirken Log backup alacaksak uzantısını .trn olarak yazıyoruz.

Restore: SSMS üzerinde Databases’a sağ tıklayıp restore database diyoruz ve açılan sekmede source kısmında device’ı seçerek sağ tarafran add diyoruz. Hangi backup’ı yükleyeceksek o backup dosyasını bulup ok diyoruz ve karşımıza aşağıdaki gibi bir ekran çıkıyor.

Database’i seçersek msdb üzerindeki alınmış backup tarihçesinden istediğimiz backup’ı seçerek restore işlemini gerçekleştirebiliriz. Fakat backup belirtilen path üzerinde hala bulunmuyorsa restore işlemi fail edecektir.

Mesela aynı instance üzerine farklı bir isimle kaydedelim. Yeni ismi prodDB olsun. Yukarıdaki ekranda Destiantion daki Database kısmına prodDB yazıyoruz ve Files sekmesine geçiyoruz. Files kısmında Restore As’den oluşacak file’ların pathlerini değiştirebilirsiniz. Ya da test amaçlı bir restore yapacaksanız ve bir sürü file’ınız varsa ve bütün file’ların değişik disklere dağıtmak yerine tek bir diskte olmasını istiyorsanız aşağıdaki gibi Relocate all files to folder deyip tek bir diski gösterebilirsiniz. Log dosyası için ayrıca Log file folder’ı seçmeniz gerekiyor.

Options kısmına geçiyoruz. Aşağıda gördüğünüz Overwrite the existing ile başlayan seçenek mevcut var olan bir veritabanının üzerine yazmak istediğinizde seçmeniz gereken seçenek. Recovery State’ten Recovery ya da Norecovery mode’da restore etmek istediğinizi seçiyorsunuz. Makalenin başındaki örnek senaryoda bu işleme neden ihtiyacımız olabileceğiniz anlatmıştım.

Script’ini aldığımızda karşımıza aşağıdaki gibi bir script çıkıyor.

USE [master]
RESTORE DATABASE [prodDB] FROM  DISK = N'C:\Backup\testDB.BAK' WITH  FILE = 1,  
MOVE N'testDB' TO N'C:\test\prodDB.mdf',  
MOVE N'testDB_log' TO N'C:\test\prodDB_log.ldf',  NOUNLOAD,  STATS = 5
GO

Map/Mount edilmiş share’e backup almak(Cannot open backup device ”. Operating system error 3(The system cannot find the path specified.).) Hatasının çözümü

Uzak bir sunucuda bir share tanımladınız ve bu share’e sql server servis hesabını yetkilendiremiyorsunuz. Sadece share’e bağlanmak için elinizde bir kullanıcı bilgisi var. Bu şekilde backup alabilmek için öncelikle o share’i sql server’un bulunduğu sunucuya map etmeniz gerekiyor. “Paylaşım Tanımlamak ve Tanımladığımız Paylaşımı SQL Server’ın olduğu sunucuya Map Etmek” isimli makalemde bu işlemin nasıl yapıldığını bulabilirsiniz. Map işlemi tamamlandıktan sonra map ettiğimiz ve drive haline gelen share’e aşağıdaki şekilde backup almak istediğimizde aşağıdaki şekilde bir hata alırız.

Bu hatayı almamak için uzak share’de tanımlı olan kullanıcı bilgilerini aşağıdaki script yardımıyla sql server sunucusuna tanıtabiliriz.

EXEC XP_CMDSHELL ‘NET USE myMappedDriveLetter: “myShare” /user:myUser myUsersPassword’

myMappedDriveLetter yazan yere biz Z ismini verdiğimiz için Z yazıyoruz.

myShare yazan yere \\ ShareSunucusu\Shareİsmi gibi bir ifade girmemiz gerekiyor. Tabi Burada ShareSunucusu yerine sizin tanımladığınız sunucu ismi ve Shareİsmi yerine de sizin tanımladığınız share ismini girmeniz gerekiyor.

myUser kısmına share üzerinde yetkili kullanıcı ismini girmeniz gerekiyor.

myUserPassword kısmına da share üzerinde yetkili kullanıcının şifresini girmeniz gerekiyor.

Bu script’i belirttiğim parametrelerle çalıştırdıktan sonra artık backup alabileceksiniz.

Aşağıda ki örnekten faydalanabilirsiniz:

EXEC XP_CMDSHELL 'NET USE Z: "\\185.657.15.12\sharedfolder" /user:MSSQLUser P@SsWoRd!'

NOT: Yukarıdaki script’i çalıştırdığınızda bazen aşağıdaki hatayı alıyor. Bu hatayı almaması için yukarıdaki örnekte gördüğünüz MSSQLUser kullanıcısının başına paylaşımın tanımlı olduğu sunucu ve \ yazmalısınız. Örneğin;

Hata:System error 1312 has occurred. A specified logon session does not exist

EXEC XP_CMDSHELL 'NET USE Z: "\\185.657.15.12\sharedfolder" /user:185.657.15.12\MSSQLUser P@SsWoRd!'

Daha sonra bu mapped drive’ı kaldırmak isterseniz aşağıdaki şekilde kaldırabilirsiniz. Aşağıdaki script bütün mapped drive’ları kaldırıyor. * yerine Z: koyarsak sadece Z drive’ını kaldırmış oluruz.

'NET USE * /delete /y'