none
SQL Server Backup Assurances

    Question

  • I am not a SQL tech but I need some assurance one of our recently inherited SQL Server 2005 databases (I work on the app side not the database side) is being adequately backed up so it could be restored if every neccesary.

    I need to ask the DBA for some assurance the database is backed up, but what can they provide in terms of the configuration of the backup job, and some indication the backup job is working. I'd also (for security assurance) want some assurance the backup is being written to a secure directory. What would you ask for in terms of a report/configuration report if you wanted the same assurance, using your technical knowledge of Ms-Sql.

    As a side issue, in your opinion, is there much a DBA can do wrong in relation to SQL Server database backups, or is it a pretty easy procedure to configure, that would be difficult to get wrong?

    
    Friday, April 19, 2013 1:38 PM

Answers

  • Hi,

    A lot of people make the mistake of never testing that they can actually restore from a backup.  There's lots that can go wrong during a backups creation that could render it useless.  Restoring it somewhere and giving it the once over is the only sure fire way to prove its worked properly.  We have 7 virtual environments here that we regularly refresh from production backups, so by proxy we're testing ours, but if you don't do something like this, you should have something scheduled to verify your backups.

    It might sound like a cop-out, but some of your questions might be answered differently if we knew your recovery objectives.  If your server failed now, would you need to be able to restore the database(s) to as close as possible to the failure, or would an hour or two be acceptable?  If someone asked you to restore a copy of that database from 5 months ago, do you have a retention policy in place to accommodate that request?

    As for where the backups are being written to; hopefully not kept on the same server as where the backups have been taken from.  Preferably not in the same building.  Another example, our backups are shipped to our DR site, and also an old bunker.  There are different ways to secure backups - http://msdn.microsoft.com/en-gb/library/ms190964(v=sql.105).aspx



    Thanks, Andrew


    Friday, April 19, 2013 2:08 PM

All replies

  • Hello,

    If they are using SQL Server native backups, you can look at the backup jobs history to see if the backups have been successful. Use the following article to know how to look at that history:

    http://msdn.microsoft.com/en-us/library/ms181046.aspx

    If they are using a third-party software, that type of software provides reports that can be emailed.

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Friday, April 19, 2013 1:51 PM
    Moderator
  • Hi,

    A lot of people make the mistake of never testing that they can actually restore from a backup.  There's lots that can go wrong during a backups creation that could render it useless.  Restoring it somewhere and giving it the once over is the only sure fire way to prove its worked properly.  We have 7 virtual environments here that we regularly refresh from production backups, so by proxy we're testing ours, but if you don't do something like this, you should have something scheduled to verify your backups.

    It might sound like a cop-out, but some of your questions might be answered differently if we knew your recovery objectives.  If your server failed now, would you need to be able to restore the database(s) to as close as possible to the failure, or would an hour or two be acceptable?  If someone asked you to restore a copy of that database from 5 months ago, do you have a retention policy in place to accommodate that request?

    As for where the backups are being written to; hopefully not kept on the same server as where the backups have been taken from.  Preferably not in the same building.  Another example, our backups are shipped to our DR site, and also an old bunker.  There are different ways to secure backups - http://msdn.microsoft.com/en-gb/library/ms190964(v=sql.105).aspx



    Thanks, Andrew


    Friday, April 19, 2013 2:08 PM