SQL Server: how to determine what will break when downgrading a database?

Question

We're building an application for a client with the assumption that they'd be upgrading to a minimum of SQL Server 2005 from SQL Server 2000. We're finished our application, built on 2005, and are ready to integrate. Turns out that they're not going to upgrade their DB server.

So, now we're stuck with trying to sort out what will break.

We don't have access to SQL Server 2000, so we can only change the compatibility of the database to 80.

Aside from complete testing and reviewing every stored procedure (and I've read that changing the compatibility mode is not foolproof - so testing wouldn't be bombproof), is there any other way to determine what will break? Any tools out there? Scripts?

Edit

I'd prefer not to try restoring this onto their production DB server to see what errors are spit out, so that's not a good option.

Answer 1

I wouldn't be worried about your ANSI-SQL (setting the database compatibility level should take care of most of that), but there are a few big features you may have used that aren't available in SQL 2000 (there are many more, but these are the ones I've seen that are most popular):

• Common Table Expressions (CTE) - http://msdn.microsoft.com/en-us/library/ms190766.aspx
• TRY...CATCH blocks
• CLR-integrated stored procs

Also, though you shouldn't be, any selections directly from system tables (objects that begin with "sys" or are in the "sys." schema) may have changed dramatically between SQL 2000 and 2005+, so I'd see if you're selecting from any of those:

SELECT *
  FROM syscomments --I know, using a sys table to figure it out :)
 WHERE text like '%sys%'

Also, it's worth noting that while extended support is available for a hefty fee, Microsoft has officially ended mainstream support for SQL 2000, and will end extended support in the near future. This leaves your client (and you) without any updates from Microsoft in the case of security patches, bugs, or anything else you discover. I'd strongly encourage them ot upgrade to a newer version (at least 2005), though I suspect you've already been down that road.

Answer 2

Suggest you look in Books online for the page that spells out the differences between the two and look for those things. YOu can look over the list and then search for some new keywords in the table where the sp text is stored. That will give you a beginning list.

@rwmnau noted some good ones, I'll add two more SQL Server 2000 does not have varchar(max) or nvarchar (max), use text instead. SQl Server 2000 also does not have SSIS - if you are creating SSIS packages to import data or move data to a data warehouse or export data, all of those need to be redone in DTS.

Also it looks to me like you can still download the free edition of SQL Server 2000: http://www.microsoft.com/downloads/details.aspx?familyid=413744d1-a0bc-479f-bafa-e4b278eb9147&displaylang=en

You might want to do that and test on that.