SQL Server backup to Azure stopped working after moving DB files

Question

I have a database in SQL Server 2014 on premises. For that database I have a backup to Azure storage configured using smart_admin.sp_set_db_backup procedure.

Recently I had to move the database files from one disk to another. I detached database, moved files, reattached it.

After that my backup stopped working. The function smart_admin.fn_backup_db_config shows that database backup record exists but database marked as is_dropped = 1

Any attempt to enable or disable backup for this database fails with error:

SQL Server Managed Backup to Windows Azure cannot configure the database, 'DATABASE_NAME', because it either does not exist or is offline.

Any way I can remove backup configuration information and create a new one? One of the ideas I found is rename the database, but I cannot do that in production.

Answer 1

Vad's answer is close, but there can be more than one record in autoadmin_managed_databases for a given db_name. You need to get the last record, which is the one with the max autoadmin_id. I picked the wrong one, and SQL Server repopulated the drop_date after I ran smart_admin.sp_set_db_backup or the 15 minute interval ran.

use msdb;
go

update [dbo].[autoadmin_managed_databases]
set drop_date = null
where [autoadmin_id]= (select max(autoadmin_id)
                       from [dbo].[autoadmin_managed_databases] 
                       where db_name = '<DROPPPED_DATABASE_NAME>')
go