How to retrieve SQL Server databases backup information?

 SQL Server Backup Information:

This article illustrates how to retrieve SQL Server database backups information, depending upon what exactly you are looking for, this article will help you to locate desired information. Below are few scenarios.


1-     How to retrieve backup information

select * from msdb.dbo.backupset

 Above statement will provide you all the databases backup history, this is going to be our master table to retrieve various type of information about database backups.

2-     How to retrieve backup dates of all the databases including system databases?

                select database_name, backup_start_date,backup_finish_date from msdb.dbo.backupset

3-      How to find out how many times backup was taken of a particular database?

                select count(1) from msdb.dbo.backupset where database_name='Your_db_name'

4-     How to find out latest backup of a particular user database?

select database_name, Max(backup_finish_date) as LatestBackup_Time from msdb.dbo.backupset
       where database_name='Your_database_name'
       group by database_name

5-      How to find out type (Full, Diff and Tran) of database backup?

select database_name, case type
      when 'D'
           then 'FULL'

       when 'I'
                        then 'Diff'

              when 'L'
                                    then 'tran'

end as [Type of Backup]
       from msdb.dbo.backupset


Note: You can add columns as you wish from our master table dbo.backupset
   6-    How to find out if database backup compression is ON or OFF?
    use [master]
    SELECT name, case value when 0 then 'Compression not enabled' when 1 then 'compression Enabled'
    end
      FROM sys.configurations
    WHERE name = 'backup compression default' ;
    GO
 

2 comments:

  1. I think there is a need to provide the best possible information about SQL and other SSIS operations.Their is a need to explore them more for solving complex database operations.

    SQL Server Load Rest Api

    ReplyDelete
  2. Toto is a game that predicts the outcome of the game by betting on sports games on the Toto site. If you win, dividends will be paid according to the set dividend rate. Therefore, it is important to choose a safe playground with a high dividend rate. 토토사이트 SITE 안전놀이터

    ReplyDelete