What is the best way to copy a database

[David Thielen]

From Sql Server 2000 on one system to Sql Server 2005 on another?

I've just spent an hour trying and no matter what approach and what
settings - it fails. It would have been faster if an hour ago I had
just started reading the schema of the source and typed it into the
destination.

I'm getting everything in the event log from null objects to shell
won't run to user already exists (but that user does not exist).

Any suggestions?

thanks - dave

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[Cowboy \(Gregory A. Beamer\)]

Detach file and copy to server.

Other option. Backup database and restore on other server.

Other option. Download the Database Publishing Wizard and do data and schema
for entire database.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

Subscribe to my blog
http://feeds.feedburner.com/GregoryBeamer#

or just read it:
http://feeds.feedburner.com/GregoryBeamer

********************************************
| Think outside the box! |
********************************************

 

[\Ji Zhou [MSFT]\]

Hello Dave,

I have found this MSDN document
http://msdn.microsoft.com/en-us/library/ms188664.aspx. I quote the
following from it

"You can use the Copy Database Wizard to upgrade from a SQL Server 2000
database to a SQL Server 2005 or later database.

You also can simplify the administration and maintenance of your databases
by integrating multiple instances of SQL Server 2000 into a single instance
of SQL Server 2005 or later or into several named instances on a single
computer.

When using the detach-and-attach method of the Copy Database Wizard to
upgrade a database, make sure that no applications or services are trying
to access the database. Do not use read-only mode; doing so results in an
error. You can rename the database during this operation.

After upgrading databases from an earlier version of SQL Server, run
sp_updatestats against the database on the destination server to update
statistics and ensure optimal performance of the copied database."

Hope this helps! Please let me know if you are still suffering from the
issue, thus I will do my best to provide future help.

Have a nice weekend!

Best regards,
Ji Zhou ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://support.microsoft.com/select/default.aspx?target=assistance&ln=en-us.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[David Thielen]

THANK YOU!!!!!!!!!!!!!!

Much easier and unliek COpy Database, it works!!!

thanks - dave

Detach file and copy to server.

Other option. Backup database and restore on other server.

Other option. Download the Database Publishing Wizard and do data and schema
for entire database.

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[Hillbilly]

Which "it?"

THANK YOU!!!!!!!!!!!!!!

Much easier and unliek COpy Database, it works!!!

thanks - dave





david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[David Thielen]

Sorry - I was typing fast. The backup/restore approach worked fine.

The Copy Database wizard was a giant time suck that never worked. So I
would suggest avoiding it.

thanks - dave

Which "it?"

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[Cowboy \(Gregory A. Beamer\)]

Any of the tools to copy databases by reading schema and creating DDL and
DML work slow. This is largely from the large amount of lines of code they
create. The Database Publishing wizard will create a file rather quickly,
but running it for a huge database is a big waste of time. It is great for
creating seed database scripts, howvever.

Backup and restore, in general, is the easiest option.
Copy is fine for initial deployment.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

Subscribe to my blog
http://feeds.feedburner.com/GregoryBeamer#

or just read it:
http://feeds.feedburner.com/GregoryBeamer

********************************************
| Think outside the box! |
********************************************