| Author |
 Topic  |
|
|
StephenD
Senior Member
Australia
1044 Posts |
 Posted - 30 May 2006 : 20:22:48
|
Ok, I'm in the process of moving hosts for one of my sites and my new host only supports SQL2000. My heavily modified snitz db is running on SQL2005 at the moment.
From googling and having tried it there is no way to restore an SQL2005 backup onto SQL2000.
What I have tried so far:
Ran sp_dbcmptlevel @dbname = ????, @new_cmptlevel = 80 on the SQL2005 db
Created a new SQL2000 db and exported from the SQL2005 into it. This brought all the data and tables across but not the primary keys, identity seeds and indexes. I tried manually adding the keys but ran into trouble on tables like FORUM_ALLOWED_MEMBERS where there are 2 keys in the table. I could not manually duplicate it in EM with a base install db.
At the moment I am sort of resigned to using setup.asp and recreate the blank db, then running all my dbs files and custom modifications, getting both db structures identical before importing records again from the SQL2000 db (with no keys, seeds and indexes).
While being a PITA I am sure this will work but has anyone got experience doing what I want to do without any of this hassle. I am facing the prospect of having to do this for 7 other dbs (not all snitz) in the near future.
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 30 May 2006 : 23:01:05
|
| That's the recommended path. You could make SQL Server 2005 generate a script to create the DB structure and run that script in the 2000 DB. You could then export the data from the 2005 Db to the 2000 Db, respecting the settings we've talked so many times, which means, for example, checking the enable identity insert for each table transferred between DBs. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
 |
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 31 May 2006 : 03:33:51
|
| you should be able to export the sql 2005 DB as a script, this should contain all primary keys defaults etc, then use the script to create the new db on sql2000, then once the table structures are correct you can import the data into the tables |
|
|
|
StephenD
Senior Member
Australia
1044 Posts |
Posted - 31 May 2006 : 05:09:32
|
| Ah thanks guys, I'll give that a try. |
|
|
|
StephenD
Senior Member
Australia
1044 Posts |
Posted - 31 May 2006 : 06:48:29
|
Thank you Huwr/Ruirib, took a few goes, turn off Users, turn off IF EXIST but all worked ok in the end. Saved me a lot of frustration.
Cheers
|
|
|
| |
Topic |
|
Topic Locked
