1

Recently our development team migrated couple of sql server 2000 Databases onto SQL2K8R2.

Now we creating the backup strategy, and just had a doubt, that:

1) Compatibility level of those DB's still show 80, we asked the team if that can be changed to current , they had no clue. I searched on web, but could not find any such stuff which clarifies on what point should we or should we not change the compatibility level of the migrated db's.

2) For the Migrated DB's having compatibly 80, are we good to take compressed backups? I am just curious because if we try to restore on the same or upper version , it might not have any conflict.?

Please advice, thanks!

2

I searched on web, but could not find any such stuff which clarifies on what point should we or should we not change the compatibility level of the migrated db's.

As a good practice and suggested by Microsoft you should change compatibility level of database to match with server version on which it is hosted. There are few legacy applications which might require version as 80 for that you have to test, I cannot tell upfront whether you change it or not. You should see what all features were deprecated in 2008 R2 as compared to SQL Server 2000 below two articles would help you

Breaking Changes to DB feature in SQL server 2008 R2

Deprecated database engine features in SQL Server 2008 R2

Deprecated SQL Server features in SQL Server 2008 R2

Regarding changing compatibility level if you face any issue you can immediately change it back to 80, that is why i said a round of testing is required. If you read Change compatibility Level of database it would really help you

For the Migrated DB's having compatibly 80, are we good to take compressed backups?

Yes you can take compressed backup of 80 compatibility database hosted on SQL Server 2008 R2 subject to condition SQL Server edition is Standard or higher edition

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.