Link to home
Start Free TrialLog in
Avatar of dbaSQL
dbaSQLFlag 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?
Avatar of dbaSQL
dbaSQL
Flag of United States of America image

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)
Avatar of 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
Avatar of lcohan
lcohan
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dbaSQL

ASKER

The managed_backup objects do not exist in msdb.  None of the options on that reference are usable.   It is v2014.
Avatar of dbaSQL

ASKER

Maybe this.  EXEC smart_admin.sp_set_db_backup @database_name='Adventure Works 2014' ,@enable_backup=0;
Back shortly with status.
Avatar of 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.