October 15, 2013 at 2:54 am
Hi,
We need to perform a SQL Server upgrade from 2000 to 2005 on a server which hosts an ERP database.
This is an old piece of software which is compatible with 2005 but no versions beyond that.
My plan is to backup and restore the database from the old 2000 instance and restore it to a newly built server with an SQL Server 2005 instance.
The problem i have is that if there is some critical issue which we can not resolve once this goes online i may well need to roll-back to the 2000 instance; including any data modifications which have taken place while the database was hosted on the 2005 instance.
I understand that you cannot perform a backup and restore from 2k5 to 2000 but is it possible to simply detach and attach back to a 2000 instance? If not, can anyone suggest some other options that i could investigate to give me this roll-back option?
Thanks in advance.
DBA Newbie.
October 15, 2013 at 5:09 am
It is not possible to attach a SQL2005 database on a SQL2000 instance. The only way to transfer your data-modifications from SQL2005 to SQL2000 is with export/import of data. You can use tools like Redgate Data Compare to get only the modified data.
It's a lot more work, but another solution is to implement triggers or logging of some kind to save all modifications on the SQL2005 database to a log file. In case of a rollback you need to apply these logged modification to the SQL2000 database.
October 15, 2013 at 5:34 am
Thanks for the advice, i think i will use the export of the data as my rollback.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply