Best way to copy a sql db between machines?

SQL server 2005 developer version

I have about a dozen db's scattered on 3 machines and want to
consolidate all of them on my desktop with the data intact.

What's the easiest, best way to accomplish this?

Can I backup on one and restore on another?

Yes you can RESTORE from one machine to another. You cannot combine databases via RESTORE, though. You would have 12 databases on one machine.

|||No, I do not want to consolidate them , just copy them from one machine to another so the data is the same everywhere.

I tried the restore and it errs out because the pc name is different
from the pc name of the db I backed up.

Seems rather limiting.

I also tried the export/import with flat file delimited but it errs out as well. I double checked the table entry details and the db's are identical. The target db is empty of data.

These are small db's but not so small I want to renter the data.

|||

Check out one of the RESTORE command options - [ WITH MOVE ...]

Refer to Books Online, Topic: RESTORE

|||I got the restore to finally work by creating the dir that it wanted.

My db's were in
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

and sql server insisted upon :
C:\Program Files\Microsoft SQL Server\MSSQL\Data

Once I created that dir the restore worked just fine.

No where I could see to change the path for the restore either.
Pretty stupid if you ask me but that's what wizards are for.

Thanks for trying guys
|||A few days ago someone pinged me to do the same, this is what I sent him over:

-If you restore the files using the RESTORE DATABASE command, use with WITH MOVE options to place the files somewhere else on your computer. For more information about the WITH MOVE option, look in the BOL of SQL Server.

-Prefering the GUI to restore the files ? Then point to the backup device and navigate to the Options page within the restore dialog. Change the settings of Restore database files as (which actually means database files and logfiles) to point to the new directory. (It will contain by default the directories which were used during the backup process, by changing the directories within the dialog, it will create the script behind the scenes which uses the WITH MOVE option)

Jens k. Suessmeyer.

http://www.sqlserver005.de