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