Wednesday, July 11, 2007

Move a database between servers - WITH the logins

Quite a few times I have been asked if moving a database from one server to another would be any problem.

Generally this is not a problem - although you can save yourself some trouble by moving the logins for the database first.
The How-To is:
1) Script the logins for the database on the source server by using the scripts listed below (Originally posted by Microsoft)
Remember to filter the logins the script generates - it takes every login on the source server and you generally only want those with reference to the database being moved.
2) After filtering the logins from the source server apply the needed logins on the destination server before attaching the database.
3) Detach the database on the source server, move it, attach it on the destination server
The detach/attach procedure can most easily be done from Management Studio by right-clicking the database name in the object explorer -> Select Task -> Select Detach. On the destination server right click the folder Databases -> Select Attach.
The scripts that script the loginsThe scripts is originally posted by Microsoft. They come in two variants - one for SQL 2000 and one for SQL 2005.
Both scripts consist of the same. 2 stored procedures - sp_hexadecimal, and sp_help_revlogin. sp_help_revlogin genereates a new script in the result windows with one line for each login on the server. Copy the text result from the result window, and filter it before applying it on the destination server.

No comments: