We've had a few articles on how you move databases, but it seems that there are always new people using SQL Server and they end up posting a question because they aren't sure how to do something.
The other day I ran across someone trying to copy their database to a new server. They'd tried to "Copy Database Wizard" and the Copy Objects" in DTS (SQL 2000), but weren't getting default. Likely that's a bug in those processes, which I believe are the same code.
I mentioned detaching and attaching your database, which the poster wasn't aware of. Surprised me, but I'm sure many people don't know how. I decide (another) blog on the topic couldn't hurt with some simple instructions.
- Detach your db in Enterprise Manager
- Copy the files (mdf/ldf/ndf) to a new server
- Attach the db in Enterprise Manager
- Deal with logins.
Now you have your data moved, all your code, defaults, rules, contraints, etc. Now logins need to be handled. There are a couple ways to do this.
1. If you care about passwords (keeping them the same), use sp_help_revlogin to script out the logins from the old server.
2. run sp_change_users_login to sync up the logins from #1 with your users, or to just create new logins on the new server if you didn't want to mess with the scripting.