How to backup database on remote server with a regular user account?

Question

How to backup database on remote server with a regular user account?

Kee

SSC Journeyman

Points: 88
More actions
May 20, 2006 at 10:56 am

#117722

Hello
I am hosting my website at a shared hosting company. I would like to backup my sql database every week or so. However I can't use the reguar backup tools avaliable with mssql manager because it only stores files on the server where the database is, I can't store the backup to my harddrive. My user account on the server doesn't have permissions to do so therefor I am looking for an alternate way to backup the data in my db. If any of you know any tools that allow that it would be great.
Thanks in advance!

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Mike C SSC-Insane Points: 23224 More actions · Answer 1

Most hosting companies have some sort of backup process already in place. You might talk to the hosting company and see if their process is adequate. If not, you probably need to work out an alternative solution with them. Creating your own custom process to work around your hosting service will be more of a hassle than it will probably be worth.

Kee SSC Journeyman Points: 88 More actions · Answer 2

My hosting company does have daily backup but I don't trust hosting companies anymore. I want to have the backup avaliable myself. I will ask them if there is any way to do it. Too bad MSSQL has such a complicated backup process as apposed to MYSQL for example where you can just generate a bunch of sql instructions and execute them.

Mike C SSC-Insane Points: 23224 More actions · Answer 3

Actually it's a fairly simple backup process, but unfortunately the backup process tends to assume that you have control of your own SQL Server... which we all know is not always the case You could go the route of generating a bunch of sql statements and executing them to grab your data remotely, but it would require you to create an application.

You might see if your host can schedule a backup job that will backup your database to a place within your own storage; then you could grab the backup file from your storage every day, every week, or whatever schedule you're interested in. That all depends on your host agreeing to it though...

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 4

SQL Server can backup to anywhere on the network that it has permission to write to. It is true that if you use Enterprise Manager to run the backup the user interface will only show you local drives, but if you learn how to write a BACKUP command you can specify any URL as the destination.

If SQL Server is running under a local system account, it probably won't be able to access other servers in the domain. If the SQL Server is running at a web hosting outfit, it is probably isolated by a firewall and cannot write to your harddrive. (Would you really want it to?).

I have gotten SQL Server to write to another server in spite of permission issues by mapping a drive letter to the foreign system with a different userid & password. This is something else that you probably can't do by going into Windows Explorer and clicking on things, the mapped drive has to be defined by the SQL Server login. Find someone with a little gray in their beard (who was around before Windows) to show you how to map the drive within SQL (Hint: exec xp_cmdshell 'net use ... ').

If the issue is the firewall (and it probably is), there's no good way for the remote-hosted server to push the file to your storage. It's usually easier to figure out a way to pull the files down using a scheduled job on your system.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 5

PS: Permission issues aside, what's so complicated about typing "BACKUP DATABASE <db> TO DISK='\\server\share\xxxx.BAK' "?