SQL Database Backups

Discussion in 'Databases' started by Stevercakes, Dec 13, 2009.

  1. Just thought of this, but do you back up the databases on a regular basis or is that left up to us to set up?
  2. While we strongly recommend customers to make their own backups from time to time, we do keep backups ourselves for emergency situations. Also, if you needed a backup from us, there is a fee for us to retrieve this backup.
  3. Ray


    There is a $30 administrative fee for retrieving the SQL backups from our storage server. Plus we can only keep a 7 day backup of our customers database.
  4. Is this real?? $30?! I'm a new customer, and I just switched from GoDaddy. Now I realize that you charge a ridiculous price for something they provide for free.

    I just talked to Raymond about a suggestion where you copy our backup files to our local shared web folders so we can download them. I'm surprised they don't already do that. He suggested posting it here. Having only a generate script option for our database backups is a hack.

  5. Ray


    The backup that we do is really for our own use in case of server failure or other unforeseen events. The backup that we do should not be regarded as your own backup routine.
  6. Sql Backups

    I agree. You should have your own.

    I'm not completely comfortable relying 100% on your disaster recovery plan. I think as customers, we should have access to our Sql Backups as part of the service we pay for. Relying on Sql Script generation (which is not easily automated) for our own personal backup isn't a very clean solution. We can very easily forget to script tables, constraints, etc since it is a manual process. Not to mention if you do it wrong, the generated script may be rendered useless out of the box. The backup file has everything we need and doesn't require us to generate scripts over the wire.

    Having access to this contributes to the peace of mind of your customers. Leaving our data to the cloud isn't always a good idea. Just go peek at: Sidekick Disaster I personally like to have my own disaster recovery plan.

    I'm only pressing the issue because I like how your web hosting services work. But there are a few key things we would like to have that could make your services much better for all your customers.
  7. People - please take some personal responsibility for your application and your data. After all this is YOUR application and YOUR data. Remember - we are paying $5 - $10 for a service. To me that is enough evidence that I need to be extra diligent in ensuring I do frequent and thourough backups. I always have the mindset that my hosting company can go away at any point in time or be down for an extensive outage. I should be able to move my code and data someplace else within a couple of hours. The risk I take is those missing data changes since the last backup. I am willing to live with this in exchange for getting away with $5 or event a month hosting.

    By the way - The fact that you may forget something does not make somebody else responsible for it.
  8. There are performance and space issues inherent in moving database backups to web servers. Imagine thousands of 200 or 300mb (or larger) files being transferred between servers every night. Then we also end up storing the data three times; in its live form, as our backup, and as a backup on the web server.

    I agree that you should never rely on a service provider as your backup strategy. As Ray pointed out, we maintain the backups for our own disaster recovery, they are not intended for customer use. If the fee seems excessive, it is. It's purposely excessive to prevent people from requesting backups.

    You have access to the database server, so it would be in your best interest to maintain your own backups on a schedule that you deem reasonable.
  9. Couldn't you just have each DB file created in a User Folder on the DB server (or where ever the DB Files reside) and simply map their ftp account to it?

    It's a lot easier to automate an ftp pull of the BAK file down to a local system than it is to automate the scripting necessary to accomplish it the way you provide.

    This is especially troublesome in the case where I recommend hosting for my clients (as is the current case) because now I can't set up an automated backup to one of their computers and instead have to manually go in and backup their DBs for them using VS. This alone prevents me from referring you to any more of my clients because I can't justify spending the time to run their backups for all of them (not to mention they would not be happy about me charging them every day for to run a backup).

    For that matter it wouldn't be that hard to program a service that could be accessed that would pull the Bak file from it's location on the SS and route via a WebRequest or ftp request - ditto for restorations.
  10. Ray


    The problem is that the web server and the SQL servers are on different hardware. And even if you were able to get direct access to the database base file .mdf, you cannot simply just copy it to your computer. You will first need to detach the database in the SQL server copy it to your local computer and then reattach the .mdf on the SQL server. As you know, in order to this you will need SA rights to the SQL server. Which we will not give a customer because it will compromise the security of all our other customers.
  11. Doesn't matter that they're on different servers. All you do on your end is

    1. Create a User Backup directory on the sql server machine. (this should be done when the user first creates the DB).

    2. When they create the DB the script should also create a job to do the backup into their individual directory. The backup should be full and set to overwrite to prevent growth in the BAK file (beside's all any of us want is a snapshot).

    3. Also, when the user creates their DB in the control panel you create a mapped drive to the directory on the sql server machine (it should be a UNC mapping re: \\theSqlServerMachine\theUserDBDirectory). This should have read only rights and is accessible to their main ftp account.

    Now they can run a backup from the SSMS into their directory and the BAK file will be available to them via ftp.

    No sa rights needed just the rights to run the backup or job.
  12. Ray


    That's not quite true. If you run any backup job off the SQL server or even schedule a Job SA rights are required or equivalent permission levels..
  13. http://msdn.microsoft.com/en-us/library/ms188283.aspx

    The SQLAgentUserRole may fit your needs here when coupled with the Backup Database right. Or you could just simply give the customer the Backup right and let them do it manually.

    In either case there is a fairly simple and secure way to let your customers backup their DBs and ftp the Bak files down to their local machines without having to grant SA right or copy the mdf (or other) files from the SS machine to somewhere else.
  14. It isn't a question of not knowing how to do it, we simply haven't implemented a database backup system yet. We understand it's a feature that a lot of users want, so we'll be working on it.
  15. Cool. I'll check back in periodically on it.

  16. Last edited: Oct 14, 2015
  17. Just tried it and it works like a champ.

    Great job guys!!!! Much Thanks.
  18. Cool, glad you like it.
  19. Ray


    Bear in mind that the restore functions over rides what you have existing in your database. If you want to upload new objects/tables without completely overriding everything, the database publishing wizard will be a good option for this.

Share This Page