July 31, 2009 at 4:42 pm
Is it possible to restore a user database from SQL 2000 SP4 version onto a SQL 2000 SP3 version server?
If so what are the issues that can arise and is detach-attach the best method? or would you recommend backup-restore or copy database wizard?
August 2, 2009 at 12:27 am
Any suggestions are welcome,guys?
August 2, 2009 at 11:01 am
You could run some test but I don't think you should be running SQL Server 2000 with service pack below SP3a because the SP3a is security patch for the slammer worm. So I think you need to move the SP3 to SP4, why because most upgrade path require SP3a and above. I know SP3a was required to add Reporting Services to 2000.
http://en.wikipedia.org/wiki/SQL_slammer_worm
Kind regards,
Gift Peddie
August 3, 2009 at 4:12 am
i think when you do it will give you an error about the service pack compatibility issue.
i tried did this long time back but faintly remeber that i did get this error.
August 3, 2009 at 5:13 am
Really? Let me try this with pubs or northwind db first and see what happens.
August 3, 2009 at 8:35 am
While I agree with Gift's assessment of service packs, I've never had an issue restoring from sp4 to sp3. It's still sql 2000 so you shouldn't have any issues.
-- You can't be late until you show up.
August 3, 2009 at 3:58 pm
I tried copying database pubs from SP4 to SP3 sql 2000 server using both backup-restore and detach-attach method and they all worked fine without any errors.I think I shoukdnt have any issues with the prod database,can anyone forsee any issues?
August 3, 2009 at 6:37 pm
If a service pack or hotfix is going to make any change to a database it's only going to be the system objects that are affected. The system objects in user databases are fairly basic and it's very unlikely that these are going to change, so in the vast majority of cases there's no issues when moving databases between different "releases" of the same version.
Where you may run into issues is with objects that are created in the user database as a result of setting up "extra features" for a database, eg. replication. However, since moving the database is going to break those then it probably won't be an issue unless you want to set up the same feature once you've moved the database to the new server. In those cases you might need to drop the "feature" before you backup/detach the database to minimise the likelihood of issues when you try to set it up for the database once it's moved to the new server.
I know we've moved databases merrily between servers running different patch levels under SQL2k, SQL2k5 and SQL2k8 (but not between versions) and haven't run into issues to date outside replication setup, and we've been able to hack the database to enable it to be set up on the new server. Most of our tests have been going up in versions (eg. copying a production database to a QA server to test a service pack), but at this level it doesn't really matter if you're going up or down in patch-levels: if the system objects don't match you're going to get issues regardless of which way you're going.
August 4, 2009 at 2:11 am
thanks glen for the excellent and comprehensive information.
August 4, 2009 at 7:14 am
Mmmppfff - "merrily" - There's a new one for the old tool-belt! 😀
-- You can't be late until you show up.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply