by Tim
10. November 2011 18:57
I'm into the finishing stages of my new music site and remote deployment is just around the corner. I've been working with two databases, one of which is automatically created by the ASP.NET SQL Server registration tool (Aspnet_regsql.exe). The registration tool is used to create a Microsoft SQL Server database (ASPNETDB.mdf in this case) that is used by the SQL Server providers in ASP.NET.
Several ASP.NET application services rely on a provider to manage storing and retrieving data from a data source. ASP.NET includes a SQL Server provider for Membership, Role Management and Profile - the features that I've used in building my new site.
I created a new database in SQL Server Management Studio, called EigerDB. Back in Visual Studio, I right clicked on the ASPNETDB.mdf database (in the database explorer) and chose "Publish to provider". Choosing this option opens up the Database Publishing Wizard where you can generate an SQL script. I generated a script for each of the two databases and executed each script in SQL Server Management Studio with my new EigerDB database selected. I now had a new database that comprised of my two original databases.
When I deploy to my remote server, I will use the "Publish to provider" option to generate the necessary script to recreate my EigerDB database.
There was one more thing to do locally and that was to change my connection strings in the config file to point to EigerDB. Also, don't forget to change the connection string name under Memebership > Providers or the SQL provider will still be pointing at ASPNETDB.mdf.
If you are using MS SQL Express 2005 or an older version then you may need to download and install the Database Publishing Wizard.