Migrate from Sql Server 2005 to Sql Server 2000

We are moving from 2000 to 2005 .And I have to come up with an escape
route if after a week or two it is decided performance/other issues
are worse.
.SSIS transfer sql server objects does not cut it for some unknown
reason with one error after the other...
Thanks for your time
massaJust detach the database from the SQL 2000 server and then attach it to the
SQL 2005 server.
Do NOT change the database compatibility level until you are certain that
you will not move it back to the SQL 2000 server.
After attaching, update statistics and re-index.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Massa Batheli" <mngong@.gmail.com> wrote in message
news:1158271663.636547.75900@.b28g2000cwb.googlegroups.com...
>
> We are moving from 2000 to 2005 .And I have to come up with an escape
> route if after a week or two it is decided performance/other issues
> are worse.
> .SSIS transfer sql server objects does not cut it for some unknown
> reason with one error after the other...
>
> Thanks for your time
> massa
>|||The database will be upgraded to the SQL 2005 on-disk format when attached
to the SQL 2005 instance. Consequently, it can't be attached back to the
SQL 2000 regardless of the database compatibility level.
Hope this helps.
Dan Guzman
SQL Server MVP
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:O$edDtE2GHA.3372@.TK2MSFTNGP04.phx.gbl...
> Just detach the database from the SQL 2000 server and then attach it to
> the SQL 2005 server.
> Do NOT change the database compatibility level until you are certain that
> you will not move it back to the SQL 2000 server.
> After attaching, update statistics and re-index.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Massa Batheli" <mngong@.gmail.com> wrote in message
> news:1158271663.636547.75900@.b28g2000cwb.googlegroups.com...
>>
>> We are moving from 2000 to 2005 .And I have to come up with an escape
>> route if after a week or two it is decided performance/other issues
>> are worse.
>> .SSIS transfer sql server objects does not cut it for some unknown
>> reason with one error after the other...
>>
>> Thanks for your time
>> massa
>|||> .SSIS transfer sql server objects does not cut it for some unknown
> reason with one error after the other...
That's the way to go back to SQL 2000. There should be a way to get more
details about the errors. Are you logging errors in your package?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Massa Batheli" <mngong@.gmail.com> wrote in message
news:1158271663.636547.75900@.b28g2000cwb.googlegroups.com...
>
> We are moving from 2000 to 2005 .And I have to come up with an escape
> route if after a week or two it is decided performance/other issues
> are worse.
> .SSIS transfer sql server objects does not cut it for some unknown
> reason with one error after the other...
>
> Thanks for your time
> massa
>|||Dan Guzman wrote:
> > .SSIS transfer sql server objects does not cut it for some unknown
> > reason with one error after the other...
> That's the way to go back to SQL 2000. There should be a way to get more
> details about the errors. Are you logging errors in your package?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Massa Batheli" <mngong@.gmail.com> wrote in message
> news:1158271663.636547.75900@.b28g2000cwb.googlegroups.com...
> >
> >
> > We are moving from 2000 to 2005 .And I have to come up with an escape
> > route if after a week or two it is decided performance/other issues
> > are worse.
> > .SSIS transfer sql server objects does not cut it for some unknown
> > reason with one error after the other...
> >
> >
> > Thanks for your time
> > massa
> >
let me attach the error if do not mind
An OLE DB record is available. Source: "Microsoft SQL Native Client"
Hresult: 0x80040E37 Description: "Invalid object name
'dbo.qs_globals'.".
helpFile=dtsmsg.rll helpContext=0
idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".
Task failed: Transfer SQL Server Objects Task
SSIS package "Package.dtsx" finished: Success.
It seems to have trouble recreating any tables not owned by dbo. when
that is deselected from the list it then has trouble copying any
table...
Thanks again for your time|||> It seems to have trouble recreating any tables not owned by dbo. when
> that is deselected from the list it then has trouble copying any
> table...
You might try creating SQL 2000 database users for non-dbo SQL 2005 schema
before the transfer. I'm not sure how the Transfer SQL Server Objects Task
handles schema-->owner translation when you copy from SQL 2005 to 2000.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Massa Batheli" <mngong@.gmail.com> wrote in message
news:1158320760.263256.231650@.h48g2000cwc.googlegroups.com...
> Dan Guzman wrote:
>> > .SSIS transfer sql server objects does not cut it for some unknown
>> > reason with one error after the other...
>> That's the way to go back to SQL 2000. There should be a way to get more
>> details about the errors. Are you logging errors in your package?
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Massa Batheli" <mngong@.gmail.com> wrote in message
>> news:1158271663.636547.75900@.b28g2000cwb.googlegroups.com...
>> >
>> >
>> > We are moving from 2000 to 2005 .And I have to come up with an escape
>> > route if after a week or two it is decided performance/other issues
>> > are worse.
>> > .SSIS transfer sql server objects does not cut it for some unknown
>> > reason with one error after the other...
>> >
>> >
>> > Thanks for your time
>> > massa
>> >
> let me attach the error if do not mind
> An OLE DB record is available. Source: "Microsoft SQL Native Client"
> Hresult: 0x80040E37 Description: "Invalid object name
> 'dbo.qs_globals'.".
> helpFile=dtsmsg.rll helpContext=0
> idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".
> Task failed: Transfer SQL Server Objects Task
> SSIS package "Package.dtsx" finished: Success.
> It seems to have trouble recreating any tables not owned by dbo. when
> that is deselected from the list it then has trouble copying any
> table...
>
> Thanks again for your time
>|||Dan Guzman wrote:
> > It seems to have trouble recreating any tables not owned by dbo. when
> > that is deselected from the list it then has trouble copying any
> > table...
> You might try creating SQL 2000 database users for non-dbo SQL 2005 schema
> before the transfer. I'm not sure how the Transfer SQL Server Objects Task
> handles schema-->owner translation when you copy from SQL 2005 to 2000.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Massa Batheli" <mngong@.gmail.com> wrote in message
> news:1158320760.263256.231650@.h48g2000cwc.googlegroups.com...
> >
> > Dan Guzman wrote:
> >> > .SSIS transfer sql server objects does not cut it for some unknown
> >> > reason with one error after the other...
> >>
> >> That's the way to go back to SQL 2000. There should be a way to get more
> >> details about the errors. Are you logging errors in your package?
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "Massa Batheli" <mngong@.gmail.com> wrote in message
> >> news:1158271663.636547.75900@.b28g2000cwb.googlegroups.com...
> >> >
> >> >
> >> > We are moving from 2000 to 2005 .And I have to come up with an escape
> >> > route if after a week or two it is decided performance/other issues
> >> > are worse.
> >> > .SSIS transfer sql server objects does not cut it for some unknown
> >> > reason with one error after the other...
> >> >
> >> >
> >> > Thanks for your time
> >> > massa
> >> >
> >
> > let me attach the error if do not mind
> >
> > An OLE DB record is available. Source: "Microsoft SQL Native Client"
> > Hresult: 0x80040E37 Description: "Invalid object name
> > 'dbo.qs_globals'.".
> > helpFile=dtsmsg.rll helpContext=0
> > idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".
> > Task failed: Transfer SQL Server Objects Task
> > SSIS package "Package.dtsx" finished: Success.
> > It seems to have trouble recreating any tables not owned by dbo. when
> > that is deselected from the list it then has trouble copying any
> > table...
> >
> >
That was quicker than any reply I have ever had (Thanks)
I will continue to persue that path though we are migrating tomorrow .
Are you aware of any other feasible method...|||> Are you aware of any other feasible method...
Not really, but it should be reasonably easy to fall back by keeping the
original SQL 2000 database around. Script out the original database foreign
keys and truncate tables. If you make schema changes, apply the scripts to
both SQL 2000 and SQL 2005 database to keep them in sync. To revert back,
copy data back and recreate foreign keys via script.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Massa Batheli" <mngong@.gmail.com> wrote in message
news:1158324606.863195.166810@.m73g2000cwd.googlegroups.com...
> Dan Guzman wrote:
>> > It seems to have trouble recreating any tables not owned by dbo. when
>> > that is deselected from the list it then has trouble copying any
>> > table...
>> You might try creating SQL 2000 database users for non-dbo SQL 2005
>> schema
>> before the transfer. I'm not sure how the Transfer SQL Server Objects
>> Task
>> handles schema-->owner translation when you copy from SQL 2005 to 2000.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Massa Batheli" <mngong@.gmail.com> wrote in message
>> news:1158320760.263256.231650@.h48g2000cwc.googlegroups.com...
>> >
>> > Dan Guzman wrote:
>> >> > .SSIS transfer sql server objects does not cut it for some
>> >> > unknown
>> >> > reason with one error after the other...
>> >>
>> >> That's the way to go back to SQL 2000. There should be a way to get
>> >> more
>> >> details about the errors. Are you logging errors in your package?
>> >>
>> >> --
>> >> Hope this helps.
>> >>
>> >> Dan Guzman
>> >> SQL Server MVP
>> >>
>> >> "Massa Batheli" <mngong@.gmail.com> wrote in message
>> >> news:1158271663.636547.75900@.b28g2000cwb.googlegroups.com...
>> >> >
>> >> >
>> >> > We are moving from 2000 to 2005 .And I have to come up with an
>> >> > escape
>> >> > route if after a week or two it is decided performance/other issues
>> >> > are worse.
>> >> > .SSIS transfer sql server objects does not cut it for some
>> >> > unknown
>> >> > reason with one error after the other...
>> >> >
>> >> >
>> >> > Thanks for your time
>> >> > massa
>> >> >
>> >
>> > let me attach the error if do not mind
>> >
>> > An OLE DB record is available. Source: "Microsoft SQL Native Client"
>> > Hresult: 0x80040E37 Description: "Invalid object name
>> > 'dbo.qs_globals'.".
>> > helpFile=dtsmsg.rll helpContext=0
>> > idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".
>> > Task failed: Transfer SQL Server Objects Task
>> > SSIS package "Package.dtsx" finished: Success.
>> > It seems to have trouble recreating any tables not owned by dbo. when
>> > that is deselected from the list it then has trouble copying any
>> > table...
>> >
>> >
> That was quicker than any reply I have ever had (Thanks)
> I will continue to persue that path though we are migrating tomorrow .
> Are you aware of any other feasible method...
>