SQL Server Performance Forum – Threads Archive

Database restore

I have a SQL server 2000 database dump file and i believe, i can restore this dump file directly under SQL 2005. Upon restore, if i change the database compatability mode to 2005, will it be consider a SQL 2005 database? What about the sql server 2005 DMV’s? Will they be automatically created? Appreciated some input on this. Thanks,
Cali

Yes after changing the compatibility level it will be considered as a Sql2005 database.
DMV’s are created at server level and not db level. You can use those DMv’s against the database restored. You can use performance dashboard after changing compatibility level to 90. But SP2 is necessary to use the dashboard tool. Sagar

DMV’s are available in master database and they can run against any database even the db is cmpt is 80 too…if I am not mistaken…
select * from master..sysobjects where type = ‘v’ When the db is in 80 cmpt, you can’t use 2005 commands and functions… Make sure you run at least update statistics after upgrade…
MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

When i restore a database i use the "SQL Server 2005 Upgrade Advisor" check the database before putting it in 2005 mode. And after that I rune new SQL Server 2005 Best Practices Analyzer just to be safe.

]]>