一旦在数据库服务器上还原了任何数据库，就执行一个过程. [英] Execute a procedure, once any database is restored on the database server.

问题描述

在我们的组织中，数据库服务器已从SQL 2005升级到2008.无论何时，我们将还原收到的所有数据库备份.它的兼容性级别不是100.在某些情况下，它的兼容性级别低于100，这会引起问题.我编写了一个过程，将所有数据库的兼容性级别更改为100.通过一个调度程序，我每隔10分钟调用一次该过程.

我希望如果有人还原任何数据库.因此，应该自动调用该过程.或如果还原了任何数据库，如何调用该过程?

我可以通过以下命令更改兼容性级别.

Hi,
In our organization database server is upgraded from SQL 2005 to 2008. Whenever we restore any database backup that we receive. Its compatibility level is not 100. In some cases it''s compatibility level is below 100 that creates problem. I have written one procedure that changes the compatibility level of all database''s to 100. And through one scheduler I am calling that procedure after every 10 min.

I want that if anyone restore any database. So automatically that procedure should be called. or How to call that procedure if any database is restored ?

I can change the compatibility level through below commands.

EXEC sp_dbcmptlevel <Database Name>, 100;

or

ALTER DATABASE  <Database Name> SET COMPATIBILITY_LEVEL = 100;

推荐答案

如果您正在使用MSSQL 2008 R2

您可以在AUDIT_BACKUP_RESTORE_EVENT事件期间使用类似的方法.

If you''re using MSSQL 2008 R2

You can use something like this during AUDIT_BACKUP_RESTORE_EVENT event.

CREATE TRIGGER CompatibilitySet
ON DATABASE
FOR AUDIT_BACKUP_RESTORE_EVENT
AS
   EXEC sp_dbcmptlevel <Database Name>, 100;
   PRINT 'CompatibilitySet executed';
;

祝你好运！

Good luck!

你好Pong D. Panda/Manu_Singh

我认为打印声明存在问题.请删除它们，然后尝试.
我在下面的代码中所做的.

Hello Pong D. Panda / Manu_Singh

I think the issue with print statement . Please do remove the same and try.
that what I did in below code.

CREATE TRIGGER CompatibilitySet
ON DATABASE
FOR AUDIT_BACKUP_RESTORE_EVENT
AS
   EXEC sp_dbcmptlevel database_name, 100; 

以这样一种方式编写存储过程，即它将从服务器收集所有数据库.然后检查其兼容性级别(如果小于100则变为100).在作业中计划该存储过程....

这样，您不必担心添加的数据库.它将适用于所有人...

谢谢

Write a stored procedure in such a way that it will collect all the DB from server. Then check its compatibility level if its less then 100 make it 100. schedule that stored procedure in a job....

In this way you do not have to worry about the databases added. It will work for all...

Thanks

这篇关于一旦在数据库服务器上还原了任何数据库，就执行一个过程.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！