SQL Server 2005: how to copy database from one server to another (manually/programmatically)

Question

I am developing an ASP.NET-app which uses an SQL2005-DB - and from time to time I need to copy the db from the dev-environment to test or production - or vice-versa. With SQL2000, I had written some batches to do that, but haven't had the time yet to find out how to do that under 2005, and did it manually. In SQL Mgmnt Studio, I would detach both dbs, then copy the MDF+LDF-Files from one server to the other and then re-attach the files. This has worked once or twice, but suddenly when I attach the db that was copied, it is in ReadOnly-Mode - which doesn't really help.

Why is that happening, am I doing something wrong - and how can I avoid that? And if someone has .BAT to do that, even better! ;)

Answer 1

Use the Copy Database Wizard to do this manually.

Start it by right clicking a database in Management Studio and selecting Tasks > Copy Database... from the popup menu.