SQL Server – Can’t Find Process Creating Backups

backupsql serversql-server-2008

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).

Best Answer

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.

The fact that VIRTUAL_DEVICE comes into the backup message clearly says that this is not backup scheduled by T-SQL Job or SQL Server so you wont find it anywhere in SQL Server. You either have some third party tool or windows inbuilt backup mechanism running which is taking this backup.

Now if you really want to find out the source of backup consult windows team or storage team and you would get a fair idea. If all the stakeholders say they do not have idea about the source of backup ( which is highly unlikely) you can stop such backup from happening by disabling SQL Server VSS writer from Services.msc.

Such Third party backups rely on SQL Server VSS writer services to connect to database and take backup.

Before proceeding above I would suggest you to talk to backup/windows admin if they are not aware about any such backup you can go ahead and disable this service. Otherwise you must not and you should consult more regarding this with them.

Please read Information Shedding Light on VSS Backups

Related Question