How to automate SQL backup on a shared hosted server?

Question

I'm using a hosting service which allows me to backup my SQL 2008 database and download the BAK file via a web interface only--and I have access to the database via Management Studio. I can execute the backup command from Management Studio,but I don't have rights to the path where the backups are located. Is there any way via SQL or script to pull down a full copy of the database--can I generate the script required to recreate the database using code? It's only a few thousand records.

Thanks.

Answer 1

If you have access via Management Studio, why don't you copy the hosted db on to your local machine (Tasks > Copy DB)?

Once it's been restored to the local system, you can do whatever you want.

Answer 2

If a host does not provide an easy way to get offsite backups of your database(automated - and tell you how to do it), you should find another.

Answer 3

one way is to script the db structure and generate insert statements for the whole database with this SSMS addin http://www.ssmstoolspack.com/

Answer 4

Free SqlBackupAndFTP allows to do remote backups using scripting