Publishing sql database to remote site

...now browsing by tag

 
"Client certificate" "Authenticate WCF Service clientw ith certificate" "Client certificate" "certificate authentication" "certificate service" .Net 1.1 abstract class abstract method allow access attribute Certificate Client certificate Client Certificate Authentication contenttype Elmah Elmah sql objects;Elmah sql logging setup error logging and handling Godaddy sql database publishing http service IIS6 IIS7 javascript closure json.dll local IIS server mail makecert Membership provider method chaining MVC4 Newton.json.dll object oriented oop open new window from aspnet code-behind Publishing sql database to remote site remote access Response.Redirect extension Secure Communication smtp Smtp mail smtp mail winhost mail SSL virtual method WCF WCF Certificate WCF Security Windows 7 winhost wordpress on iis7
 

How to move your SQL server database from one hosting site to another

Sunday, February 5th, 2012

I normally used backup and restore tool in SQL server to move one sql server database to another server in the same network; but this does not work if your db is in a remote shared hosting site like Godaddy. To get my sql db from Godaddy shared hosting environment and move it to local sql server or a different host server, I had to use Microsoft Database Publishing Wizard. This evening I have downloaded my sql server db (called it remoteDB) to my local sql 2008 express successfully, by going through these steps:

  1. Open a VS2010 Project (existing or create a new).
  2. Go to Server Explorer ->Data Connections, right click and select “Add New Connection”.
  3. Enter remotedb.db.xxxx.godaddyresource.com to “Server Name” and select “use SQL server authentication”; enter username and password assigned by Godaddy for the database.
  4. If server name, and user credential are correct and validated, your target database will now display under the “Select or enter a database name” dropdown box.
  5. Select the database and press OK.
  6. Return to Database Connections, right click on the newly added data connection, select “Publish to Provider” from the context menu.
  7. The Microsoft Database Publishing Wizard appears; just follow the onscreen instruction to script out all database objects for the selected database.
  8. When asked to “Select an Output Location”, I selected “Script to file” and saved to a sql file at local drive. “c:\development\remote\db\sqlbackup\remotedb_published_2-5-12.sql”.
  9. when asked to “Select Publishing Options”, I selected:
    • Drop existing objects in script =True
    • Schema qualify =True
    • Script for target database =SQL Server 2008 (other options are 2005 and 2000)
    • Type of data to publish=Schema and data
  10. Click on Finish.
  11. Now, open the localhost\SQLExpress2008 and attached the remoreDb.mdf from an older version
  12. Open the .sql file “remotedb_published_2-5-12.sql” and executed the sql file
  13. Verified all data and objects were imported as current as of 2-3-2012.

If publishing to another remote sql server, you will use SQL Server 2008 Management Studio to connect to that remote database, and execute the sql file directly against that database.

Posted in SQL Database Publishing, Web Development, Website Deployment | No Responses »
Tags: ,