Moving the aspnetdb.mdf Database to a Production SQL Server
One of the least documented features in ASP.NET is the way to move the user access and profiles tables in aspnetdb.mdf to a production SQL Server. Although aspnetdb.mdf can be used in production, it is not recommended. This is done in 2 steps, first create the structure (tables, views and stored procedures) in the database, and second point the connection strings in the solution to that database.
The structure should be created automatically, to do so open the command line by opening the Run dialog box and enter cmd. Then change to the directory C:\Windows\Microsoft.NET\Framework\v2.0.50727. Note that the last directory’s name may be different, just make sure it’s v2.0, which signifies version 2.0 of the .NET Framework. In this directory run the executable called aspnet_regsql.exe. When run without arguments a wizard will start. Although I never had trouble with this tool before, I would still advice to backup the database before continuing the wizard, if its not empty. The following screens show the input pages of the wizard:
To let the solution use the database for user access and profiles, a hidden connection string called LocalSqlServer has to be removed and added again with the new settings. The following code example does that.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
<connectionStrings>
<!-- Previously defined connection strings will be here -->
<!-- LocalSqlServer is the connectionstring used by the asp.net -->
<!-- access and profile modules -->
<remove name="LocalSqlServer"></remove>
<add name="LocalSqlServer"
connectionString="Data Source=amgadhs;
Initial Catalog=testdb;
User ID=amgad;
Password=123"
providerName="System.Data.SqlClient">
</add>
</connectionStrings> |
Hi
I ran the wizard as as given in this page, but it created an empty database, instead of copying my existing database.
How do i copy my database to serever?
Thanks
Joyce
Hi Joyce, the wizard creates the structure (tables, views and stored procedures) needed to use the database for ASP.NET memberships. There are 3 ways you could copy the database. You could copy the database’s mdf and ldf files to the new location and attach it using SQL Server Management Studio, backup the database and restore it in the new location, or generate the SQL statements needed to create the structure and run it in the new location.
Hi Amgad Suliman ,
Its working, But it still have some problem like when i add roles in asp.net web application Admin web site it not affect my SQL Server DB. It Affect the mdf file is created in App_data,
Any Solution for that,
Thanks in advance
Hey Vinoth,
You should add the XML code above to your project’s web.config file. Under connectionstrings.
Please share the XML code.
thank you
Hi Imtiaz, I meant the XML code listed above in the post.
Everything works great, untill the part where i have to paste the code into my web.config file. After doing that:
500 – Internal server error.
There is a problem with the resource you are looking for, and it cannot be displayed.
A couple of tries later, same result.
Sorry for the double post, but after removing said code i get this error:
[SqlException (0x80131904): Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed.]
Mixx, the code above is just a connection string to use the database. You should make sure the wizard created the database and the required tables? And also make sure you are using the right SQL Server instance, database and user in the connection string?
Take a look at the parameters available for aspnet_regsql.exe.
aspnet_regsql.exe /?
You can specify a different database name, the server instance, etc. We develop lots of applications for different customers and having the ability to store membership information in separate databases for each application is a must.
This post was the most concise post on this subject!
You sir …. are awsome.
Rich