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.
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.
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.