Deploying the ASPNETDB.MDF to a Remote SQL Server Database

Topic: Using Publish to Provider to generate scripts to create/move/copy .MDF databases to your remote SQL Server database.

When using Visual Studio to create your web projects, you may choose to use the ASP.NET Configuration tool to create your users and roles. This will create a set of tables which are then placed in a local ASPNETDB.MDF SQL Server express database. (These same steps may be used to copy any MDF database to your remote SQL Server database.)

Once you've done everything you need to do locally and you're ready to deploy this to your production database server, how do you do that?

Here's what I did. Maybe there's a slicker, quicker way. But this is free and fairly simple to me.

In this example, I am using Visual Studio 2008 and deploying to a SQL Server 2008 Web Edition database.

From Visual Studio, in your Server Explorer tab, you'll notice all of the tables created by default. As well, there are Views, Stored Procedures, etc.

First, we want to create a SQL script to recreate all of those objects. So right click the database and select Publish To Provider:

This will bring up the Database Publishing Wizard. Select the button to continue.

You will be presented with a list of your databases and asked to select your database. There is a checkbox to Script all objects in the database. Select it. Select Next to continue.

Now you are given the option to script to a file. It presents a default path, I changed mine.

In the next window you will be offered several options. In my case, I needed to change the target database to SQL Server 2008. Select the target you need, then Select to continue. As per Chris Rivera's comment below this blog, if 2008 doesn't show as an option, make sure you download and install SP1. See my post on installing SP1.

Next you may review the options selected.

Click Finish to complete the Wizard. You'll see the progress.

Now you have a fancy script generated for you. This script will select, alter, drop, update, insert, etc. etc., all needed values.

Now to get this to your database server. Mine happens to be on the same network as my webserver, but not on the same network as my development box, so I created a folder within my project and placed the script in there. When I updated my project, this sql script was then copied to my web server. From my database server, I then copied it via the network to my SQL Server 2008 Web Edition desktop.

I opened my Microsoft SQL Server Management Studio and created my new blank database. Just right click Databases, and select New Database. Give it a name and select OK.

Then with this new database selected, click the sql file on your desktop and it will open up in your screen. Again, with the new database selected, right click in the script and select "Execute." This will execute the script on the selected database and create all the objects needed.

Voila! You are done! You now have a full-blown SQL Server 2008 database on your production server!

Now, assuming you have created other MDF's for other projects that have nothing to do with the database created by Microsoft, you may use this same method to create your scripts. Also, this same functionality is in Visual Web Developer Express Edition.

May your dreams be in ASP.net!

Nannette

 

 

