Downgrading Your 2008 Database to 2005

I have seen this scenario asked about more times than I’d care to think about, but it’s one of those areas that I feel I ought to get down to make sure it makes sense in my head.

You upgrade your SQL Server database to 2008, but you leave the database in 90 compatibility mode. Maybe you restored a backup, or attached the files, but in any case, it’s a 2005 database in your mind. Something doesn’t work, and so you want to move it back to SQL Server 2005 while you fix things.

Easy, right? Backup the database that’s a v90 database and restore it on a SQL Server 2005 instance. Wrong, that doesn’t work. The files have been physically changed to the 2008 format. Detach / attach doesn’t work either.

What can you do? I’ve seen this and in my mind you have a couple of options. IF you still have the old SQL Server 2005 instance, then things are easier, and you can just move data.

The two options I see are using the copy database tools in SSIS to move the data, or doing it yourself. I have been wary of the wizard tools and had problems with them in SQL Server 2000 DTS. They might work now, but I still am wary.

Instead my plan would be to a) move objects, and b) move data.

For the first item, you need to handle the following:

1. Logins – Use sp_help_revlogin
2. Objects – Script out all objects
3. Permissions – This should come from #2, but make sure that users are created and synced. sp_change_users_login can help find any that are not.

That’s pretty simple, though if this is the complete instance level move, you need to check on jobs, packages, etc. that might need to be moved back to 2005

To move data, there are multiple ways, but I think my dbsp_bcp_out and dbsp_bcp_in scripts that I wrote years ago would work. I used these to suck out all data in a database and move it back in. If you are doing this in a static database I would likely script out all constraints and drop them before doing the inserts and then add them back later. If they were in effect in the source, you should be fine with the integrity of the data.