Friday, April 13, 2007

SQL 2k > SQL 2k5 > SQL 2k = NoGo

Right you're probably wondering why on earth you would want to migrate a database from SQL2k to SQL2k5 and then back to SQL2k heh?
Well let me explain the context; a request to migrate an existing Sharepoint 2003 environment into another existing Sharepoint 2003 environment was made by a customer. No problem eh? Well it is when one environment is only RTM and the other is SP2. Luckily the to-be-migrated environment wasn't that big so I could restore the environment onto a development machine using the RTM version then apply SP2 and backup the environment again and restore that servicepackked environment to the destination environment.
So what was the problem you think.. well the 'from' environment was SQL2K, the 'upgrade-to-sp2' environment was SQL2K5 and the 'destination' environment was SQL2K. And while doing the whole process of installing Sharepoint on the 'upgrade-to-sp2' environment I had forgotten that SQL2K5 was running as primary database server.
When I wanted to restore the sp2'ed environment I immediatly received errors in the SPSBackup/Restore tool (ofcourse without a proper error-code). The next thing I tried was to restore the backup files straight into SQL where I was faced with some very weird errors like 'only 64 backup devices are allowed'.. Then it struck me! So I opened up the development machine, opened up SQL Management Studio and there were my SQL2K databases, upgraded to SQL2k5.. although they had SQL2000 compatablility level, it was not downgradable to be restored/attached to a SQL2k database server.

Thus long story short : it's not possible to restore (migrated) sql2k5 databases to sql2k databaseservers.

No comments: