Backup a SQL Server 2008 Database From a Shared Hosting Environment

SQL Server hosting is usually expensive so shared hosting is often used to keep costs down. With shared database hosting, and with shared web hosting, security is usually tighter and this leads to some standard functionality not being available. With SQL Server 2008 shared hosting the Back Up task is often disabled.

In this article we cover an alternative way to backup your database using the Generate Scripts and Export Data database tasks.The Generate Scripts task is used to generate an SQL script that creates all the different objects that exist in the database. This script can then be executed on a local database to create a backup of the database objects. With the objects created an Export Data task can be executed to copy the data from the remote database to the local database.

Generating the SQL Script

The database objects can be scripted by right clicking the database in Microsoft SQL Server Management Studio and selecting Tasks > Generate Scripts.

Generate Scripts database task

You can then specify the tables, stored procedures, functions and users to be scripted.

On the Set Scripting Options screen you should use the Advanced button so you can control exactly what is scripted. By default indexes are not scripted so you will need to set the Script Indexes option to True so that indexes are included. Full-text indexes are also disabled by default so should be enabled if used.

Scripting indexes with the Generate Scripts advanced scripting options

With the advanced options configured you can save the script to a new query window and execute the SQL on the local database. With the database objects created you can now copy the data.

Importing/Exporting the Data

The local (backup) and remote databases should now have the same objects so you can easily backup the data using the Import/Export Data tasks available from the database Tasks menu (right clicking the database as before).

With the source and destination databases configured you should highlight all the tables and select Edit Table Mappings. This allows you to set table mapping rules for all tables at once. On the mapping screen you select: do not drop tables, delete data and allow identity inserts. With these options the values of identity indexes will be preserved.

With the mappings set you should be ready to run the task and backup your data. You can save the task as a package to run at a later date to save you going through the import/export steps again.

This process can also be used to copy a database from your local environment to your remote hosting environment.

This entry was posted in Programming and tagged . Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.

2 Comments

  1. Posted September 2, 2011 at 2:55 am | Permalink

    I got this error message: insufficient rights.
    How do you set the appropriate rights on the remote server?

    Thanks…J

    Getting the list of objects from ‘DB_21542_mm’. Passed
    Preparing DB_21542_mm Failed Microsoft.SqlServer.Management.Smo.SmoException: Could not read metadata, possibly due to insufficient access rights. at Microsoft.SqlServer.Management.SqlScriptPublish.GeneratePublish
    Page.worker_DoWork(Object sender, DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.
    OnDoWork(DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.
    WorkerThreadStart(Object argument

    • Posted September 2, 2011 at 7:41 am | Permalink

      At what point did the error occur Jakub? Can you see if any of the objects were created?

      You probably need to grant privileges on all objects in your database to your user (the hosting provider you are using may have restrictions with this though).

      For a table you can use the following:

      GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON [table] to [user]

      For a stored procedure you just need the EXECUTE privilege:

      GRANT EXECUTE ON [stored_procedure] to [user]

      You can find out more about the GRANT statement on the MSDN reference.

      I hope this helps.

One Trackback

  1. […] Backup a SQL Server 2008 Database From a Shared Hosting Environment […]

Post a Reply to Nick Cancel Reply

*
*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

* = Required