MS SQL server backup via stored procedure

megan

megan

Perch
HI - I have a set of stored procedures that allows me to backup a local sql server 2000 database, and then restore it using a coldfusion script - works great locally

I decided to test just the backup part on my jodohost sql server database and was able to create the backup stored procedure, but then when I tried running it using query analyzer , I got a permissions error message.

the stored procedure takes just 2 params, the name of the database and the path to the folder where the .bak file would be stored.

I have two questions - how would I go about getting the right permissions to create a backup this way, and what path would I use to store the bak file if the sql server db is on a different server than the regular website.

any ideas ??? is this do-able at all? (I am wanting to create a coldfusion db backup system) thanks, megan
 
You cant do it to another server (like the server where your website runs) and we already have automated routines to do backup every 24 hours the same way.
 
Ok - thanks for the reply. - and well, I have a potential client who is concerned about backups to the db and was looking for a way to have their own backups (from an admin without having to use enterprise manager) I guess not wanting to be so entirely dependent on the hosting provider.

thanks again, megan
 
Not really possible the way you have designed it unless he is viable enough for you to afford him a Windows VPS. You can use this VPS for other tasks as well.
 
You could write a script (.NET, ASP, PHP, whatever) to issue the necessary SQL commands to do the backup. You could save the results to a file, email them to your client, whatever. I've done this for PHP/MySQL (see link in sig) so it seems like something you could do for MS SQL.

Tim
 
Thanks for the reply :) - Yes, that is something I could do (good idea!) - I guess I was just wanting to find a less labor intensive route, but I'm sure this can be worked out :) - thanks again, Megan
 
You must log in or register to reply here.
Back
Top