移动数据库文件后，对Azure的SQL Server备份停止工作

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.

我在内部的SQL Server 2014中有一个数据库。对于该数据库,我使用smart_admin.sp_set_db_backup过程配置了Azure存储备份。

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

之后我的备份停止了工作。函数smart_admin.fn_backup_db_config显示数据库备份记录存在但数据库标记为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.

SQL Server托管备份到Windows Azure无法配置数据库“DATABASE_NAME”,因为它不存在或处于脱机状态。

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.

我可以用任何方式删除备份配置信息并创建一个新信息吗?我发现的一个想法是重命名数据库,但我不能在生产中这样做。

3 个解决方案

#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.

Vad的答案很接近,但对于给定的db_name,autoadmin_managed_databases中可能有多条记录。您需要获取最后一条记录,即具有max autoadmin_id的记录。我选错了,在运行smart_admin.sp_set_db_backup或15分钟间隔运行后,SQL Server重新填充了drop_date。

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

#2

Managed Backups - is_dropped flag set to Yes after detaching database and reattaching DB

托管备份 - 在分离数据库并重新附加数据库后,is_dropped标志设置为“是”

Rename the database and set up managed backup again.

重命名数据库并再次设置托管备份。

Reference

#3

As I mentioned earlier I was not allowed to rename the database on the Production. So I found where it's marked as dropped and changed the value. That helped. Automated backups for the database started working again. IMO what happened looks like a bug in SQL Server 2014.

正如我之前提到的,我不允许在Production上重命名数据库。所以我发现它被标记为丢弃并改变了值。这有帮助。数据库的自动备份再次开始工作。 IMO发生的事情看起来像SQL Server 2014中的一个错误。

use msdb;
go

update [dbo].[autoadmin_managed_databases]
set drop_date = null
where [db_name] = '<DROPPED_DATABASE_NAME>'
go

#1

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

#2