How to Transfer your local Sql Server 2005 database to your Hosting Provider’s database

If you’ve experienced as many problems as I have while trying to Import/Export my Sql Server 2005 database to a hosting provider for the first time, then hopefully this how-to will be of some assistance to you.  Keep in mind there are various ways to accomplish this task and this tutorial just touches on the basics of transferring a database up to a hosting provider.

1. Create new Server Group, and give it a name. To do this Right-click on Database Engine in the Registered Servers pane and then select New -> Server Group.
   Note: This step is optional, but I have found that it’s a good we to categorize you server registrations, if you have more than one remote database to connect to.
2. Now add a new Server Registration to the newly created Server Group by right-clicking on the Server Group folder name and selecting New -> Server Registration. Use your hosting provider’s database connection string information in the New Server Registration pop-up box. Make sure you use the correct Server name, login, and password. Click Test to check your connection and then click Save.
3. Connect to your localhost SQL Server 2005 on your computer. In the Object Explorer pane right-click on the database that you want to transfer to your hosting provider, and select Tasks -> Generate Scripts…  
   1. In the pop-up Script Wizard, click Next, then find and select your database, then click the Next button.
   2. In the Choose Script Options section scroll down to ‘Table/View Options’ area and change ‘Script Indexes’ to True, then hit Next.  * Note: if you do not change 'Script Indexes' to True, then your Primary and Unique Indexes will not be transferred.
   3. Now select the Object Types that you want to script such as Stored procedures, Tables or Views, and click Next. (Depending on your database design you may need to script objects such as User-defined functions, Database roles, Schema, Users, etc.)
   4. After selecting all of the Stored Procedures, Tables, and Views that you want, click Next.
   5. In Output Option section, leave the default setting, ‘Script to New Query Window’ and click Next.
   6. Now click Finish to generate your database scripts. If your scripts were generated successfully click Close.  You should now see the .sql file that was just generated in the active tab of SQL Server 2005.
4. Now connect to your database at your hosting provider from within SQL Server Management Studio. To do this, just double-click on the Server Registration that you created in Step 2 above, or right-click on the Server Registration and select Connect -> Object Explorer.  (and make sure you see it in the Available Databases drop down list box if you are using SQL Server Management Studio)
5. With the script file you just generated in Step 3 open/ active, and the database on your hosting provider selected as the available database in the Available Databases drop down box, click the Parse button to test out your generated sql script file.
   • (Parse is the button with a blue check mark on the SQL Editor toolbar).
   • (Note: The Available Databases drop down box is located on the SQL Editor toolbar.  To activate the SQL Editor toolbar from the main menu, click View -> Toolbars -> SQL Editor.) If you see “Command(s) completed successfully” in the Results window then you are ready to execute the file for real.
6. Click the Execute button on the SQL Editor toolbar. Once the query has finished executing, you should see a message saying “Command(s) completed successfully.”

Congratulations, you just transferred your databases objects (such as Stored Procedures) to your database at your hosting provider.

Before moving on, I would recommend that you double check to make sure all of your Table Indexes (Primary, Unique) and Relationships were transferred correctly to the hosting provider's database.

**** Half way there ****

In this next section you will learn how to import/copy data (table data) from your local database to your hosted database.

1. Right-click on your hosted database in the Object Explorer and select Tasks -> Import Data…
2. In the pop-up SQL Server Import and Export Wizard, click Next.
3. Choose the database source where you want to copy data from.  Since you are importing data from your local database, leave the Server name: (local). Leave the Authentication as “Use Windows Authentication” and select your local database in the Database: drop down box, and click the Next button.
4. Choose the database that you want to import data to. The Sever name and Database should be already selected for you, if not, make your selections.  In the Authentication area choose “Use SQL Server Authentication” and type in your User name and Password that you use to connect to the database with.  Now click Next.
5. In the Specify Table Copy or Query section, leave the default “Copy data from one or more tables or views” selected and click Next.
6. Select the Tables and Views that you want to copy to your hosted database.  If you want to change a table or views Column Mappings, select the destination Table and click on the Edit Mappings button. Also you may or may not want to copy all of the Tables and Views data to your hosted database. However, you probably will want to copy any User account tables so that you will be able to log into a web application tied to the database.  For instance, if your using ASP.NET 2.0’s Membership provider then you will want to select the “aspnet_...” tables.  Once you’ve selected all the Tables and Views data you want import, click Next.
7. In the Save and Execute Package section, leave Execute immediately checked and click Next.
8. Click the Finish button to copy data to your hosted database. (And cross your fingers, because this is a spot that you may run into errors during execution.)
9. If the execution was successful, congrats! Now click the Close button.

If the execution was unsuccessful you may need to go back and try copying fewer Tables or Views at a time. You may also need to click on the Edit Mappings button for each table and make sure you are copying the table data correctly.

* Note: Make sure your Identity values transferred correctly.  For instance, table data may get transferred, but the identity column may be re-numbered starting with 1, but your local database table identity may start with a number like 25 and not have consistent id’s from rows being deleted. If you have related tables that depend on the identity values, then your database will not function properly. That’s why it’s best to double check to make sure everything is consistent. 

For more on SQL Server not keeping your Identity insert values correctly when choosing “Optimize for Many Tables” check out, http://blogs.msdn.com/chrissk/archive/2006/06/24/645968.aspx