Copy Database From 2000 To 2005 Failing To Create View

I have 180+ databases on a production SQL Server 2000 system that I would
like to copy over to a new SQL Server 2005 system for testing prior to
rolling out a new database server.
Unfortunately, the Copy Database wizard using SQL MO fails. The event log
shows that it could not create a view because of an "invalid object name".
This, I assume, is because the Dependencies are not set so the SQLISPackage
doesn't know which order to create the objects. That is, the databases use
views that did not specify SCHEMABINDING when they were created, so when
using View Dependencies, the view only shows itself, even if the view query
makes use of other views/tables.
Is there a way to stop this check from occuring, so that the view is created
regardless of whether a view dependency has been fulfilled?
Andrew
Have you tried to BACKUP all user databases and then RESTORE on SQL Server
2005?
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:F323D970-8FC8-4CBB-8418-3137CD980046@.microsoft.com...
>I have 180+ databases on a production SQL Server 2000 system that I would
> like to copy over to a new SQL Server 2005 system for testing prior to
> rolling out a new database server.
> Unfortunately, the Copy Database wizard using SQL MO fails. The event log
> shows that it could not create a view because of an "invalid object name".
> This, I assume, is because the Dependencies are not set so the
> SQLISPackage
> doesn't know which order to create the objects. That is, the databases use
> views that did not specify SCHEMABINDING when they were created, so when
> using View Dependencies, the view only shows itself, even if the view
> query
> makes use of other views/tables.
> Is there a way to stop this check from occuring, so that the view is
> created
> regardless of whether a view dependency has been fulfilled?
|||I was hoping to avoid that. Performing a backup and restore of 180+ databases
is going to take some time...
"Uri Dimant" wrote:

> Andrew
> Have you tried to BACKUP all user databases and then RESTORE on SQL Server
> 2005?
>
|||Andrew
Well , I don't think so , especially comparing it with copy wizard ,but try
detach\attach , for more details please refer to the BOL
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:152BB668-35A7-4698-8379-438DB8DA3157@.microsoft.com...
>I was hoping to avoid that. Performing a backup and restore of 180+
>databases
> is going to take some time...
> "Uri Dimant" wrote:
>
|||On Feb 27, 5:06 am, Andrew Hayes
<AndrewHa...@.discussions.microsoft.com> wrote:
> I was hoping to avoid that. Performing a backup and restore of 180+ databases
> is going to take some time...
>
Backup/restore will be MUCH faster than the Copy Database wizard.
It's trivial to write a script to loop through sysdatabases to run a
BACKUP command for each database.
|||Uri - Can't use Detach/Attach since it's a production server in use 24/7. Or
if I do, it means spending several weeks doing it so I can minimise the
impact on our customers.
Tracy - OK. So I write a script to backup every database from the source
server, copy each backup file over the WAN to the destination server and then
perform a restore, changing the file locations for each database MDF. That
would probably take several days.
In either case, it doesn't resolve the issue that the Copy Database Wizard
fails to create views because it's performing a logic/object check during the
copy. Why can't I just turn that off?
|||On Feb 27, 7:36 am, Andrew Hayes
<AndrewHa...@.discussions.microsoft.com> wrote:
> Uri - Can't use Detach/Attach since it's a production server in use 24/7. Or
> if I do, it means spending several weeks doing it so I can minimise the
> impact on our customers.
> Tracy - OK. So I write a script to backup every database from the source
> server, copy each backup file over the WAN to the destination server and then
> perform a restore, changing the file locations for each database MDF. That
> would probably take several days.
> In either case, it doesn't resolve the issue that the Copy Database Wizard
> fails to create views because it's performing a logic/object check during the
> copy. Why can't I just turn that off?
How long do you think it's going to take the copy wizard to complete?
The first thing that gizmo does is script all of the objects in your
source database, run those scripts on the destination database (to
create the objects), and then imports/exports the data from the source
to the destination. That is going to take twice as long as going the
backup/restore route. Trust me, backup/restore is the way to go, you
can AUTOMATE the whole thing, and you have FULL CONTROL over what's
happening.
If you insist on using the wizard, I'm afraid I can't help you out. I
don't use it, it's just too klunky to work with.