CODEWITHSUNDEEP
sql-serverdatabase-backups
Jayden
Jayden

Reputation: 2778

Remove SQL Server Managed Backup Entry

Is there a way to remove a SQL Server Managed Backup entry entirely? (Using Microsoft SQL Server 2017 Enterprise Edition)

We have a database that was renamed (the database name, not the files). Managed Backup configuration was setup again against the new database name. We now have two entries for the same db_guid that show up when we execute:

select * from managed_backup.fn_backup_db_config (null)

db_name db_guid ... is_managed_backup_enabled
DB1 e3ff68.. 1
DB2 e3ff68.. 1

In this example DB1 and DB2 are the same database, DB1 was renamed to DB2.

Now trying to disable the backup for DB1 using:

EXEC msdb.managed_backup.sp_backup_config_basic   
 @enable_backup = 0,   
 @database_name = 'DB1'

We get the error: SQL Server Managed Backup to Microsoft Azure cannot configure the database, 'DB1', because it either does not exist or is offline.

We tried creating a new database called DB1, configuring the managed backup to disable the backups, but that's only exacerbated the problem. We now have a new entry in the managed databases with a different db_guid:

db_name db_guid ... is_managed_backup_enabled
DB1 e3ff68.. 1
DB2 e3ff68.. 1
DB1 8ee1ca.. 1

Upon dropping the 'new' DB1, it's disappeared from the managed backup.

How do I remove the old duplicate DB1 entry though (without dropping the database which we don't want to do).

Upvotes: 0

Views: 696

Answers (1)

user1539334
user1539334

Reputation: 1

delete d
FROM msdb.[dbo].[autoadmin_managed_databases] d
where d.drop_date is not null

Upvotes: 0

Related Questions