|
Home > Archive > microsoft.public.sqlserver.server > November 2002 > instmsdb.sql
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| John Fichera 2002-11-19, 3:23 pm |
| Hello,
This may seem like a hack job, but here it goes:
Due to problems upgrading a SQL 7.0 server to SQL 2000 on a NT4 SP6a server
(too detailed to mention here..went round and round with PSS before giving
up) I would like to upgrade a SQL Server 7.0 instance by installing SQL 2k
clean and then reattaching all user databases. The problem is, I know that
I cannot do this with msdb so I am going to lose my job , DTS, and maint
plan data unless I script and recreate.
My question is: Is it practical to restore / reattach a 7.0 msdb to a sql
2000 server and then execute the instmsdb.sql script from the \mssql\install
directory to "clean up" the 7.0 msdb? (actually I know this is possible
because I have done it on a test system, so I'm looking for feedback). Are
there any other scripts I would need to run? Am I nuts for even attempting
this?
Thanks,
John
ps: The problem I had with the upgrade is the same as this (link
below)...no resolution was posted and if you read the whole post, a guy from
PSS said that they are getting calls on this, but nobody will spend enough
time to troubleshoot as the "install clean" method seems to be more
efficient.
http://groups.google.com/groups?hl=...hreadm=26f801c1
8734%24129490e0%24b1e62ecf%40t
kmsftngxa04&rnum=24&prev=/groups%3Fq%3D%2522up
grade. sql%2522%2Berror%26start%3D20%
26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUT
F- 8%26selm%3D26f801c18734%252412
9490e0%2524b1e62ecf%2540tkmsft
ngxa04%26rnum%
3D24
| |
|
| John,
The best advice I can give is to script everything out and run scripts
against SS2K. This way you won't have to worry about matching old dbids
against new.
Otherwise you can try transfering data only from a restored copy of msbd
using DTS 'Copy SQL server objects' task.
Regards,
Tim Grdon-Jones MCDBA
http://www/pdbsolutions.co.uk
"John Fichera" <jfichera@[NOSPAM].nc.rr.com> wrote in message
news:#3iCr0AkCHA.3464@tkmsftngp08...
> Hello,
>
> This may seem like a hack job, but here it goes:
>
> Due to problems upgrading a SQL 7.0 server to SQL 2000 on a NT4 SP6a
server
> (too detailed to mention here..went round and round with PSS before giving
> up) I would like to upgrade a SQL Server 7.0 instance by installing SQL
2k
> clean and then reattaching all user databases. The problem is, I know
that
> I cannot do this with msdb so I am going to lose my job , DTS, and maint
> plan data unless I script and recreate.
>
> My question is: Is it practical to restore / reattach a 7.0 msdb to a sql
> 2000 server and then execute the instmsdb.sql script from the
\mssql\install
> directory to "clean up" the 7.0 msdb? (actually I know this is possible
> because I have done it on a test system, so I'm looking for feedback).
Are
> there any other scripts I would need to run? Am I nuts for even
attempting
> this?
>
> Thanks,
> John
>
> ps: The problem I had with the upgrade is the same as this (link
> below)...no resolution was posted and if you read the whole post, a guy
from
> PSS said that they are getting calls on this, but nobody will spend enough
> time to troubleshoot as the "install clean" method seems to be more
> efficient.
>
>
http://groups.google.com/groups?hl=...hreadm=26f801c1
>
8734%24129490e0%24b1e62ecf%40t
kmsftngxa04&rnum=24&prev=/ groups%3Fq%3D%2522up
>
grade. sql%2522%2Berror%26start%3D20%
26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUT
>
F- 8%26selm%3D26f801c18734%252412
9490e0%2524b1e62ecf%2540tkmsft
ngxa04%26rnum%
> 3D24
>
>
| |
| Tibor Karaszi 2002-11-20, 5:23 am |
| > My question is: Is it practical to restore / reattach a 7.0 msdb to a sql
> 2000 server and then execute the instmsdb.sql script from the \mssql\install
> directory to "clean up" the 7.0 msdb? (actually I know this is possible
> because I have done it on a test system, so I'm looking for feedback). Are
> there any other scripts I would need to run? Am I nuts for even attempting
> this?
I'm surprised that SQL Server let you restore a 7.0 system database. When I tried, I got error
message that you can't restore prior version of system databases. I can't even restore a prior
build of SQL2K msdb...
Anyhow, I wouldn't go this route of running instmsdb. The srcipt is not, AFAIK, written to
upgrade 7.0 msdb system tables structure (I might be wrong on this, of course). But even if it
is, I consider this an unsupported measure and you never know what will come back and hunt you
later. Go with the script option...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ublic.sqlserver
"John Fichera" <jfichera@[NOSPAM].nc.rr.com> wrote in message
news:#3iCr0AkCHA.3464@tkmsftngp08...
> Hello,
>
> This may seem like a hack job, but here it goes:
>
> Due to problems upgrading a SQL 7.0 server to SQL 2000 on a NT4 SP6a server
> (too detailed to mention here..went round and round with PSS before giving
> up) I would like to upgrade a SQL Server 7.0 instance by installing SQL 2k
> clean and then reattaching all user databases. The problem is, I know that
> I cannot do this with msdb so I am going to lose my job , DTS, and maint
> plan data unless I script and recreate.
>
> My question is: Is it practical to restore / reattach a 7.0 msdb to a sql
> 2000 server and then execute the instmsdb.sql script from the \mssql\install
> directory to "clean up" the 7.0 msdb? (actually I know this is possible
> because I have done it on a test system, so I'm looking for feedback). Are
> there any other scripts I would need to run? Am I nuts for even attempting
> this?
>
> Thanks,
> John
>
> ps: The problem I had with the upgrade is the same as this (link
> below)...no resolution was posted and if you read the whole post, a guy from
> PSS said that they are getting calls on this, but nobody will spend enough
> time to troubleshoot as the "install clean" method seems to be more
> efficient.
>
> http://groups.google.com/groups?hl=...hreadm=26f801c1
> 8734%24129490e0%24b1e62ecf%40t
kmsftngxa04&rnum=24&prev=/groups%3Fq%3D%2522up
> grade. sql%2522%2Berror%26start%3D20%
26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUT
> F- 8%26selm%3D26f801c18734%252412
9490e0%2524b1e62ecf%2540tkmsft
ngxa04%26rnum%
> 3D24
>
>
| |
| John Fichera 2002-11-20, 9:23 am |
| Thanks for the input! There is really not that many jobs / DTS packages so
I'm going with the script option...but I was looking for a little input.
I should clarify on the restore of msdb. I first restored as a new "user"
database (msdb2) which allowed for the system catalog to be auto upgraded.
I then backed msdb2 up to new file and then SQL allowed me to restore over
msdb...but immediately started getting errors because the additional msdb
functionality for SQL2K was not added.
| |
| Tibor Karaszi 2002-11-21, 9:23 am |
| Ah, OK. That makes sense now, how you managed to restore earlier version of msdb. I'm not
surprised that you got this error messages... :-)
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ublic.sqlserver
"John Fichera" <jfichera@[NOSPAM].nc.rr.com> wrote in message
news:eR6vjdKkCHA.1392@tkmsftngp12...
> Thanks for the input! There is really not that many jobs / DTS packages so
> I'm going with the script option...but I was looking for a little input.
>
> I should clarify on the restore of msdb. I first restored as a new "user"
> database (msdb2) which allowed for the system catalog to be auto upgraded.
> I then backed msdb2 up to new file and then SQL allowed me to restore over
> msdb...but immediately started getting errors because the additional msdb
> functionality for SQL2K was not added.
>
>
>
|
|
|
|
|