Avatar of dbaSQL
dbaSQL
Flag for United States of America asked on

How do I find out what is creating these backups to Azure?

I just deployed a change yesterday to a couple of servers so the backups are now being written to Azure Blob Storage.  Everything tested well, and looked good -- until today.  I am seeing FULL backups running hourly -- that I am not running.  I am also seeing  transaction log backups coming in more frequently -- that I am also not running.  I have queried the backup history and can see that the FULLs are all copy_only backups -- but again, I have no idea what is running  them.

This is the URL that I am targeting with the backup command to write the files, and I have confirmed the ones I expected were written to this location:
 @URL = 'https://companynameprodbackups.blob.core.windows.net/sqlbackups/'

This is where all of the other backups are being written:
https://companynamebackupstore.blob.core.windows.net/39e6490f14b24fcfa34606d184baa7f1/dbname_1c97a7a0d3184c5d9355a7ee99f7ff1a_b2a72d1bcf5d4cd99540ce881f63554f_20180822111208-06.log
https://companynamebackupstore.blob.core.windows.net/39e6490f14b24fcfa34606d184baa7f1/dbname_595d1e54a57548b7977f5c218c6f3df1_8cd2a3504f2a464cab281ccddc9b3817_20180822111209-06.bak



How do I find out what is creating these backups?
Microsoft SQL ServerAzure

Avatar of undefined
Last Comment
dbaSQL

8/22/2022 - Mon
dbaSQL

ASKER
This tells me I have managed backups -- completely unaware of this.  How do I disable/stop these managed backups?

USE msdb;
SELECT db_name, is_managed_backup_enabled, retention_days, storage_url, encryption_algorithm
FROM smart_admin.fn_backup_db_config(NULL)
lcohan

Can you please run a SQL command like below to see if any SQL Job is running those backups?


SELECT  js.database_name as DatabaseName,
                 jobs.Name as JobName,
                 js.step_id as StepID,
                 js.step_name as StepName,
                 js.command as StepCommand
FROM     msdb.dbo.sysjobs as jobs
                INNER JOIN msdb.dbo.sysjobsteps as js ON jobs.job_id = js.job_id
WHERE  --jobs.[enabled] = 1 AND
js.command LIKE  '%backup%'
ORDER BY jobs.Name,js.step_id
ASKER CERTIFIED SOLUTION
lcohan

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
dbaSQL

ASKER
The managed_backup objects do not exist in msdb.  None of the options on that reference are usable.   It is v2014.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
dbaSQL

ASKER
Maybe this.  EXEC smart_admin.sp_set_db_backup @database_name='Adventure Works 2014' ,@enable_backup=0;
Back shortly with status.
lcohan

dbaSQL

ASKER
Yes, lcohan, I saw that, and i tried them both unsuccessfully.  It was not until afterward that I realized the managed_backup objects did not exist in the msdb.  The smart_admin objects do, and I used this to successfully disable the managed backups on all of the databases:

EXEC smart_admin.sp_set_db_backup @database_name='XXXXX',@enable_backup=0;

Though I found the answer myself, I appreciate you taking a look.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.