Backup SQL Server Database remotely without backup permission

Question

I have a host which runs SQL Server 2005, I have the ability to login to my database and copy/read/write/drop all tables/views/sp's. I tried simply right clicking on my server and selecting backup but I don't seem to have permission to do any backing up.

This cannot be an uncommon occurrence (someone using host/providers db).

Has anyone figured out how to backup the database to a remote computer (without resorting to plain old copy row by row)? I'd Like to have my own backup of my server without relying on my host to preform backups. If so, what library/app allowed you to do this.

Note, I believe that my host does allow for some form of backup using (Database Publishing Wizard) Does anyone know what libraries it uses (so that I may reproduce it in my code)?

Answer 1

To the other response: Hosted provider; I highly doubt they are giving out permission to read/write local disks to the user accounts they setup.

• Personally I would just script the DB using the DB Publishing wizard, which should give you an actual script you can use in the future.

• You can also right-click on the DB and choose the "script" option (at least in non-express)

• Alternately, you could contact the host and ask about access to your backups. Don't know if you could get them or not, or what the process of giving them back for restoration would be.

• There are other tools out there for this, which you can get the source to. Not sure how well they all work, though

  • VB.Net backup util (make a version w/o the interface)
  • More info on same util / SQL DMO
  • SQL DMO code to do the backup

Answer 2

The Database Publishing Wizard just pulls all of the objects in your database into a .sql file. I'm not sure about the libraries, but I'm a bit unclear as to why you need to reproduce it. If you need to call it programatically, it has a command line interface.

Here's the link to the download: Linkage

Also, here's a link on how to use the Publishing Wizard from the command line: More Linkage

Answer 3

To add to Joel's comment: The hosting company probably wants to restrict these operations for performance reasons on their SQL Server (maybe they only want backups to occur at night or something). Or sometimes they just want to charge extra for frequent backups.

So you'll have to do something different to backup the data. Don't forget to stay within the host's SQL limits ;)

Answer 4

Why don't you export the database to another SQL server? If you have read permission you can do it. It's most like a backup with sutile differences.

Here is a tutorial of how to export a database:

http://www.databasejournal.com/features/mssql/article.php/3580216/SQL-Server-2005-Import--Export-Wizard.htm

You can export it to your Express edition with some limits, like for example a maximum of 1 gigabyte of size.

Answer 5

Your best bet is to contact your hosting provider and see if they offer access to backups from a web control panel or other means.

Answer 6

Take a look at http://www.codeplex.com/SqlWebAdmin

I use it along with 1and1.com host, just install it like any other website and then you 'should' be able to do ALOT of administrative features similar to SSMS. Your hosting service milage may vary... I know alot of them have things locked down pretty tight for obvious reasons.

Answer 7

If you are using SQL Server, you can start the MSSQL service to the network service as this way your backup you can create your database in a shared folder on your computer, provided you have write permissions on the shared folder. ñ_ñ

USE AdventureWorks;
GO
BACKUP DATABASE AdventureWorks
TO DISK = '\\yourmachine\SQLServerBackups\AdventureWorks.Bak'
   WITH FORMAT,
      NAME = 'Full Backup of AdventureWorks';
GO

see: http://msdn.microsoft.com/en-us/library/ms191304.aspx