Keeping with the times isn't always an easy task when it comes to databases... So how do you deal with a DB that needs brought up to speed? That's where things can get tricky.
I've heard a million times how you don't really need to worry about switching compatibility modes and there is rarely ever issues, which may be true in most cases. But what if you need to make a major leap (IE 2000 to 2016) and that entails a number of breaking syntax changes?
Of course there is the wonderful Microsoft Data Migration Assistant, which will show you every single issue in a beautiful UI with the ability to export. This is undoubtedly the best place to start and really is a great tool. But does it really find every issue? In my experience that is a definite NO.
Don't panic - there are ways to identify these rogue issues missed in the initial assessment other than going full on break fix mode with applications on your dev box.
Here is a script I found some time ago(Credit to Unknown) and tweaked that is great for finding issues on 2008 and prior DB's.
If your DB is running in 2000 mode and you want to go to say 2008. Flip the Mode of the db on your Dev instance and run the script. It will find any issues related to Stored Procedures, Functions, Triggers and views and display the issues associated with the object:
Simple, but effective.
-Matt
Comments