26 Comments

  • sounds great except I keep getting a timeout error when it gets to step 3.

  • great help thanks.

  • this is wonder, it absolutely solved my problem. plz keep up the good work.

  • After trying this procedure everything worked to a "T" until I got the "Select the new database and chick on the Script File and click execute! It executes and completes OK, but I hav eno idea where it ended up putting the tables it should have generated. The ASPNETDB still is a skeleton database with no tables from the \sqlexpress AspnetDB on the development system. ????

  • Tom,
    Did you forget to create your new blank database first?

    From blog:

    I opened my Microsoft SQL Server Management Studio and created my new blank database. Just right click Databases, and select New Database. Give it a name and select OK.

    Then with this new database selected, click the sql file on your desktop and it will open up in your screen. Again, with the new database selected, right click in the script and select "Execute." This will execute the script on the selected database and create all the objects needed.


  • Tom,

    On your development system, the place you want the database to be...

    you need to copy the script to there...

    create your blank database...

    and execute the script there...

    Nannette

  • Your instructions in the last paragraph was misleading. Here's what I did to get it to work.
    Double click on the .sql script file you created. This brings up the SQL Management Server asking for a DB Connection. Respond with your SQL Server. The script file then appears in the right panel, left panel is blank. On the Toolbar, select where the drop down listbox appeaars, your blank database you created earlier. Then right click on the script in the right panel and select "Execute". It works fine, the instructions at the end just confused me. It doesn't work if you start the SQL Management Server first and go through the steps. Thanks, for the most part this was a very helpful piece of information!

  • Tom,

    Are you executing your script locally? And not on a remote sql server database? It sounds like that's the difference.

    If so, that info is definitely good to know too! thanks!

    Nannette

  • quote
    "Also, this same functionality is in Visual Web Developer Express Edition."

    Not in VWD 2008 it isn't!

  • Thank you for providing this info. Your instructions were very helpful. I published the ASPNETDB.mdf database to my remote server but I am still having issues trying to login into my site. I have some pages that requires a user to login in order to view. I created the user's, roles and memberships using ASP.NET Configuration utility in VS 2005. I precompiled the site in VS 2005. Everything works fine locally but when I try to login to the published website I get an error 26 something about an instance not being found. Is there something I am missing. Do I need to setup some type of setting in the webconfig file?

  • Given that my databases are held on a shared server, I cannot have one of them named ASPNETDB.MDF.  So, I have to use a different name for it.  Let's say that I named it myaspnetdb.mdf.  When going from local to remote server by the steps above, does it recognize that the original db name is different from the target and adjust accordingly?  The above steps in this article worked fine for me, however, my log-ins still only work locally, not on my remote server.  Are the login controls still looking for a db named ASPNETDB.MDF and not my renamed one?  How can I get it to point to the renamed one?  Thanks!

  • Hi
    Great tutorial, and did it ok.
    Now I want to use the structures to manage more than one website application. How do I get website A to only view the security data for itself - and not get mixed up with websiteB ?
    How is the applicationID passed between the site and the database ?
    Thanks
    TM
    UK

  • Excelent Post!!
    It helped me so much!!!! It's exactly what I was looking for.
    Thanks!!

  • Thanks for sharing this link, but unfortunately it seems to be offline... Does anybody have a mirror or another source? Please reply to my message if you do!

    I would appreciate if a staff member here at weblogs.asp.net could repost it.

    Thanks,
    Alex

  • Hey,
    I have a message for the webmaster/admin here at weblogs.asp.net.
    Can I use part of the information from this post  right above if I provide a link back to your site?
    Thanks,
    Harry

  • Harry,

    Yes, you may use my content as long as I receive credit with a link back.

    Thanks!
    Nannette

  • Hey,

    Great post. It helped me a lot. but how to point the login control to look for this new database. its still looking for old aspnetdb.mdf file.

    Thanks!

  • Nitin,
    Your connection string will be in your web.config file. You need to change that to point to your server by IP address and database name, etc.
    Nannette

  • Man! read your post i can say that you are the best.Can you give me some more information
    Thanks

  • to those having issues making it connect to your database after you have deployed it, you need to do the following in your web config:
    <remove name="LocalSqlServer"/>  
    <add name="LocalSqlServer" connectionString="Connection String to the NEW Database" providerName="System.Data.SqlClient" />
    THEN it will work... took me like 2 hours to find this, as it is a pretty important step in an otherwise phenomenal post by the blogger. I ran the script on an existing database and it worked just fine so it's possible to keep the names unique based on the database being used.  This is pretty awesome!

  • Very helpful.

    I like all your blogs on ASP.

    Thanks a lot!!

  • Thanks a lot.  I've been looking forever to find out how to deploy the aspnetdb.  I followed your instructions and was able to deploy.  I still can't login to the database at my new hosting company.  But, at least I finally deployed aspnetdb.

    Thanks again.

  • I have created the ASPNETDB.MDF but dont know to proceed further and connection string issues are coming...

  • Hi,

    Thanks, Nannette!

    I followed the steps and could deploy the aspnetbd database to the remote server. However, the login control still could not work, giving this error:

    'The 'System.Web.Security.SqlRoleProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version......'

    I finally solved the problem by populating the aspnet_SchemaVersions table with values like this:

    Feature CompatibleSchemaVersion IsCurrentVersion
    common 1 1
    health monitoring 1 1
    membership 1 1
    personalization 1 1
    profile 1 1
    role manager 1 1

  • OK, I got to the point of the "execute" now where does SQL Server Management save to so I can retrieve it?

  • Did you forget to create your new blank database first?

    From blog:

    I opened my Microsoft SQL Server Management Studio and created my new blank database. Just right click Databases, and select New Database. Give it a name and select OK.

    Then with this new database selected, click the sql file on your desktop and it will open up in your screen. Again, with the new database selected, right click in the script and select "Execute." This will execute the script on the selected database and create all the objects needed.

Comments have been disabled for this content.