Blog Post

Moving your database to a new server

,

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.

  1. Detach your db in Enterprise Manager
  2. Copy the files (mdf/ldf/ndf) to a new server
  3. Attach the db in Enterprise Manager
  4. 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.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating