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.

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+ database
s
is going to take some time...
"Uri Dimant" wrote:

> Andrew
> Have you tried to BACKUP all user databases and then RESTORE on SQL Serve
r
> 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+ databa
ses
> 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.|||> It's trivial to write a script to loop through sysdatabases to run a
> BACKUP command for each database.
And automating a restore is pretty trivial as well. The code in
http://www.karaszi.com/SQLServer/ut...all_in_file.asp can be a good
starting point.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tracy McKibben" <tracy.mckibben@.gmail.com> wrote in message
news:1172582161.712054.88030@.8g2000cwh.googlegroups.com...
> On Feb 27, 5:06 am, Andrew Hayes
> <AndrewHa...@.discussions.microsoft.com> wrote:
> 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 the
n
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 th
e
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 t
hen
> 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.

Copy database for testing on same SQL Server?

I have a live and functioning database on my lone SQL Server 2000. I would
like to create a copy of this database on the same server for when my 3rd
party provider releases updates (SQL scripts) I don't have to immediat3ly
apply them to my live database. If the script causes a failure I want to
discover it on the test database so my users can keep doing their thing.
Is this possible? I've been looking in the Help files as well as scanning
this newsgroup but have yet to discover the solution.
Thanks in advance for your help.
Just do a backup of your live database, and then restore it to your
server as a new name, and use the MOVE switch on the RESTORE....see
below
MOVE 'logical_file_name' TO 'operating_system_file_name'
Specifies that the given logical_file_name should be moved to
operating_system_file_name. By default, the logical_file_name is
restored to its original location. If the RESTORE statement is used to
copy a database to the same or different server, the MOVE option may be
needed to relocate the database files and to avoid collisions with
existing files. Each logical file in the database can be specified in
different MOVE statements.
from
http://msdn.microsoft.com/library/de...ra-rz_25rm.asp
Or you could script out everything on your database and DTS all the
data over to new db.
|||Thanks for the prompt reply and link. I'll give that a try!
"unc27932@.yahoo.com" wrote:

> Just do a backup of your live database, and then restore it to your
> server as a new name, and use the MOVE switch on the RESTORE....see
> below
> MOVE 'logical_file_name' TO 'operating_system_file_name'
> Specifies that the given logical_file_name should be moved to
> operating_system_file_name. By default, the logical_file_name is
> restored to its original location. If the RESTORE statement is used to
> copy a database to the same or different server, the MOVE option may be
> needed to relocate the database files and to avoid collisions with
> existing files. Each logical file in the database can be specified in
> different MOVE statements.
> from
> http://msdn.microsoft.com/library/de...ra-rz_25rm.asp
>
> Or you could script out everything on your database and DTS all the
> data over to new db.
>

Copy database for testing on same SQL Server?

I have a live and functioning database on my lone SQL Server 2000. I would
like to create a copy of this database on the same server for when my 3rd
party provider releases updates (SQL scripts) I don't have to immediat3ly
apply them to my live database. If the script causes a failure I want to
discover it on the test database so my users can keep doing their thing.
Is this possible? I've been looking in the Help files as well as scanning
this newsgroup but have yet to discover the solution.
Thanks in advance for your help.Just do a backup of your live database, and then restore it to your
server as a new name, and use the MOVE switch on the RESTORE....see
below
MOVE 'logical_file_name' TO 'operating_system_file_name'
Specifies that the given logical_file_name should be moved to
operating_system_file_name. By default, the logical_file_name is
restored to its original location. If the RESTORE statement is used to
copy a database to the same or different server, the MOVE option may be
needed to relocate the database files and to avoid collisions with
existing files. Each logical file in the database can be specified in
different MOVE statements.
from
http://msdn.microsoft.com/library/d...br />
25rm.asp
Or you could script out everything on your database and DTS all the
data over to new db.|||Thanks for the prompt reply and link. I'll give that a try!
"unc27932@.yahoo.com" wrote:

> Just do a backup of your live database, and then restore it to your
> server as a new name, and use the MOVE switch on the RESTORE....see
> below
> MOVE 'logical_file_name' TO 'operating_system_file_name'
> Specifies that the given logical_file_name should be moved to
> operating_system_file_name. By default, the logical_file_name is
> restored to its original location. If the RESTORE statement is used to
> copy a database to the same or different server, the MOVE option may be
> needed to relocate the database files and to avoid collisions with
> existing files. Each logical file in the database can be specified in
> different MOVE statements.
> from
> http://msdn.microsoft.com/library/d... />
z_25rm.asp
>
> Or you could script out everything on your database and DTS all the
> data over to new db.
>

Copy database backup files over the network

Hello!
We are using SQLSafe software to backup/compress our databases. We would
like to copy backup files (could be as big as 400GB compressed) to the
different computer within the same network. I know there are tools like
robocopy,xcopy etc. that are suitable for this job. I was wondering if
anybody can share his/her own experience in copying big backup files.
Ideally, we wouldn't like to saturate our network/cpu resources while
copying these files.
Thanks,
Igor
Hi
I find it best to have the backup software stripe the backup over multiple
files. This enables you to re-start a failed copy without having to re-copy
all 399GB
Also, add a dedicated NIC (Fiber, 1GBit or faster) onto the 2 servers, so
that they do not copy this file over the production network, affecting normal
queries.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"imarchenko" wrote:

