Click here to Skip to main content
15,921,959 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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.

XML
EXEC sp_dbcmptlevel <Database Name>, 100;

or

ALTER DATABASE  <Database Name> SET COMPATIBILITY_LEVEL = 100;
Posted

If you're using MSSQL 2008 R2

You can use something like this during AUDIT_BACKUP_RESTORE_EVENT event.

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


Good luck!
 
Share this answer
 
Comments
Manu_Singh 20-Apr-11 7:16am    
But in our case database name is not fixed. We have more than 200 databases on the server. So I'll have to write trigger for all the Databases.
And executing above trigger gives me this error
Msg 1082, Level 15, State 1, Procedure CompatibilitySet, Line 29
"AUDIT_BACKUP_RESTORE_EVENT" does not support synchronous trigger registration.
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.

SQL
CREATE TRIGGER CompatibilitySet
ON DATABASE
FOR AUDIT_BACKUP_RESTORE_EVENT
AS
   EXEC sp_dbcmptlevel database_name, 100; 
 
Share this answer
 
v3
Comments
Manu_Singh 20-Apr-11 7:48am    
Same error. It's not working.
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
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900