How to tell if you are using SQL Backup?

• 
  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%'
  

  petey December 04, 2007 20:42

  - 0 +
• 
  I found the information. I so appreciate your assistance.

  gsc_ghx December 04, 2007 22:01

  - 0 +
• 
  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...

  gsc_ghx December 07, 2007 16:42

  - 0 +
• 
  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
  

  petey December 08, 2007 04:10

  - 0 +
• 

  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

  sri8580 June 17, 2020 15:38

  - 0 +

Add comment

Please sign in to leave a comment.