JohnSQLServerCentral (5/18/2010)
We wrote a report in our application which scripts all the table's data, and then the client uploads the resulting ZIP file it to us. This solution continues to work ok for us, but it frequently takes 3-4 hours to create the script. We know a .BAK file takes a fraction of the time and would be a quicker solution and less effort for the end-users.
Personally, when I have to copy a database, I always use the backup...restore approach. Not only does the script approach usually take a lot longer, but it also misses things (depending on your script) like identity values, indexes, free space, etc. The backup catches all this. The backup itself could be automated, but please make sure you include the copy_only option. If you have to do a point-in-time recovery and don't have the full backup available, you're out of luck. The copy_only option says to not mark the transaction log entries as having been backed up, so running the backup won't interfere with any later recovery.