Situatie
Mai jos este un script ce afiseaza toate restaurarile de baze de date efectuate pe un server MSSQL. Daca se doreste vizualizarea restaurarilor unei anumite baze de date se inlocuiste valoarea null cu numele bazei in variabila @dbname de la inceputul scriptului.
Scriptul afiseaza restaurarile din ultimele 365 de zile. Daca se vrea afisarea istoricului pe un alt numar de zile, se inlocuieste valoarea null cu numarul de zile dorit in variabila @days de la inceputul scriptului.
Solutie
DECLARE
@dbname SYSNAME,
@days INT;
SET @dbname = null; — se lasa null pt toate bazele sau se pune numele bazei dorite
SET @days = null; — se lasa null pt a se obtine istoricul pt ultimul an sau se pune numarul de zile dorit
SELECT
distinct
[rsh].[destination_database_name] AS [database],
[rsh].user_name AS [restored by],
CASE
WHEN [rsh].[restore_type] = ‘D’
THEN ‘Database’
WHEN [rsh].[restore_type] = ‘F’
THEN ‘File’
WHEN [rsh].[restore_type] = ‘G’
THEN ‘Filegroup’
WHEN [rsh].[restore_type] = ‘I’
THEN ‘Differential’
WHEN [rsh].[restore_type] = ‘L’
THEN ‘Log’
WHEN [rsh].[restore_type] = ‘V’
THEN ‘Verifyonly’
WHEN [rsh].[restore_type] = ‘R’
THEN ‘Revert’
ELSE [rsh].[restore_type]
END AS [restore type],
[rsh].[restore_date] AS [restore started],
[bmf].[physical_device_name] AS [restored from]
FROM [msdb].[dbo].[restorehistory] AS [rsh]
INNER JOIN [msdb].[dbo].[backupset] AS [bs] ON [rsh].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN [msdb].[dbo].[restorefile] AS [rf] ON [rsh].[restore_history_id] = [rf].[restore_history_id]
INNER JOIN [msdb].[dbo].[backupmediafamily] AS [bmf] ON [bmf].[media_set_id] = [bs].[media_set_id]
WHERE [rsh].[restore_date] >= DATEADD([dd], ISNULL(-@days, -365), GETDATE())
AND [destination_database_name] = ISNULL(@dbname, [destination_database_name])
ORDER BY 4 DESC, 1
Leave A Comment?