CODEWITHSUNDEEP
c#sql-server-2005backup
maxfridbe
maxfridbe

Reputation: 5970

Backup SQL Server Database remotely without backup permission

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)?

Upvotes: 4

Views: 7257

Answers (9)

Deepak S V
Deepak S V

Reputation: 55

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

Sample Code:

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

Upvotes: -1

José Ibáñez
José Ibáñez

Reputation: 21

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

Upvotes: -1

backslash17
backslash17

Reputation: 5390

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.

Upvotes: 4

Christopher Klein
Christopher Klein

Reputation: 2793

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.

Upvotes: 0

Andrew
Andrew

Reputation: 8674

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

Upvotes: 0

Jeff Hall
Jeff Hall

Reputation: 1754

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.

Upvotes: 0

Keltex
Keltex

Reputation: 26426

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 ;)

Upvotes: -1

JustinT
JustinT

Reputation: 692

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

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 415830

I don't seem to have permission to do any backing up.

What do you mean by "seem"? Is is just that the option isn't there?

If that's the case, I suspect you're using the express edition of SQL Server Management Studio. Certain features in Management Studio — including backup/restore — are only available in a for-pay edition. But the feature still exists in the database itself and the permissions might be fine. If you connect with a full edition of Management Studio you might be able to do the back up.

Upvotes: 0

Related Questions