Post reply

Automation of datbase restores and integrity checks.

  • June 22, 2012 at 9:30 am

    #262765

    I have been tasked with the automation of database restores and integrity checks with the purpose of:

    1.) Verifying that all our backups and media sets are valid.

    I know that I could choose the "Verify Database" option at the same time I'm creating the backup, but that is not an option for us as it would make the backups run past the backup window (we currently ruin backups from midnight-6:00am, and must stay within this window).

    2.) Running DBCC CHECKDB against each database without actually using the production server that houses them.

    Running a DBCC CHECKDB would add hours to our nightly maintenance schedule, so this is also not an option for us.

    So, my first step was to setup a VM running the highest version of SQL in our environment (SQL 2008R2 SP1).

    I have done this and I gave that server a little bit more SAN storage than our largest DB+LOG.

    The idea here, is that I need to create a "maintenance" database on this new server that has a single table with the following fields:

    1.) Database Server

    2.) Database Name

    3.) Backup File Name

    4.) Backup File Location

    5.) Day of Week to Run Restore & Validation

    Then, I need to setup a PowerShell script that will run each night, query the aforementioned table and perform the following actions:

    1.) Create blank databases on my "restore" server.

    2.) Restore the databases from their current location on their production servers.

    3.) Run a DBCC CHECKDB on the restored database.

    4.) Report any failures with the database restore or integrity checks by storing that information in a SQL table and emailing the DBA team.

    This process will need to be able to handle multiple databases, and on one particular night, a single large database.

    Does anyone have anything like this they are using already?

    If not, can anyone point me in the right direction to getting started building something like this?

    I beleive such a script/methodology will be very valuable to all of us as DBAs, as this seems to be a common problem among Enterprise implementations.

  • June 22, 2012 at 11:26 am

    #1504648

    I assume you have more than one server to include in this task

    For master database you have to perform CHECKDB on production server

    Check this article by Paul

    I have setup this task for my production databases and here is my approach

    I keep a table to track which server/database last CHECKDB ran on (just a table with single row) so that if my job fails for some reason, it can restart from the same point

    I just keep serverlist and all other information I am retriving from production server (msdb) using linked server. So that when new database is added, it will be automatically included in the task

    One table for log: which database, what time, source backup file etc... Also I am updating several checkpoints of CHECKDB process. i.e. updating this row with exact SQL before execution of each step... checking size of database vs available space, estimated tempdb size, restoring DB, running checkdb etc... this will help to investigate if something fails

    Also I have a boolean field for CHECKDB Error (this will help for reporting)

    One table for CHECKDB output, this is child of previous table

    Hope this will help

Viewing 2 posts - 1 through 1 (of 1 total)

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