Situatie
Pentru cazurile in care avem nevoie de o privire rapida asupra setarilor tipului de recovery pentru bazele de pe un server sql, am facut o procedura ce returneaza, pentru fiecare baza de date de pe server, tipul de recovery setat pe fiecare baza si data ultimului backup efectuat pentru fiecare baza in parte.
Solutie
CREATE PROCEDURE BD_RecoveryModelAndLastBackup_prc
as
SELECT
[name] AS [DatabaseName],
CONVERT(SYSNAME, DATABASEPROPERTYEX(N”+ [name] + ”, ‘Recovery’)) AS [RecoveryModel]
into #RecoveryModel
FROM master.dbo.sysdatabases ORDER BY name
SELECT
msdb.dbo.backupset.database_name,
max(msdb.dbo.backupset.backup_finish_date) as FinishDate
into #LastBackup
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
group by msdb.dbo.backupset.database_name
ORDER BY
msdb.dbo.backupset.database_name,
2 desc
select a.DatabaseName, a.RecoveryModel, b.FinishDate as LastBackup
from #RecoveryModel a
left join #LastBackup b on a.[DatabaseName] = b.database_name
Leave A Comment?