October 20, 2002 at 4:12 pm
I have a dev Sql 7 database with many DTS scripts, Database Maintenance Plans, user databases, etc. I want to copy all of these items to a server with a clean install of Sql 2k (so an upgrade is involved as well). I found the Sql 2K “Database Copy Wizard” (DCW) works pretty well with logins and user databases but misses the mark entirely for DTS Scripts and Maintenance Plans (and who knows what else). I also found that running an in-place upgrade of the original Sql 7 server (after installing the patched setup.ins file in “x86\SETUP\”) seems to upgrade everything properly. But, when I want to copy the upgraded Sql2K databases to my clean Sql2k install, once again, the DCW won’t copy the DTS packages, etc. I know how to dump the DTS scripts to a file manually, etc, restore them on the new server, etc. …. but this getting ridiculous. Too many steps and I don’t know if I really have gotten everything I need. from the DCW What is the right way to do what I want?
TIA,
Bill
October 20, 2002 at 5:08 pm
Why not just detach msdb and move to the new server?
Andy
October 20, 2002 at 9:36 pm
Andy,
Please clarify what you mean. While I can detach the msdb database from the sql7 server I can't attach it to the new sql2k server (for that matter, I don't believe under sql 2K you can detach any of the system databases, including msdb).
Bill
October 21, 2002 at 1:32 am
Found more info:
Upgrading in-place from sql 7 to sql 2k may fail (it did for me) with "error running script messages.sql(1). See link
http://support.microsoft.com/default.aspx?scid=kb;DE;q300676
Here's another link that comes clean on moving databases -- not hard but not a walk in the park either, especially under Sql 2k with the system databses.
http://support.microsoft.com/default.aspx?scid=KB;EN-US;q224071&
Bill
October 21, 2002 at 4:00 am
sqldts.com has a page that details the options
http://www.sqldts.com/default.aspx?6,105,204,0,1
You could just backup and restore msdb, this should copy all jobs, maintenance plans and dts packages. This might be a problem if you are using multi server admin (TSX and MSX servers)
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
October 21, 2002 at 4:20 am
As for backup and resotre and sp_detach_db and sp_attach_db SQL 7 to 2000. System databases master, model, msdb and distribution (if you have replciation) are not intended to be transfered in this manner. They can and will potentially fail to work if done so.
KB Article http://support.microsoft.com/default.aspx?scid=kb;en-us;Q321824
quote:
NOTE: The SQL Server 7.0 system databases are not compatible with SQL Server 2000. Do not try to back up or restore, or to attach and detach procedures on the SQL Server 7.0 master, model, msdb or distribution databases to SQL Server 2000.
There is also an article on problems with msdb upgrade between 7 and 200 failing which this article touches on.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Edited by - antares686 on 10/21/2002 04:23:43 AM
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply