SQLINFO- An i-book For SQL Server by Varun R

Simple thoughts but quite different…!

SQLINFO- An i-book For SQL Server by Varun R - Simple thoughts but quite different…!

SQL Server – Database Backup Information

In previous post we discussed about a script which will give the restore information. In this post we will discuss about a script which will give the backup information.

 

SELECT server_name        AS [Servername], 
       database_name      AS [Databasename], 
       [user_name]        AS [BackupUser], 
       backup_start_date  AS [BackupStartDate], 
       backup_finish_date AS [BackupFinished], 
       CASE 
         WHEN type = 'D' THEN 'FullBackup' 
         WHEN type = 'I' THEN 'Differential' 
         WHEN type = 'L' THEN 'LOG' 
         WHEN type = 'F' THEN 'Partial' 
         WHEN type = 'Q' THEN 'Differential partial' 
         ELSE NULL 
       END                AS [BackupType], 
       MF.physical_device_name, 
       CASE 
         WHEN device_type = 2 THEN 'Disk' 
         WHEN device_type = 5 THEN 'Tape' 
         WHEN device_type = 7 THEN 'VirtualDevice' 
         WHEN device_type = 105 THEN 'A Permanant Backup Device' 
         ELSE NULL 
       END                AS [BackupDevice] 
FROM   msdb.dbo.backupset Bs 
       INNER JOIN msdb.dbo.backupmediafamily MF 
               ON Bs.media_set_id = MF.media_set_id 
ORDER  BY backupfinished DESC

 

This script will give the following information like backup start and end date, Type of backup, backup device, path of backup etc. Let us look into the screen shot

Backup Information

SQL Server – Database Restoration Information

Today I am going to give a small script which helps you to find the database restoration details.

SELECT RH.restore_date              AS [RestorationDate], 
       RH.[user_name]               AS [RestoredUser], 
       CASE 
         WHEN rh.restore_type = 'D' THEN 'Database' 
         WHEN rh.restore_type = 'F' THEN 'File' 
         WHEN rh.restore_type = 'G' THEN 'Filegroup' 
         WHEN rh.restore_type = 'I' THEN 'Differential' 
         WHEN rh.restore_type = 'L' THEN 'Log' 
         WHEN rh.restore_type = 'V' THEN 'Verifyonly' 
         WHEN rh.restore_type = 'R' THEN 'Revert' 
         ELSE rh.restore_type 
       END                          AS [RestoreType], 
       RH.destination_database_name AS [RestoredDatabase], 
       RF.destination_phys_name     AS [PhysicalDestiation] 
FROM   msdb.dbo.restorehistory RH 
       INNER JOIN msdb.dbo.restorefile RF 
               ON RH.restore_history_id = RF.restore_history_id

Result:

 Restoration Details