Publishing sql database to remote site

...now browsing by tag

 
 

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.