SQL受管实例还原已删除/删除的数据库 [英] SQL managed instance restoring deleted/dropped database

问题描述

你好

您能否确认在受管实例中没有还原已删除的数据库".来自 

https://docs.microsoft.com/zh-cn/azure/sql-database/sql-database-recovery-using-backups#deleted-database-restore

绝对是真的吗?删除的数据库永远消失了吗?

Hello

can you please confirm if "Restoring deleted database is not available in Managed Instance." from 

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-recovery-using-backups#deleted-database-restore

is absolutely true ? deleted database is gone forever?

如果是这样的话，您对防止SA用户丢弃它的建议是什么?

防止特定DB删除的常用方法仍然可以在prem SQL上从SQL MI删除数据库.



创建触发器Prevent_DB_Drop 
在所有服务器上
FOR DROP_DATABASE
AS
宣告@DatabaseName NVARCHAR(100)，
        @eventData XML

-将EVENTDATA()函数值设置为我们的xml，以便我们可以对其进行解析
SET @eventData = EVENTDATA()           
选择@DatabaseName     = @ eventData.value('(//EVENT_INSTANCE/DatabaseName)[1]'   ，'varchar(128)')
IF @DatabaseName ='CannotDropMe'
 开始
    PRINT'不允许您删除此数据库.'
   回滚；
 结束
开始
在所有服务器上启用触发器[Prevent_DB_Drop]

If that is so what would be your suggestion to prevent SA user from dropping it?

Usual method to prevent specific DB drop that works in on prem SQL drops database from SQL MI anyways.



CREATE TRIGGER Prevent_DB_Drop 
ON ALL SERVER 
FOR DROP_DATABASE
AS 
DECLARE @DatabaseName NVARCHAR(100),
        @eventData XML

--set the EVENTDATA() functions value to our xml so we can parse it
SET @eventData = EVENTDATA()           
SELECT @DatabaseName     = @eventData.value('(/EVENT_INSTANCE/DatabaseName)[1]'     ,'varchar(128)' )
IF @DatabaseName = 'CannotDropMe'
  BEGIN
    PRINT 'You are not allowed to drop this Database.' 
    ROLLBACK;
  END
GO
ENABLE TRIGGER [Prevent_DB_Drop] ON ALL SERVER 

推荐答案

Hello,

DDL Triggers are supported at the database level and instance level.

https://docs.microsoft.com/zh-CN/azure/sql-database/sql-database-features

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-features

If you had scheduled backups to URL for that database, you can always restore a database from a native backup you have stored on a storage account.

https://docs.microsoft.com/zh-CN/azure/sql-database/sql-database-managed-instance-get- starts-restore ?? wt.mc_id = MVP

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-managed-instance-get-started-restore??wt.mc_id=MVP

看看MSDB，以了解一些已创建的备份，而您可能不知道

Take a look on the MSDB to know about some backups that are created and you may not know

SELECT backup_start_date, server_name, user_name, type, compatibility_level, database_name, machine_name

Hope this helps.

Regards,

SQLCoffee.com

Alberto Morillo
SQLCoffee.com

这篇关于SQL受管实例还原已删除/删除的数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！