Migrating ASP.NET Membership users from one database to another

15 Oct 2010

TheĀ Configuring a Website that Uses Application Services page does coverĀ Copying User Accounts from Development to Production but doesn't go into all that much detail. It really skips over the topic and starts talking about the ApplicationId and applicationName problem that a lot of people have had trouble with. This problem has also been covered by Scott Gu back in 2006. The part it skips over is that you can actually migrate your users and applications if you use the Database Publishing Wizard. But it doesn't tell you what to do if you don't want to use the Database Publishing Wizard. Here's what I did:

  1. In SQL Server Managment Studio right-click on your database and select "Tasks -> Generate Scripts...". The SQL Server Scripts Wizard dialog will appear.
  2. Click "Next", select the database with the Application Services, ASP.NET Membership tables installed and click "Next" again
  3. In the "Choose Script Options" dialog change the "Script Create" option to False and change "Script Data" to True. The click "Next".
  4. In the "Choose Object Types" dialog select just "Tables" and click "Next".
  5. In the "Choose Tables" dialog select all the tables with a "aspnet_" prefix. In my dialog this went from "aspnet_Applications" to "aspnet_WebEvent_Events". Click "Next".
  6. You can then generate the script which you will then run on the destination database to a variety of destinations including a new query window in SQL Server Management Studio or a .sql text file by clicking "Finish" (potentially twice).
The next steps simply involve running SQL Server Management Studio and connecting to you destination database and running the generated script against it. This obviously assumes that you have the Application Services installed already.

When I ran my generated SQL on the destination server I did get a few "Violation of PRIMARY KEY constraint" errors. To be honest I just ignored them since my application and users had been successfully inserted into the necessary tables and I could login using the expected credentials.

If you do have any questions about this please just post a comment.