Can't identify source of mystery SQL backups

Question

(WAF v2 is blocking post saying it's too long. I wish I hadn't filled in that little pop-up survey with a positive response!! I'll have to post my full question as replies to my own post.)

I am new to running SQL on Azure and, although I've done a bit of development on SQL Server, I would still consider myself a novice.

We recently deployed a Windows VM in Azure that is running SQL Server 2017. I want to use Redgate SQL Backup to perform the backups of my systems. The problem is I'm seeing some "mystery" backups being performed that aren't going anywhere. They are not scheduled jobs in SQL Server Agent. The jobs I had running there have been disabled and the only jobs that are scheduled there are the jobs created by Redgate's program.

Answer 1

Good day @Chris Parker ,

WAF v2 is blocking post

This is a know bug in the system which the team working on fixing. You can get more information in this link. Please remember that this system is still in preview and this is not the only bug.

WHILE POSTING THIS ANSWER I GOR+T THIS ERROR AS WELL 😕

I want to use Redgate SQL Backup to perform the backups of my systems.

Obviously this part is not related to this forum as this is not Microsoft application. If the issue related to third party app like "Redgate SQL Backup", then you should get their help or use a forum which focus on this third party App.

They are not scheduled jobs in SQL Server Agent... the only jobs that are scheduled there are the jobs created by Redgate.

I do not understand your description exactly. First you say that the backup are not scheduled in the SQL Server Agent, but next you say that there are scheduled jobs created by the third party app.

If you want to use the SQL Agent directly then you can do so. You can create new jobs and steps and manage you backup using the SQL Server Agent. You can get more information in this link. As I understand, you use third party app to manage it for you (Usually if you have a few servers then it is simpler to manage directly using the built-in tools).

As said, this forum is not for supporting third party applications like Redgate product, and I am not Redgate experts. I can give you general information which I do not not how it is specifically related to how third party app works behind the scenes.

Note: There are two types of applications for backup SQL Server databases.

The first type are applications which use the built-in SQL Server Agent and basically create jobs and simply provide a rapper management tool.

The second type of third party application for backup SQL Server databases are using Volume Shadow Copy Service (VSS) for the task. SQL Server has a special VSS Writer in order to provide full compatibility with the way the server works.

I have a full tutorial with code which demonstrate how to develop such application using C#. IT is very old post and I am not sure what was changed in the classes since. I did not develop any such application since 2014. You can check if this give you some understanding. This post is in English. In addition I have full lecture about the entire topic which I presented in the Israeli SQL Server User group at Microsoft Offices, but it is in Hebrew so I am not sure how useful it is for you.

... will continue in the comment...

Answer 2

Good day @Chris Parker ,

WAF v2 is blocking post

This is a know bug in the system which the team working on fixing. You can get more information in this link. Please remember that this system is still in preview and this is not the only bug.

WHILE POSTING THIS ANSWER I GOR+T THIS ERROR AS WELL 😕

I want to use Redgate SQL Backup to perform the backups of my systems.

Obviously this part is not related to this forum as this is not Microsoft application. If the issue related to third party app like "Redgate SQL Backup", then you should get their help or use a forum which focus on this third party App.

They are not scheduled jobs in SQL Server Agent... the only jobs that are scheduled there are the jobs created by Redgate.

I do not understand your description exactly. First you say that the backup are not scheduled in the SQL Server Agent, but next you say that there are scheduled jobs created by the third party app.

If you want to use the SQL Agent directly then you can do so. You can create new jobs and steps and manage you backup using the SQL Server Agent. You can get more information in this link. As I understand, you use third party app to manage it for you (Usually if you have a few servers then it is simpler to manage directly using the built-in tools).

As said, this forum is not for supporting third party applications like Redgate product, and I am not Redgate experts. I can give you general information which I do not not how it is specifically related to how third party app works behind the scenes.

Note : There are two types of applications for backup SQL Server databases.

The first type are applications which use the built-in SQL Server Agent and basically create jobs and simply provide a rapper management tool.

The second type of third party application for backup SQL Server databases are using Volume Shadow Copy Service (VSS) for the task. SQL Server has a special VSS Writer in order to provide full compatibility with the way the server works.

I have a full tutorial with code which demonstrate how to develop such application using C#. IT is very old post and I am not sure what was changed in the classes since. I did not develop any such application since 2014. You can check if this give you some understanding. This post is in English. In addition I have full lecture about the entire topic which I presented in the Israeli SQL Server User group at Microsoft Offices, but it is in Hebrew so I am not sure how useful it is for you.

The jobs I had running there have been disabled and the only jobs that are scheduled there are the jobs created by Redgate's program.

According to this information, the application which you choose manage the backup nased on the SQL Server Agent. In this case it make sense that the application disabled you other backup jobs in order to have one time-line of backups.

Anyhow, you best option is to go the Redgate forums and get help from red-gate experts:

https://forum.red-gate.com/

I asked about this in the Redgate forum and they suggested I look at something from outside the VM.

1. confirm that the job is well scheduled and that it was executed.
2. List information for all jobs and Check the status of the execution during the execution using dbo.sp_help_jobactivity USE msdb ;
   GO
   -- List information for all jobs
   EXEC dbo.sp_help_job ;
   GO
   -- lists activity for all jobs that the current user has permission to view.
   EXEC dbo.sp_help_jobactivity ;
   GO
3. Check the result (history) of the executions of the job using sp_help_jobhistory USE msdb ;
   GO
   EXEC dbo.sp_help_jobhistory
   @Job _name = Your-Job-Name' ;
   GO
4. Make sure that the job is executed using a user which have the permission to write the backup file and to execute the BASCKUP command (windows user and SQL Server user).

If the issue was not solved, then Provide us the information you get so we can go to the nest step

Answer 3

Hi @pituach

Thanks for your message. Apologies for the late reply.

There are some misunderstandings on my question but I probably could have written it more clearly. I will try to be as succinct as possible.

Someone other than myself setup this Azure VM SQL server

That person created two backups jobs using SSMS. These were very basic daily full and periodic log backups.

I wanted some more features so I bought Redgate's product. I implemented it and disabled the two hand-built jobs in SQL Server Agent.

Redgate's product programmatically creates sophisticated backup jobs that SQL Server Agent runs.

Redgate's product displays the job history of the SQL server.

Looking at this history, I noticed that every four hours all my databases have a full backup performed against them. I don't know what is initiating these backups.

These "mystery" backups are what I'm trying to identify.

SELECT a.backup_start_date, a.database_name, a.type, b.device_type , b.logical_device_name, b.physical_device_name, a.name, a.user_name FROM msdb..backupset a INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id WHERE logical_device_name IS NULL AND database_name = 'MyDatabase' ORDER BY a.backup_start_date

2019-11-06 04:01:28.000 MyDatabase D 7 NULL {B347BF74-XXXX-XXXX-BCF6-XXXXXXXXXXXX}9 NULL DOMAIN\SERVER$
2019-11-06 06:00:29.000 MyDatabase D 2 NULL e:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\MyDatabase\MyDatabase_backup_2019_11_06_060002_7473862.bak MyDatabase_backup_2019_11_06_060002_7473862 DOMAIN\SERVER$

The Redgate forum suggested this was being caused by a snapshot backup of the VM but I have not been able to correlate this to anything in the VM documentation.

Answer 4

Hi @Chris Parker , were you able to identify the source of "mystery" backups ? I experiencing a similar issue on a SQL Server VM in Azure where databases are getting backed up every few hours.