Are Your Database Backups Current and Available?

  • SQLRockstar

    SSCertifiable

    Points: 6022

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/tlarock/areyourdatabasebackupscurrentandavailable.asp

    ----------------------
    https://thomaslarock.com

  • SQL Pete

    SSC Enthusiast

    Points: 114

    I have a similar process which runs a sproc on each server which performs a LOAD HEADERONLY on each dump device which populates a central db with details of the backups for each server and database. A website and email notification lets me know if there are any databases without current backups on disk.

    It's been working well for several years.

  • voxinforma

    Grasshopper

    Points: 20

    anyone got a job that will: 1. create a test database; 2. restore a backup into the test database; 3.  verify that the restore succeeded; 4.  delete the test database.

    Now that would be a good test of current/available backups!

  • tbredeme

    Ten Centuries

    Points: 1106

    Thomas LaRock, you are my new hero!

    Artificial Intelligence stands no chance against Natural Stupidity.

  • Sassan Karai

    SSC Veteran

    Points: 290

    I can't seem to find either the download or DBA_Verify_File_Exists procedure.

  • Nancy Lytle

    SSC Rookie

    Points: 39

    Although I am new at this, it didn't take long to get this set up on my workstation, and its great!.  The question I have is, how do I get this to check all of the servers, or do I just have to set it up on each server?

    N Lytle


    NLytle

  • neil hoang

    Grasshopper

    Points: 15

    Where can I find the zip files that contains the store procs? Thanks.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716565

    My apologies!!!!

    The code is now linked at the bottom of the article.

  • Jim P.

    SSCrazy Eights

    Points: 8725

    But as I understand it, in SQL2K5 they are really restricting xp_cmdshell. Or has that changed?



    ----------------
    Jim P.

    A little bit of this and a little byte of that can cause bloatware.

  • SQLRockstar

    SSCertifiable

    Points: 6022

    Thanks Mom, always nice to hear from you.

    ----------------------
    https://thomaslarock.com

  • SQLRockstar

    SSCertifiable

    Points: 6022

    This was designed to be distributed, so yes you would have to push this out to each of your servers.

    It could be modified to run from a central location, as well as to generate only one email report each morning. But for something like this my preference is to have it reside on each server, and generate separate emails. I guess I prefer to have an email from each server in my inbox, so that i don't lose track of something during the day.

    We have some reports that are centrally located, and some that are distributed. Perhaps for my next article I will include something that is done centrally instead.

    ----------------------
    https://thomaslarock.com

  • SQLRockstar

    SSCertifiable

    Points: 6022

    In SS2K5 they have truned it off by default. You can turn it back on rather easily, and lock it down to specific users as well.

    I know, it is not the preferred method, and I am looking to figure out a way to do this using SQLCMD in the future. If I get the time to put it all together, I'll put together another article as well.

    ----------------------
    https://thomaslarock.com

  • Kathi Kellenberger

    SSChampion

    Points: 11811

    Voxinforma:

    I use a script to automatically find the lastest full backup, copy the file to a different server and restore the database.  I am using LiteSpeed, so I would need to create another script for native restores to post it for those interested.

    Great job, Tom!!!

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • SQLRockstar

    SSCertifiable

    Points: 6022

    Thanks Kathi!

    As for your script, why not pass a parameter (say, @use_native = 'Y'), and have the code issue a native backup command, otherwise you would call the litespeed xp proc.

    We use litespeed here as well, but we often have a need to ship a native backup to a vendor, so we ended up adding a parameter to our procs to handle the different dumps.

    ----------------------
    https://thomaslarock.com

  • Kathi Kellenberger

    SSChampion

    Points: 11811

    That's a good idea.   I'll think about doing that.  Thought, I might want to write an article about how to get started writing your own scripts and include it as part of the article.

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

Viewing 15 posts - 1 through 15 (of 16 total)

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