How to tell if you are using SQL Backup?

gsc_ghxgsc_ghx Posts: 73
edited December 8, 2007 4:10AM in SQL Backup Previous Versions
I am new to sql server dba and have never used this software (SQL Backup) and there is no one who can tell me if they use the Redgate software for backups. How would i know?

Thanks
g

Comments

  • peteypetey Posts: 2,358 New member
    Is SQL Backup installed?
    Open the Service Manager, look for a service beginning with the name 'SQL Backup Agent'.

    Is it being used to perform backups?
    Open Query Analyzer or Management Studio, run the following (this query will be slow if you have a lot of data):
    SELECT b.backup_start_date, b.database_name, b.user_name, a.logical_device_name, a.physical_device_name 
    FROM msdb..backupmediafamily a
    INNER JOIN msdb..backupset b ON a.media_set_id = b.media_set_id
    WHERE b.backup_start_date >= GETDATE() - 7
      AND a.logical_device_name LIKE 'Red Gate SQL Backup%'
    
    Has it ever been used to perform backups?
    Open Query Analyzer or Management Studio, run the following (this query will be slow if you have a lot of data):
    SELECT b.backup_start_date, b.database_name, b.user_name, a.logical_device_name, a.physical_device_name 
    FROM msdb..backupmediafamily a
    INNER JOIN msdb..backupset b ON a.media_set_id = b.media_set_id
    WHERE a.logical_device_name LIKE 'Red Gate SQL Backup%'
    
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • I found the information. I so appreciate your assistance.
  • when i run the following code - SELECT b.backup_start_date, b.database_name, b.user_name, a.logical_device_name, a.physical_device_name
    FROM msdb..backupmediafamily a
    INNER JOIN msdb..backupset b ON a.media_set_id = b.media_set_id
    WHERE b.backup_start_date >= GETDATE() - 7
    AND a.logical_device_name LIKE 'Red Gate SQL Backup%'

    it returns a value Red Gate SQL Backup (4.2.0.511):00000000036300000000000000C2760000005C17000000030101 - does the 4.2.0.511 refer to the version of backup software we are using?

    Also - how would i find out what servers are running which license we have? I need to identify which servers have which license...

    Thank you...
  • peteypetey Posts: 2,358 New member
    Yes, 4.2.0.511 represents the SQL Backup version.

    If you use the SQL Backup GUI and register your servers, those with SQL Backup installed will display a SQL Backup icon in its name.

    If you want to check manually, run the following:
    IF OBJECT_ID('sqbutility') IS NOT NULL
    BEGIN
    	DECLARE @value varchar(128)
    	EXEC master..sqbutility 1030, @value OUTPUT
    	SELECT @value
    END
    
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • sri8580sri8580 Posts: 2 New member

    Hi...using SQL backup we can tell with below solution-

    SQL Server Full Database Backups

    SELECT database_name, backup_start_date, type, * 
    FROM msdb.dbo.backupset
    WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()
    AND Type = 'D'
    ORDER BY backup_set_id DESC
    GO

    SQL Server Transaction Log Backups

    SELECT database_name, backup_start_date, type, * 
    FROM msdb.dbo.backupset
    WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()
    AND Type = 'L'
    ORDER BY backup_set_id DESC
    GO

    SQL Server Differential Backups

    SELECT database_name, backup_start_date, type, * 
    FROM msdb.dbo.backupset
    WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()
    AND Type = 'I'
    ORDER BY backup_set_id DESC
    GO

    SQL Server File\File Group Backups

    SELECT database_name, backup_start_date, type, * 
    FROM msdb.dbo.backupset
    WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()
    AND Type = 'F'
    ORDER BY backup_set_id DESC
    GO
Sign In or Register to comment.