We have an instance of SQL Server 2008 installed on a customer server. The customer's IT department is in charge of the various backups of the machine.
The SQL Server log shows there is a full backup every day at 7 PM but we can't find evidence of any plan scheduled in SQL Server.
The technicians we contacted couldn't tell us if there was some sort of automatic backup, all they told is that the entire machine is being backup up.
Using a script I found in this forum thread I found out that the physical device name is a GUID and that this means that this is an external backup process:
USE [msdb]
GO
SELECT
[bs].[database_name],
[bs].[backup_start_date],
[bs].[backup_finish_date],
[bs].Server_name,
[bs].user_name AS [BackupCreator] ,
[bmf].physical_device_name
FROM msdb..backupset bs
INNER JOIN msdb..backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id]
ORDER BY [bs].[backup_start_date] DESC
This is a row from the above query:
db_name 2015-09-16 19:01:23.000 2015-09-16 19:01:28.000
SERVER_NAME NT AUTHORITY\SYSTEM
{424F084A-F35D-4A66-8FC7-072268A89A77} 5
Moreover the backup start and finish date spans only for 5 seconds, so I guess it's clear it's not a job of sql server.
This is a line from the log:
2015-09-03 19:02:30.71 Backup
Database backed up.
Database: db_name, creation date(time): 2012/10/12(20:52:11), pages dumped: 3290021,
first LSN: 276028:152755:172, last LSN: 276028:152827:1, number of dump devices: 1,
device information:
(FILE=1, TYPE=VIRTUAL_DEVICE: {'{95380B0A-D50B-408F-B95F-1AB8975BA7F8}5'}).
This is an informational message only. No user action is required.
So, since they can't help us what can I do to track down the process responsible of the backups? We need this because we want to coordinate the backup in an ordered manner, and do the transaction log backups too in order to keep the log at a reasonable size (now we do shrink the log every week, and this is not the good way to go).
msdb.dbo.backupmediafamily.device_typeField to the OP's query to see what backup type it is (i.e. Disk, Tape, Virtual, Azure, or Permanent). I filtered on themasterdatabase and looked at the first entry to see what kicked it off. – MikeTeeVee Aug 16 '19 at 06:31