Monday, November 24, 2008

moving a database from sql 2005 to sql2000.....

This I would have thought would have been a fairly easy task, as the DB in question used to be hosted on a SQL2000 server and was detached, then attached to the SQL2005 development server to be adjusted etc etc. I even left the compatibility set to 80.

The only structural changes were via the aspnet provider script + the odd new table/stored proc etc

However when I came to move the DB back to the live server all hell broke loose. Well I might be exaggarating a bit there, but it felt like it.

To cut a long story short I followed these steps to get it all to work;

1- Script Database as .... Create to ...
2- Run the installProviders.sql for aspnet
3- Task ... Generate scripts ... (then manually edit this to remove any of references already created by the installProviders.sql above, also had to manually remove a number of the unusual SQL2005 extra bits)
4- Add required sql users
5- I used Redgate SQL Compare (trial edition) to move the actual data across (the SQL 2005 export tool failed)
6- crossed fingers and it seems to have worked...

It seems annoying that even if you set the compat to SQL2000t hat its still won't work, but I know now. ha ha.

As soon as possible I think the live server will get an upgrade....

No comments: