I've seen several postings here about copying/moving databases and I am
curious as to which method is considered to be the most reliable and/or
efficient.

DTS can transfer a database from one SQL Server to another - I have done
this and it worked fine. I have not used the sp_detachdb et al yet and, to
be honest, have not had a chance to read up on it. Backup/Restore are
basically just new commands for what used to be Dump/Load. But which method
is best? Does it depend on the situation?

--
Mike MacGregor
Principal Consultant
TygerCon Inc.
Tel: 519-579-5276
Fax: 519-579-7360
Cell: 416-568-4805

www.tygercon.com

Mike,

People will differ. For development data, I'll use DTS on small database
transfers but not large ones.

For production databases, I'll always make a backup, copy the backup file,
and then restore. The reason is that it seems the most cautious.

If the data files are extremely large and it's not production data, I've
used sp_detachdb/attachdb and then copied the files.

Ron
Ron Talmage
SQL Server MVP, MCSD, MCP

There are reasons for each technique... if all you're concerned about is
speed... then sp_attachdb and the related commands should always be faster.
If you've moving the entire data set, DTS would be the slowest option but
would provide lot's of custom flexibility. Backup/Restore is slower than
sp_attachdb but has the advantage of keeping the db "up" so users can
connect...

I'm not aware of any data integrity issues that could be caused by using
sp_attachdb compared to backup/restore for production data. Does anyone have
any interesting experiences on that topic?

--
Brian Moran

Director of Database Technology
CIBER Washington D.C.
MCSE, MCSD, SQL Server MVP
Windows NT Magazine Columnist
President, Capital Area
SQL Server Users Group

Mike,

I agree with all the contributors to this thread but I'd like to point out
one advantage of DTS over the other two methods: it will allow you to move
data from one server to another where the processor architecture/sort
order/character set are different.

Kieran

Thanks guys!

--
Mike MacGregor
Principal Consultant
TygerCon Inc.
Tel: 519-579-5276
Fax: 519-579-7360
Cell: 416-568-4805

www.tygercon.com