Etiket arşivi: sql full backup ne zaman alınmış

MSSQL Backup Restore Sorgulama

Aşağıdaki scriptlerle sql databaseleriniz ne zaman backuplanmış, ne zaman restore edilmiş, tüm geçmişe ulaşabilirsiniz. type kolonu  backupın tipini gösteriyor, D full backup, L log backup, I diff backup olduğunu belirtir. Bu sorguları job yaparak backupı alınmayan db leri mailde attırabilirsiniz.

--------Full Backup History -------------------
select top 1000 database_name,user_name,backup_start_date,backup_finish_date,type,* 
from msdb.dbo.backupset where type='D' order by 4 desc


--------Diff Backup History -------------------
select top 1000 database_name,user_name,backup_start_date,backup_finish_date,type,* 
from msdb.dbo.backupset where type='I' order by 4 desc

--------Log Backup History -------------------
select top 1000 database_name,user_name,backup_start_date,backup_finish_date,type,* 
from msdb.dbo.backupset where type='L' order by 4 desc


--------En Son Full Backup Ne Zaman Alinmış------------------
select database_name,max(backup_finish_date) 
from msdb.dbo.backupset 
where type='D' 
group by database_name 
order by 2 desc

--------Database Restore History ------------------
SELECT 
[rs].[destination_database_name], 
[rs].[restore_date], 
[bs].[backup_start_date], 
[bs].[backup_finish_date], 
[bs].[database_name] as [source_database_name], 
[bmf].[physical_device_name] as [backup_file_used_for_restore]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id] 
ORDER BY [rs].[restore_date] DESC


--------------- EN son restore ne zaman yapilmis-------------------------
select ab.name as DBName,
ISNULL(aa.last_restore_date,'19000101') as LastRestoreDate,
ISNULL(aa.GECEN_SURE,-1) as DateDiffFromLastRestore
from ( select *,DATEDIFF(HH,last_restore_date,GETDATE()) as GECEN_SURE from 
(select DB_ID(destination_database_name) as DestDBID,destination_database_name,MAX(restore_date) as last_restore_date
from msdb.dbo.restorehistory
group by destination_database_name) a ) aa
right join sys.databases ab
on aa.DestDBID=ab.database_id order by 3