magarity (4/18/2009)
1. Detach and copy the original mdf and lfd files. This works on my SQL Express between NTFS HDD and FAT32 USB. Is there something that prevents this with Standard or Enterprise?
No, but if you're trying to copy a production DB to a test environment, taking it offline for the duration of the copy may not be an option
2. Temporarily set up a trust between servers and copy all objects with data.
Fine if the source DB is dev, but not so much an option on a busy production DB. The load that the copy will impost may be unacceptable. If there's a downtime window where it can be done, ensure it's big enough.
3. Generate a script that includes all data. Edit the first couple of lines where the 'create database' commands are located to put the mdf and ldf files on the FAT32. Also change the logging to be simple or bulk instead of full logged. Then run the script with sqlcmd on the recipient machine.
Same as above. Fine for a small DB or a dev DB, may be an unacceptable load or take far too long on large, busy production servers
In summary, copying an entire database via the backup and restore method leaves a lot to be desired and several cases where problems can crop up.
True, but if it's a production DB that you're copying, there will always be a backup file available.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability