10

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

12

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

8

I think I have found the process responsible for the backup.

I searched info in Windows System Event log and I found this lines in the temporal period interested by the backup:

Informazioni    16/09/2015 19:01:28 MSSQLSERVER 18264   Backup
Informazioni    16/09/2015 19:01:28 MSSQLSERVER 18264   Backup
Informazioni    16/09/2015 19:01:28 MSSQLSERVER 18264   Backup
Informazioni    16/09/2015 19:01:28 ESENT   2006    ShadowCopy
Informazioni    16/09/2015 19:01:28 ESENT   2006    ShadowCopy
Informazioni    16/09/2015 19:01:28 ESENT   2003    ShadowCopy
Informazioni    16/09/2015 19:01:28 ESENT   2003    ShadowCopy
Informazioni    16/09/2015 19:01:28 MSSQLSERVER 3198    Server
Informazioni    16/09/2015 19:01:28 MSSQLSERVER 3198    Server
Informazioni    16/09/2015 19:01:28 MSSQLSERVER 3198    Server
Informazioni    16/09/2015 19:01:28 MSSQLSERVER 3198    Server
Informazioni    16/09/2015 19:01:28 MSSQLSERVER 3198    Server
Informazioni    16/09/2015 19:01:28 MSSQLSERVER 3198    Server
Informazioni    16/09/2015 19:01:28 MSSQLSERVER 3198    Server
Informazioni    16/09/2015 19:01:28 MSSQLSERVER 3198    Server
Informazioni    16/09/2015 19:01:28 MSSQLSERVER 3198    Server
Informazioni    16/09/2015 19:01:25 MSSQLSERVER 3197    Server
Informazioni    16/09/2015 19:01:25 MSSQLSERVER 3197    Server
Informazioni    16/09/2015 19:01:25 MSSQLSERVER 3197    Server
Informazioni    16/09/2015 19:01:25 MSSQLSERVER 3197    Server
Informazioni    16/09/2015 19:01:25 MSSQLSERVER 3197    Server
Informazioni    16/09/2015 19:01:25 MSSQLSERVER 3197    Server
Informazioni    16/09/2015 19:01:25 MSSQLSERVER 3197    Server
Informazioni    16/09/2015 19:01:25 MSSQLSERVER 3197    Server
Informazioni    16/09/2015 19:01:25 ESENT   2001    ShadowCopy
Informazioni    16/09/2015 19:01:25 ESENT   2001    ShadowCopy
Informazioni    16/09/2015 19:01:25 ESENT   2001    ShadowCopy
Informazioni    16/09/2015 19:01:25 MSSQLSERVER 3197    Server
Informazioni    16/09/2015 19:01:23 ESENT   2005    ShadowCopy
Informazioni    16/09/2015 19:01:23 ESENT   2005    ShadowCopy
Informazioni    16/09/2015 19:01:11 BeVssProvider   0   Nessuna
Informazioni    16/09/2015 18:38:06 VSS 8224    Nessuna
Informazioni    16/09/2015 18:29:01 VSS 8224    Nessuna

You can clearly see the backup activity on MSSQLSERVER on top and short before other activity started by BeVssProvider, continuing with a ShadowCopy by ESENT.

I googled for these names and BeVssProvider is linked to Symantec Backup Exec™ for Windows Servers (http://systemexplorer.net/it/file-database/file/bevssprovider-exe).

So I guess that software is taking a snapshot of the machine every day (I'm not an expert in this domain).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.