> Hello!
> We are using SQLSafe software to backup/compress our databases. We would
> like to copy backup files (could be as big as 400GB compressed) to the
> different computer within the same network. I know there are tools like
> robocopy,xcopy etc. that are suitable for this job. I was wondering if
> anybody can share his/her own experience in copying big backup files.
> Ideally, we wouldn't like to saturate our network/cpu resources while
> copying these files.
> Thanks,
> Igor
>
>
|||Thanks Mike! We are striping backup over multiple files. We just want to
copy backup to the different computer, once it si completed, in the most
efficient way. Thanks for your tip regarding dedicated NIC.
Igor
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:E6EB48F8-4D11-48DD-B2A2-FA1F46334344@.microsoft.com...[vbcol=seagreen]
> Hi
> I find it best to have the backup software stripe the backup over multiple
> files. This enables you to re-start a failed copy without having to
> re-copy
> all 399GB
> Also, add a dedicated NIC (Fiber, 1GBit or faster) onto the 2 servers, so
> that they do not copy this file over the production network, affecting
> normal
> queries.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "imarchenko" wrote:

Copy database backup files over the network

Hello!
We are using SQLSafe software to backup/compress our databases. We would
like to copy backup files (could be as big as 400GB compressed) to the
different computer within the same network. I know there are tools like
robocopy,xcopy etc. that are suitable for this job. I was wondering if
anybody can share his/her own experience in copying big backup files.
Ideally, we wouldn't like to saturate our network/cpu resources while
copying these files.
Thanks,
IgorHi
I find it best to have the backup software stripe the backup over multiple
files. This enables you to re-start a failed copy without having to re-copy
all 399GB
Also, add a dedicated NIC (Fiber, 1GBit or faster) onto the 2 servers, so
that they do not copy this file over the production network, affecting norma
l
queries.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"imarchenko" wrote:

> Hello!
> We are using SQLSafe software to backup/compress our databases. We woul
d
> like to copy backup files (could be as big as 400GB compressed) to the
> different computer within the same network. I know there are tools like
> robocopy,xcopy etc. that are suitable for this job. I was wondering if
> anybody can share his/her own experience in copying big backup files.
> Ideally, we wouldn't like to saturate our network/cpu resources while
> copying these files.
> Thanks,
> Igor
>
>|||Thanks Mike! We are striping backup over multiple files. We just want to
copy backup to the different computer, once it si completed, in the most
efficient way. Thanks for your tip regarding dedicated NIC.
Igor
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:E6EB48F8-4D11-48DD-B2A2-FA1F46334344@.microsoft.com...[vbcol=seagreen]
> Hi
> I find it best to have the backup software stripe the backup over multiple
> files. This enables you to re-start a failed copy without having to
> re-copy
> all 399GB
> Also, add a dedicated NIC (Fiber, 1GBit or faster) onto the 2 servers, so
> that they do not copy this file over the production network, affecting
> normal
> queries.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "imarchenko" wrote:
>

Converting negative numbers to positive

Hi All
I have a table with column Col2 with negative and positive numbers. I would
like to query the col2 and make sure all the negative numbers are converted
to positive in the resultset.
eg
Tb1:
Col1 Col2
-- --
a -1
b -2
c 3
select col2 from tb1 where col1 ='a'
Resultset:
Col2
--
1
How can i do this? Thank you in advance.

5 years experience with SQL Server 2000 and SAP BW/SEM> I would
> like to query the col2 and make sure all the negative numbers are
> converted
> to positive in the resultset.
Try:
SELECT ABS(col2)
FROM tb1
WHERE col1 = 'a'
Hope this helps.
Dan Guzman
SQL Server MVP
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:ACE007EE-E918-4CB6-A59C-8318CAC4B059@.microsoft.com...
> Hi All
> I have a table with column Col2 with negative and positive numbers. I
> would
> like to query the col2 and make sure all the negative numbers are
> converted
> to positive in the resultset.
> eg
> Tb1:
> Col1 Col2
> -- --
> a -1
> b -2
> c 3
> select col2 from tb1 where col1 ='a'
> Resultset:
> Col2
> --
> 1
> How can i do this? Thank you in advance.
>
>
>
>
>
>
>
>
>
> 5 years experience with SQL Server 2000 and SAP BW/SEM|||On Wed, 24 May 2006 16:41:02 -0700, MittyKom wrote:

>Hi All
>I have a table with column Col2 with negative and positive numbers. I woul
d
>like to query the col2 and make sure all the negative numbers are converte
d
>to positive in the resultset.
Hi MittyKom,
SELECT ABS(col2)
FROM tbl
Hugo Kornelis, SQL Server MVP|||MittyKom wrote:
> 5 years experience with SQL Server 2000 and SAP BW/SEM
5 years of experience and you don't know how to convert a negative
number into a positive one?|||Perhaps the OP was mostly on the administration side and/or specialized in
some other SQL Server non-development role. One could guess that there
would be a specialized function for this task but it's sometimes difficult
to find answers in the Books Online when you don't know what to look for.
Hope this helps.
Dan Guzman
SQL Server MVP
"Chris Lim" <blackcap80@.hotmail.com> wrote in message
news:1148517832.138537.174900@.i40g2000cwc.googlegroups.com...
> MittyKom wrote:
> 5 years of experience and you don't know how to convert a negative
> number into a positive one?
>|||To Chris Lim
I am new to sql programming my friend. If you cant help keep your mouth
shhhhhhh. OK.... I have been working on sql server admin only. Enjoy your
day.
"Chris Lim" wrote:

> MittyKom wrote:
> 5 years of experience and you don't know how to convert a negative
> number into a positive one?
>|||Thank you Hugo and Dan. It worked.
"Dan Guzman" wrote:

> Try:
> SELECT ABS(col2)
> FROM tb1
> WHERE col1 = 'a'
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
> news:ACE007EE-E918-4CB6-A59C-8318CAC4B059@.microsoft.com...
>
>