November 11, 2007 at 6:12 pm
Hi,
My produciton box is a 2005 server. my test server is a 2000 server.
I'd like to restore a backup from the 2005 server to the 2000 server.
The last time I tried, I got an error referring to the compatibility level.
Would changing the 2005's database compatibility level from 90 to 80, doing a backup and then restore of this version help?
How do i do this?
November 11, 2007 at 8:15 pm
It's not possible - even changing the compatibility level on the 2005 box's database will make no difference. The file formats are different.
However, you could transfer the database table by table, etc. Both SQL Server Integration Services in 2005 and Data Transformation Services (aka Import & Export Data) in SQL 2000 will do this for you.
November 11, 2007 at 8:21 pm
Hi,
that just sucks.....
think i will tell the users its not possible, and they'll have to wait until the test server upgrade.
November 12, 2007 at 1:44 pm
Create a blank database on the 2000 box. Script out the tables etc from the 2005 box and run that on the 2000 box. If that works then you can import/export data. Again not sure if it will work but worth a try.
November 12, 2007 at 1:47 pm
Good advice but I like the "... tell the users it is not possible..." answer!!!
November 12, 2007 at 1:51 pm
That is the "nicest" thing we can tell them isn't it?
November 12, 2007 at 6:36 pm
November 13, 2007 at 4:53 am
Yeah, unfortunately you can't move a 2005 database to 2000 without a lot of work. Even then, something will probably fail.
Kimberly Tripp (works for MS) actually said this about a dozen times during her SQL 2005 webcasts (as 2005 was coming out). "You can't downgrade 2005 to 2000. Once it's been upgraded to 2005, that's it. You're done. So keep a copy of your original 2000 database just in case." (I'm paraphrasing a little).
You can do a side-by-side install of 2005 to 2000, though, on the same server. Use a different instance name, and then move the DB from Prod to the Test 2005 instance.
The caveats for that are you will have to install 2k5 to a different path than 2000 and probably re-install SSRS 2000 (if you have it on that box). And, if you plan to use Visual Studio at all, you'll want to install VS BEFORE installing SQL Server 2k5.
November 13, 2007 at 6:23 am
Brandie, have you tried the Database Publishing Wizard that Vidhya described in that last post?
November 13, 2007 at 6:42 am
No, I haven't. I've never needed to revert. But even though that wizard is out there, I'm sure it can't revert everything.
There are a lot of changes in SQL 2k5 that if those items exist in your current DB simply can't be converted. DDL triggers for one. Certain datatypes for another (though Microsoft may have been able to script Varchar(MAX) to Text, but what if you're not wanting to use Text for that column?). And schemas / ownership are not quite the same thing between the two either. What if you've got a schema called "MyDev" in 2k5? Well, it's doubtful you'll have a user account called "MyDev" unless you physically create one. Or, maybe the wizard creates a SQL Server account called "MyDev"... But what if your database is in Windows Only auth mode? You can't create SQL Only accounts then.
That's the sort of thing I'm thinking of when I say the reversion isn't so easy.
So to those of you who have used the Wizard, have you run into the above issues? What did the wizard do when you had such things in your 2k5 db and you were trying to revert to 2000? Since I've never needed the Wizard myself (and unfortunately don't have time to test it due to urgent projects at work), I am curious as to whether or not it bombs in cases like that.
FYI: At the time of the webcasts I was speaking about, I don't think this wizard existed or Kimberly surely would have mentioned it when the audience continued to ask her about reversion.
November 13, 2007 at 7:16 am
This is from the readme file. As you would expect, if you use any 2005-specific features you'll get burned. But I would guess that there are lots of 2005 databases out there that have been converted simply to beat the deadline for the end of 2000 support, but don't use any of the new features. . .
Known Issues with Database Publishing Wizard 1.0
Issues when scripting SQL Server 2005 databases to SQL Server 2000
User-defined functions and stored procedures with 2005-specific T-SQL (PIVOT, CTE, APPLY, etc) will script without error, even though the resulting script will not work on SQL Server 2000.
Tables with columns defined using 2005-only collations (Indic_General_90_CI_AS, Japanese_90, etc) will script without error, even though the resulting script will not work on SQL Server 2000.
User-defined functions containing the phrase "END;" at the end will script, even though this causes a syntax error on SQL Server 2000.
Constraints other than PRIMARY KEY or UNIQUE placed on computed columns will script without error, even though the resulting script will not work on SQL Server 2000.
User-defined functions and stored procedures specified explicitly with EXECUTE AS CALLER will script without error, even though the resulting script will not work on SQL Server 2000.
Issues related to roles, users, and schemas
When scripting a source database, Database Publishing Wizard converts any users the database contains into roles, and where appropriate, schemas. This behavior occurs because Database Publishing Wizard does not script logins.
Roles owned by other roles or application roles (using the AUTHORIZATION option) may not work.
User-defined data types will not be schema qualified when scripting to SQL Server 2000.
If schema qualification is turned off in the Database Publishing Wizard options, this will not affect the T-SQL source of any user-defined functions or stored procedures.
November 13, 2007 at 8:24 am
I just ran the utility on a 3 GB database, it took 45 minutes and created a 12 GB sql file, using a 2.8 GHz P4 with 2 GB RAM running XP Pro.
Not something you would want to do on a daily basis. No wonder MS didn't mention the utility. Fat and slow, the hallmark of a 1.0 product.
November 14, 2007 at 4:37 am
Thanks for checking that out, William. I appreciate the confirmation.
So, pretty much any reversion from 2k5 to 2000 has to have the following traits:
1) No use of 2k5 objects / code in the database to be reverted
2) The DBA needs plenty of time to waste on the revision
Ugly, time consuming and... well, just ugly.
Moral of the story is plan your upgrades well. Do Dev/Test/QC first, which will give you a chance to find the problems before they reach production, then do production last. And then you won't have issues with bringing a production DB down to a 2000 Test server.
November 15, 2007 at 9:22 am
you can trie using bcp, to copy only the data.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply