Automated SQL Server Database backups

Discussion in 'Databases' started by joelilly, Nov 14, 2011.

  1. I need to automate my SQL backups. I'm aware of how to backup thru the Winhost GUI and that works great. But I need to call the same function from my locally running .NET code which schedules the backup on some interval (ie daily). I want to execute a command such as:

    BACKUP DATABASE [databasename] TO DISK=N' [App_DataFolder] + [databasename] + "_backup.bak'

    I've tested this command successfully against my development server, but I haven't yet been successful against the Winhost server. I'd like to either
    a) backup directly to a local (shared) folder or,
    b) backup to the App_Data folder of my Winhost account

    With option a, I understand that a SQL db can be backed up to a local folder if the folder is shared and if permission is granted, in this case to the Winhost server. How do I grant permission to the Winhost server?

    With option b, I need to pass the correct folder string. I've tried using the [root]/App_Data of my account and I throw this error:

    Cannot open backup device [root]\App_Data\[databasename]_backup.bak'. Operating system error 3(The system cannot find the path specified.)

    I suspect that this fails because the folder path is a physical E:\... on the Winhost web server and isn't local to the Winhost SQL Server.

    Is there a string for [App_DataFolder] (a UNC path to a shared folder that begins with \\ and not a physical drive like E:\) that I may use for this command?
     
    Last edited by a moderator: Oct 14, 2015
  2. curtis

    curtis Winhost Staff

    Unfortunately, there is not a way to automate the sql backup - you will need to run the sql backup tool manually through the CP GUI.
     
  3. Curtis, Will you help me solve this problem please?

    I need automated SQL database backups. If Winhost won't allow me to do it the way I'm trying, will you suggest another way... maybe can Winhost execute a daily backup for me just the same as the manual process? My requirement is that I systematically and routinely pull backups off the Winhost server. I don't really care how we accomplish that.

    I was just told by Winhost that with an ultimate account, I would have a task scheduler in order to automate my backups. So now that I've paid for an ultimate account, I still don't see any way to issue a backup command. Please tell me that I didn't just get burned.

    I know this can be done. I just need a little support. Unfortunately, this may be a show-stopper. Please advise.

    Joe
     
    Last edited by a moderator: Oct 14, 2015
  4. curtis

    curtis Winhost Staff

    You will need to manually backup your sql database. There is no means to automate it. I'm sorry but we do not customize plans or set server configs on an individual basis. That can cause many issues down the road.

    The Ultimate plan has a scheduled task tool but there is no mechanism to automate the sql backup. You can downgrade the plan if you do not need the Ultimate plan.
     
  5. I think that this is a real shortcoming with Winhost. I just signed up for Max but if I cannot run daily automated backups, I may have to look for another hoster when my quarterly subscription is done.
     
    Last edited by a moderator: Oct 14, 2015
  6. curtis

    curtis Winhost Staff

    the only host i know of that you can automate sql backup is discountasp and they are more expensive than us.
     
  7. Ray

    Ray

    Well,, technically we automated database backups. We do it every night around 1 a.m. - 2 a.m. But these backups are for our own disaster recovery purposes. If you need us to retrieve the backup for you, we can do so, but a $30 administrative fee will be charged to ones account.
     
  8. Scheduled SQL Database Backups

    Guys,
    It is a real short coming not being able to schedule database backups. Just a simple backup will do such as database_name_yyyymmdd.bak. This will be a great addition, especially for customers who have a large number of databases.

    Can you let me know if there are any plans to do this soon?

    Thanks
    CA
     
  9. No, afraid not.
     
  10. Having asked about automatic backups on two different occasions via Support tickets, it puzzles me that no one at Winhost mentioned that you do disaster recover backups every day, and more importantly that it is possible to request and pay for ($30 is nothing) a retrieval!

    Why don't you mention this in your hosting plan description? If I had known this, I would never have bothered you with the question in the first place. What you offer now is all I need.
     
    Last edited by a moderator: Oct 14, 2015
  11. rum

    rum Winhost Staff

    Although we do perform nightly backups of user databases and keep them for few days, there is no guarantee that the backups will always be available. The full database backups are being taken by SQL Agent and it can fail for number of reasons. For example, if database is set to single user mode and there is an open connection to it (by web application or Management Studio), SQL Agent will not be able to connect to it and take the backup. The nightly backups of user databases is part of our disaster recovery plan and is not a replacement for user business backup requirements. You are advised to perform manual full database backups of your databases as part of your own disaster recovery plan.
     

Share This Page