Setare tip recovery si data ultim backup efectuat

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

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

Tip solutie

Permanent

Follow Us