Transfer asp.net membership records of one site from one sql server to another

Question

I need to transfer the asp.net membership data of one website from a sql server database that has entries for several sites to another sql server.

I was looking for some existing/known methods of doing this ? I tried the Web Deploy utility but I could not find any methods of transferring the database records.

Any help would be much appreciated.

Thanks.

Answer 1

The ASP.NET membership database is just a "normal" SQL Server database, so the question is actually "how to transfer a SQL Server database from one server to another, with just a part of the data it contains".

1) Since the database on the old server contains membership information of more than one site, you have to know how to find the data of "your" site (the one you want to transfer) in the database.

I found information about the schema of the ASP.NET Membership database in this tutorial on the ASP.NET site.
Scroll down to "Step 3: A Look at the Schema's Core Tables".
The key information is this, especially the sentences that I made bold:

The aspnet_Applications table is what defines these partitions. Each application that uses the database to store user account information is represented by a row in this table. The aspnet_Applications table has four columns: ApplicationId, ApplicationName, LoweredApplicationName, and Description. ApplicationId is of type uniqueidentifier and is the table's primary key; ApplicationName provides a unique human-friendly name for each application.

The other Membership- and Role-related tables link back to the ApplicationId field in aspnet_Applications. For example, the aspnet_Users table, which contains a record for each user account, has an ApplicationId foreign key field; ditto for the aspnet_Roles table. The ApplicationId field in these tables specifies the application partition the user account or role belongs to.

In short: you have to find the site that you want to transfer in the aspnet_Applications table, take the ID from the ApplicationId column and find all rows with the same ApplicationId in all the other tables.

2) Now that you know how to find your site's data in the database, you can move the data to the new server. This is nothing ASP.NET specific, it's just plain SQL Server stuff - you just need to create the same database on the new server, but only with the data for your site.

I can think of two ways to do this:

Way 1:

1. Backup the whole database on the old server
2. Restore this backup on the new server
3. Now you have the complete database on the new server, but it contains data for all sites, not just the one you want
   --> you have to delete the data for all the other sites from the database on the new server.
   The easiest way to do this is with SQL - just execute this for every table in the database:
   delete from aspnet_Users where ApplicationID <> 'X'
   (where X is the ApplicationID of your site from the aspnet_Applications table)

Way 2:

1. Create a new empty Membership database on the new server
2. Set up the old SQL Server as a Linked Server in the new SQL Server
   (this enables you to run SQL on the new server that selects data from the old server)
3. Copy the data for your site from each table in the old server to the new server. Just execute the following SQL for each table:
   insert into aspnet_Users
select * from [Name_of_old_server].[Name_of_old_database].dbo.aspnet_Users
where ApplicationID = 'X'
   (where X is the ApplicationID of your site from the aspnet_Applications table)
4. Now you can delete the linked server (that you created in step 2) again - it was just for the purpose of transferring data one time only.