SQL Server Assured Recovery Test

In today’s world, it is important to make sure you can recover your data at any time and that you can rely on your backup. I have seen too many times the struggles to get data back, and when restored only to find out the data was corrupted or even not able to recover at all.

One of the great new features of UDP 6.5 is Assured Recovery Testing (AR), which allows you to view the health status of the recovery points which helps you to make sure you can recover from the data that is protected.

The AR Testing is part of a plan in UDP, and this plan is based on a backup / replication source from previous task.

AR testing provides you with two options;

  • Instant Virtual Machine based test
  • Instant Virtual Disk based test

Instant VM based test boots the recovery point by creating an Instant VM and checks the heartbeat of the VM, optional you can inject test scripts for further testing. Instant VM testing supports Windows and Linux operating systems and the instant VM can be started from either VMware or Hyper-V hyper visors.

Instant Virtual Disk bases tests mounts the recovery point by creating instant disk and runs check disk to check the data integrity of the volume and runs a verify file system that verifies if the file system and size of volume are similar to what the source machine has.

AR testing is great feature however, it does need planning especially when you are going to use the instant VM testing.  I always advice to use a separate virtual network for IVM testing, this will prevent any conflicts between the source and the test IVM. Also, resource planning is important, you can schedule when to run the AR tests so that it won’t run during production hours but also you can schedule the different plans at different times so that not too many IVM are running at the same time which can cause congestion in your virtual environment. In larger environments, I do recommend to have a separate DR cluster to be used for IVM testing.

In this post, I will create a plan for a SQL server to be tested with AR testing and I will add an additional test to check the database integrity using a script.

Pre-requisites:

  • Arcserve UDP 6.5
  • Hyper-visor (VMware or Hyper-V)
  • IVM Network
  • Two shares for my scripts and logs

As I am using a script to perform some additional testing I created two shares on my RPS server called logs and scripts. The RPS server has an additional network card attached to the IVM network.

First we create a new task called Assured Recovery Test

At the source screen, I select the recovery point I would like to test, in my example I will use the last recovery point, however you can select to test it on daily / weekly and monthly recovery points as well.

Next is to set the test settings. First check if you want to perform an instant virtual machine or a instant virtual disk test. In this scenario I want to do an instant virtual machine test and I will select my VMware environment as the hyper visor environment. Scrolling down in the screen you can set the parameters for the testing;

Select the proxy server (in my case the RPS server) and set the resources you would like to use for the Instant VM. As I am using VMware, make sure you enter the path for the VM files folder as the instant VM is using NFS to mount the datastore to the hyper-visor.

In the schedule, you can set the times when to run the tests

For example, only on Sunday from 8 in the morning till 6 in the afternoon.

In the advanced screen you can set the heartbeat properties and the custom script.

Why would you use a script?

As you can imagine, booting up a virtual machine does not guarantee the integrity of your database itself, so you can run scripts to test this inside the IVM.

I am using two scripts

  • DBcheck.bat
  • dbIntCheck.PS1

The DBcheck file is used to inject into the VM and will call the dbIntCheck powershell script. This powershell script will run a few commands to check if the SQL service is running but also to check the integrity of the database itself.

In my lab server SQL I have one database called AdventureWorks which will be tested.

In the Batch file I have set two variables

  • LogPath – Pointing to the log share
  • Scriptpath – Pointing to the script share

The batch file first checks if a certain file exist in the log share if so than delete that file and continue to call the powershell script.

I have setup a simple loop check that waits until the powershell script is finished before closing it.


@ECHO off
REM Script to run powershell script for Assured Recovery Testing
REM this script will call a powershell script which runs an integity check on a particular database
REM set paths to your log and script file share location
set LogPath="\\[SERVER]\Logs"
set ScriptPath="\\[SERVER]\Scripts"
setlocal enabledelayedexpansion

if exist %LogPath%\%computername%-DBcheck.log (
echo %computername%-DBcheck.log file exist
echo %DATE% %TIME% %computername%-DBcheck.log file exist, removing old %computername%-DBcheck.log file >> %LogPath%\script.log
del %LogPath%\%computername%-DBcheck.log /F /Q
) else (
echo %computername%-DBcheck.log not available
echo %DATE% %TIME% %computername%-DBcheck.log Not available >> %LogPath%\script.log
Goto script
)

:script
Powershell.exe -NoProfile -ExecutionPolicy ByPass -Command "& '%ScriptPath%\dbIntCheck.ps1'"

:Check
if exist %LogPath%\%computername%-DBcheck.log (
echo %computername%-DBcheck.log Created, script has run
echo %DATE% %TIME% %computername%-DBcheck.log Created, script has run >> %LogPath%\script.log
Goto EOF
) else (
echo %computername%-DBcheck.log not available
echo %DATE% %TIME% %computername%-DBcheck.log Not created, script not finished >> %LogPath%\script.log
Goto Check
)

:EOF
exit

Next is the powershell script. In order to perform this powershell script inside the SQL IVM,  I needed to change the right for NT Authortity/SYSTEM account within SQL Server management. Make sure that this account has sysadmin role else your test will fail.

In the powershell script I created two variables that needs to be changed for your environment;

  • Logfile – Pointing to the log file share
  • DatabaseName – the database that I want to check

The script first will check if the SQL service is running. Than it will call a DBCC check.  This checks the logical and physical integrity of all the objects in the specified database. I will write results into the log files.


# Reset error output
# Change Log file path [SERVER] into your Server name / IP
$sqlerr = $null;
$Logfile = "\\[SERVER]\Logs\$(gc env:computername)-DBcheck.log"
$node = gc env:computername
$date = Get-Date

# Check if SQL servis running and write to log
$txtService = "$date Service MSSQLSERVER on $node is "
$service = Get-Service -Name MSSQLSERVER
$result = $txtService+$service.Status
$result >> $logfile

# Do a Database inegrity check using SQLCMD and output to log file
# change DatabaseName variable to your database that you want to be checked
$DatabaseName = '[YOUR DATABASE]'
$Sql = "DBCC CHECKDB ([$DatabaseName]);"
$CheckDbMessages = @()
$CheckDbWatch = [System.Diagnostics.Stopwatch]::StartNew()
SQLCMD.EXE -E -Q $Sql |
ForEach-Object {
$CheckDbMessages += $_
" {0:s}Z $_" -f ([System.DateTime]::UtcNow) >> $Logfile
}
$CheckDbWatch.Stop()

"{0:s}Z Database [$DatabaseName] integrity check done in $($CheckDbWatch.Elapsed.ToString()) [hh:mm:ss.ddd]." -f ([System.DateTime]::UtcNow) >> $Logfile
if ($CheckDbMessages[($CheckDbMessages.Count) - 2] -clike 'CHECKDB found 0 allocation errors and 0 consistency errors in database *') {
"{0:s}Z Database [$DatabaseName] integrity check is OK." -f ([System.DateTime]::UtcNow) >> $Logfile
}
else {
"{0:s}Z Error in integrity check of the database [$DatabaseName]:`n $($CheckDbMessages[($CheckDbMessages.Count) - 2])" -f ([System.DateTime]::UtcNow)
throw "Integrity check of the database [$DatabaseName] failed." >> $Logfile
}

The scripts can also be download from my Github

When I start the plan it will first run a backup and then it starts to run the Instant VM testing depeding on the schedule. You can check the job log and the report screen of the duration and if it was successfully done:

The scripts also created out put for me

The script.log has the following logs written:

Sun 03/26/2017 11:58:11.63 SQL-DBcheck.log file exist, renaming old SQL-DBcheck.log file 
Sun 03/26/2017 11:58:44.22 SQL-DBcheck.log Created, script has run

and the database script SQL-DBcheck shows the following:

03/26/2017 11:58:21 Service MSSQLSERVER on SQL is Running
 2017-03-26T09:58:42Z DBCC results for 'AdventureWorks2014'.
 2017-03-26T09:58:42Z Service Broker Msg 9675, State 1: Message Types analyzed: 14.
 2017-03-26T09:58:42Z Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
 2017-03-26T09:58:42Z Service Broker Msg 9667, State 1: Services analyzed: 3.
 2017-03-26T09:58:42Z Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
 2017-03-26T09:58:42Z Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
 2017-03-26T09:58:42Z Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
 2017-03-26T09:58:42Z Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
 2017-03-26T09:58:42Z Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0.
 2017-03-26T09:58:42Z DBCC results for 'sys.sysrscols'.
 2017-03-26T09:58:42Z There are 1977 rows in 24 pages for object "sys.sysrscols".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysrowsets'.
 2017-03-26T09:58:42Z There are 347 rows in 4 pages for object "sys.sysrowsets".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysclones'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.sysclones".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysallocunits'.
 2017-03-26T09:58:42Z There are 400 rows in 5 pages for object "sys.sysallocunits".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysfiles1'.
 2017-03-26T09:58:42Z There are 2 rows in 1 pages for object "sys.sysfiles1".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysseobjvalues'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.sysseobjvalues".
 2017-03-26T09:58:42Z DBCC results for 'sys.syspriorities'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.syspriorities".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysdbfrag'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.sysdbfrag".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysfgfrag'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.sysfgfrag".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysdbfiles'.
 2017-03-26T09:58:42Z There are 2 rows in 1 pages for object "sys.sysdbfiles".
 2017-03-26T09:58:42Z DBCC results for 'sys.syspru'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.syspru".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysbrickfiles'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.sysbrickfiles".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysphfg'.
 2017-03-26T09:58:42Z There are 1 rows in 1 pages for object "sys.sysphfg".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysprufiles'.
 2017-03-26T09:58:42Z There are 2 rows in 1 pages for object "sys.sysprufiles".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysftinds'.
 2017-03-26T09:58:42Z There are 3 rows in 1 pages for object "sys.sysftinds".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysowners'.
 2017-03-26T09:58:42Z There are 15 rows in 1 pages for object "sys.sysowners".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysdbreg'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.sysdbreg".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysprivs'.
 2017-03-26T09:58:42Z There are 142 rows in 1 pages for object "sys.sysprivs".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysschobjs'.
 2017-03-26T09:58:42Z There are 2819 rows in 48 pages for object "sys.sysschobjs".
 2017-03-26T09:58:42Z DBCC results for 'sys.syscsrowgroups'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.syscsrowgroups".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysexttables'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.sysexttables".
 2017-03-26T09:58:42Z DBCC results for 'sys.syscolpars'.
 2017-03-26T09:58:42Z There are 1819 rows in 34 pages for object "sys.syscolpars".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysxlgns'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.sysxlgns".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysxsrvs'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.sysxsrvs".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysnsobjs'.
 2017-03-26T09:58:42Z There are 7 rows in 1 pages for object "sys.sysnsobjs".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysusermsgs'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.sysusermsgs".
 2017-03-26T09:58:42Z DBCC results for 'sys.syscerts'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.syscerts".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysrmtlgns'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.sysrmtlgns".
 2017-03-26T09:58:42Z DBCC results for 'sys.syslnklgns'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.syslnklgns".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysxprops'.
 2017-03-26T09:58:42Z There are 1232 rows in 41 pages for object "sys.sysxprops".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysscalartypes'.
 2017-03-26T09:58:42Z There are 40 rows in 1 pages for object "sys.sysscalartypes".
 2017-03-26T09:58:42Z DBCC results for 'sys.systypedsubobjs'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.systypedsubobjs".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysidxstats'.
 2017-03-26T09:58:42Z There are 479 rows in 9 pages for object "sys.sysidxstats".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysiscols'.
 2017-03-26T09:58:42Z There are 759 rows in 4 pages for object "sys.sysiscols".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysendpts'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.sysendpts".
 2017-03-26T09:58:42Z DBCC results for 'sys.syswebmethods'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.syswebmethods".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysbinobjs'.
 2017-03-26T09:58:42Z There are 23 rows in 1 pages for object "sys.sysbinobjs".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysaudacts'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.sysaudacts".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysobjvalues'.
 2017-03-26T09:58:42Z There are 799 rows in 152 pages for object "sys.sysobjvalues".
 2017-03-26T09:58:42Z DBCC results for 'sys.syscscolsegments'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.syscscolsegments".
 2017-03-26T09:58:42Z DBCC results for 'sys.syscsdictionaries'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.syscsdictionaries".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysclsobjs'.
 2017-03-26T09:58:42Z There are 22 rows in 1 pages for object "sys.sysclsobjs".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysrowsetrefs'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.sysrowsetrefs".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysremsvcbinds'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.sysremsvcbinds".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysxmitqueue'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.sysxmitqueue".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysrts'.
 2017-03-26T09:58:42Z There are 1 rows in 1 pages for object "sys.sysrts".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysconvgroup'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.sysconvgroup".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysdesend'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.sysdesend".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysdercv'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.sysdercv".
 2017-03-26T09:58:42Z DBCC results for 'sys.syssingleobjrefs'.
 2017-03-26T09:58:42Z There are 873 rows in 6 pages for object "sys.syssingleobjrefs".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysmultiobjrefs'.
 2017-03-26T09:58:42Z There are 861 rows in 6 pages for object "sys.sysmultiobjrefs".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysguidrefs'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.sysguidrefs".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysfoqueues'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.sysfoqueues".
 2017-03-26T09:58:42Z DBCC results for 'sys.syschildinsts'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.syschildinsts".
 2017-03-26T09:58:42Z DBCC results for 'sys.syscompfragments'.
 2017-03-26T09:58:42Z There are 3 rows in 1 pages for object "sys.syscompfragments".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysftsemanticsdb'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.sysftsemanticsdb".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysftstops'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.sysftstops".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysftproperties'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.sysftproperties".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysxmitbody'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.sysxmitbody".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysfos'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.sysfos".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysqnames'.
 2017-03-26T09:58:42Z There are 363 rows in 4 pages for object "sys.sysqnames".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysxmlcomponent'.
 2017-03-26T09:58:42Z There are 315 rows in 2 pages for object "sys.sysxmlcomponent".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysxmlfacet'.
 2017-03-26T09:58:42Z There are 145 rows in 1 pages for object "sys.sysxmlfacet".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysxmlplacement'.
 2017-03-26T09:58:42Z There are 322 rows in 2 pages for object "sys.sysxmlplacement".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysobjkeycrypts'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.sysobjkeycrypts".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysasymkeys'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.sysasymkeys".
 2017-03-26T09:58:42Z DBCC results for 'sys.syssqlguides'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.syssqlguides".
 2017-03-26T09:58:42Z DBCC results for 'sys.sysbinsubobjs'.
 2017-03-26T09:58:42Z There are 3 rows in 1 pages for object "sys.sysbinsubobjs".
 2017-03-26T09:58:42Z DBCC results for 'sys.syssoftobjrefs'.
 2017-03-26T09:58:42Z There are 165 rows in 2 pages for object "sys.syssoftobjrefs".
 2017-03-26T09:58:42Z DBCC results for 'sys.plan_persist_plan'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.plan_persist_plan".
 2017-03-26T09:58:42Z DBCC results for 'sys.xml_index_nodes_1765581328_256000'.
 2017-03-26T09:58:42Z There are 195 rows in 3 pages for object "sys.xml_index_nodes_1765581328_256000".
 2017-03-26T09:58:42Z DBCC results for 'Production.ScrapReason'.
 2017-03-26T09:58:42Z There are 16 rows in 1 pages for object "Production.ScrapReason".
 2017-03-26T09:58:42Z DBCC results for 'sys.plan_persist_runtime_stats'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.plan_persist_runtime_stats".
 2017-03-26T09:58:42Z DBCC results for 'sys.xml_index_nodes_1765581328_256001'.
 2017-03-26T09:58:42Z There are 301696 rows in 2152 pages for object "sys.xml_index_nodes_1765581328_256001".
 2017-03-26T09:58:42Z DBCC results for 'sys.plan_persist_runtime_stats_interval'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.plan_persist_runtime_stats_interval".
 2017-03-26T09:58:42Z DBCC results for 'sys.xml_index_nodes_526624919_256000'.
 2017-03-26T09:58:42Z There are 9113 rows in 64 pages for object "sys.xml_index_nodes_526624919_256000".
 2017-03-26T09:58:42Z DBCC results for 'HumanResources.Shift'.
 2017-03-26T09:58:42Z There are 3 rows in 1 pages for object "HumanResources.Shift".
 2017-03-26T09:58:42Z DBCC results for 'sys.plan_persist_context_settings'.
 2017-03-26T09:58:42Z There are 0 rows in 0 pages for object "sys.plan_persist_context_settings".
 2017-03-26T09:58:42Z DBCC results for 'sys.xml_index_nodes_418100530_256000'.
 2017-03-26T09:58:42Z There are 350 rows in 5 pages for object "sys.xml_index_nodes_418100530_256000".
 2017-03-26T09:58:42Z DBCC results for 'Production.ProductCategory'.
 2017-03-26T09:58:42Z There are 4 rows in 1 pages for object "Production.ProductCategory".
 2017-03-26T09:58:42Z DBCC results for 'sys.xml_index_nodes_418100530_256001'.
 2017-03-26T09:58:42Z There are 650 rows in 12 pages for object "sys.xml_index_nodes_418100530_256001".
 2017-03-26T09:58:42Z DBCC results for 'Purchasing.ShipMethod'.
 2017-03-26T09:58:42Z There are 5 rows in 1 pages for object "Purchasing.ShipMethod".
 2017-03-26T09:58:42Z DBCC results for 'sys.fulltext_index_docidstatus_610101214'.
 2017-03-26T09:58:43Z There are 0 rows in 0 pages for object "sys.fulltext_index_docidstatus_610101214".
 2017-03-26T09:58:43Z DBCC results for 'sys.sqlagent_jobs'.
 2017-03-26T09:58:43Z There are 0 rows in 0 pages for object "sys.sqlagent_jobs".
 2017-03-26T09:58:43Z DBCC results for 'sys.fulltext_docidfilter_610101214'.
 2017-03-26T09:58:43Z There are 0 rows in 0 pages for object "sys.fulltext_docidfilter_610101214".
 2017-03-26T09:58:43Z DBCC results for 'Production.ProductCostHistory'.
 2017-03-26T09:58:43Z There are 395 rows in 3 pages for object "Production.ProductCostHistory".
 2017-03-26T09:58:43Z DBCC results for 'sys.sqlagent_jobsteps'.
 2017-03-26T09:58:43Z There are 0 rows in 0 pages for object "sys.sqlagent_jobsteps".
 2017-03-26T09:58:43Z DBCC results for 'sys.fulltext_indexeddocid_610101214'.
 2017-03-26T09:58:43Z There are 4 rows in 1 pages for object "sys.fulltext_indexeddocid_610101214".
 2017-03-26T09:58:43Z DBCC results for 'sys.sqlagent_job_history'.
 2017-03-26T09:58:43Z There are 0 rows in 0 pages for object "sys.sqlagent_job_history".
 2017-03-26T09:58:43Z DBCC results for 'sys.fulltext_avdl_610101214'.
 2017-03-26T09:58:43Z There are 3 rows in 1 pages for object "sys.fulltext_avdl_610101214".
 2017-03-26T09:58:43Z DBCC results for 'sys.sqlagent_jobsteps_logs'.
 2017-03-26T09:58:43Z There are 0 rows in 0 pages for object "sys.sqlagent_jobsteps_logs".
 2017-03-26T09:58:43Z DBCC results for 'sys.fulltext_index_docidstatus_1589580701'.
 2017-03-26T09:58:43Z There are 0 rows in 0 pages for object "sys.fulltext_index_docidstatus_1589580701".
 2017-03-26T09:58:43Z DBCC results for 'sys.fulltext_docidfilter_1589580701'.
 2017-03-26T09:58:43Z There are 0 rows in 0 pages for object "sys.fulltext_docidfilter_1589580701".
 2017-03-26T09:58:43Z DBCC results for 'Production.ProductDescription'.
 2017-03-26T09:58:43Z There are 762 rows in 18 pages for object "Production.ProductDescription".
 2017-03-26T09:58:43Z DBCC results for 'sys.fulltext_indexeddocid_1589580701'.
 2017-03-26T09:58:43Z There are 13 rows in 1 pages for object "sys.fulltext_indexeddocid_1589580701".
 2017-03-26T09:58:43Z DBCC results for 'Sales.ShoppingCartItem'.
 2017-03-26T09:58:43Z There are 3 rows in 1 pages for object "Sales.ShoppingCartItem".
 2017-03-26T09:58:43Z DBCC results for 'sys.fulltext_avdl_1589580701'.
 2017-03-26T09:58:43Z There are 3 rows in 1 pages for object "sys.fulltext_avdl_1589580701".
 2017-03-26T09:58:43Z DBCC results for 'sys.fulltext_index_docidmap_1077578877'.
 2017-03-26T09:58:43Z There are 13 rows in 1 pages for object "sys.fulltext_index_docidmap_1077578877".
 2017-03-26T09:58:43Z DBCC results for 'Production.ProductDocument'.
 2017-03-26T09:58:43Z There are 32 rows in 1 pages for object "Production.ProductDocument".
 2017-03-26T09:58:43Z DBCC results for 'sys.fulltext_index_docidstatus_1077578877'.
 2017-03-26T09:58:43Z There are 0 rows in 0 pages for object "sys.fulltext_index_docidstatus_1077578877".
 2017-03-26T09:58:43Z DBCC results for 'DatabaseLog'.
 2017-03-26T09:58:43Z There are 1597 rows in 782 pages for object "DatabaseLog".
 2017-03-26T09:58:43Z DBCC results for 'sys.fulltext_docidfilter_1077578877'.
 2017-03-26T09:58:43Z There are 0 rows in 0 pages for object "sys.fulltext_docidfilter_1077578877".
 2017-03-26T09:58:43Z DBCC results for 'Production.ProductInventory'.
 2017-03-26T09:58:43Z There are 1069 rows in 7 pages for object "Production.ProductInventory".
 2017-03-26T09:58:43Z DBCC results for 'sys.fulltext_indexeddocid_1077578877'.
 2017-03-26T09:58:43Z There are 13 rows in 1 pages for object "sys.fulltext_indexeddocid_1077578877".
 2017-03-26T09:58:43Z DBCC results for 'Sales.SpecialOffer'.
 2017-03-26T09:58:43Z There are 16 rows in 1 pages for object "Sales.SpecialOffer".
 2017-03-26T09:58:43Z DBCC results for 'ErrorLog'.
 2017-03-26T09:58:43Z There are 0 rows in 0 pages for object "ErrorLog".
 2017-03-26T09:58:43Z DBCC results for 'sys.fulltext_avdl_1077578877'.
 2017-03-26T09:58:43Z There are 5 rows in 1 pages for object "sys.fulltext_avdl_1077578877".
 2017-03-26T09:58:43Z DBCC results for 'Production.ProductListPriceHistory'.
 2017-03-26T09:58:43Z There are 395 rows in 3 pages for object "Production.ProductListPriceHistory".
 2017-03-26T09:58:43Z DBCC results for 'Person.Address'.
 2017-03-26T09:58:43Z There are 19614 rows in 344 pages for object "Person.Address".
 2017-03-26T09:58:43Z DBCC results for 'Sales.SpecialOfferProduct'.
 2017-03-26T09:58:43Z There are 538 rows in 3 pages for object "Sales.SpecialOfferProduct".
 2017-03-26T09:58:43Z DBCC results for 'Production.ProductModel'.
 2017-03-26T09:58:43Z There are 128 rows in 12 pages for object "Production.ProductModel".
 2017-03-26T09:58:43Z DBCC results for 'Person.AddressType'.
 2017-03-26T09:58:43Z There are 6 rows in 1 pages for object "Person.AddressType".
 2017-03-26T09:58:43Z DBCC results for 'Person.StateProvince'.
 2017-03-26T09:58:43Z There are 181 rows in 2 pages for object "Person.StateProvince".
 2017-03-26T09:58:43Z DBCC results for 'Production.ProductModelIllustration'.
 2017-03-26T09:58:43Z There are 7 rows in 1 pages for object "Production.ProductModelIllustration".
 2017-03-26T09:58:43Z DBCC results for 'AWBuildVersion'.
 2017-03-26T09:58:43Z There are 1 rows in 1 pages for object "AWBuildVersion".
 2017-03-26T09:58:43Z DBCC results for 'Production.ProductModelProductDescriptionCulture'.
 2017-03-26T09:58:43Z There are 762 rows in 4 pages for object "Production.ProductModelProductDescriptionCulture".
 2017-03-26T09:58:43Z DBCC results for 'Production.BillOfMaterials'.
 2017-03-26T09:58:43Z There are 2679 rows in 20 pages for object "Production.BillOfMaterials".
 2017-03-26T09:58:43Z DBCC results for 'sys.ifts_comp_fragment_610101214_4'.
 2017-03-26T09:58:43Z There are 351 rows in 2 pages for object "sys.ifts_comp_fragment_610101214_4".
 2017-03-26T09:58:43Z DBCC results for 'sys.ifts_comp_fragment_1077578877_3'.
 2017-03-26T09:58:43Z There are 524 rows in 3 pages for object "sys.ifts_comp_fragment_1077578877_3".
 2017-03-26T09:58:43Z DBCC results for 'Sales.Store'.
 2017-03-26T09:58:43Z There are 701 rows in 101 pages for object "Sales.Store".
 2017-03-26T09:58:43Z DBCC results for 'Production.ProductPhoto'.
 2017-03-26T09:58:43Z There are 101 rows in 50 pages for object "Production.ProductPhoto".
 2017-03-26T09:58:43Z DBCC results for 'sys.ifts_comp_fragment_1589580701_5'.
 2017-03-26T09:58:43Z There are 1324 rows in 9 pages for object "sys.ifts_comp_fragment_1589580701_5".
 2017-03-26T09:58:43Z DBCC results for 'Production.ProductProductPhoto'.
 2017-03-26T09:58:43Z There are 504 rows in 2 pages for object "Production.ProductProductPhoto".
 2017-03-26T09:58:43Z DBCC results for 'Production.TransactionHistory'.
 2017-03-26T09:58:43Z There are 113443 rows in 792 pages for object "Production.TransactionHistory".
 2017-03-26T09:58:43Z DBCC results for 'Production.ProductReview'.
 2017-03-26T09:58:43Z There are 4 rows in 2 pages for object "Production.ProductReview".
 2017-03-26T09:58:43Z DBCC results for 'Person.BusinessEntity'.
 2017-03-26T09:58:43Z There are 20777 rows in 96 pages for object "Person.BusinessEntity".
 2017-03-26T09:58:43Z DBCC results for 'Production.TransactionHistoryArchive'.
 2017-03-26T09:58:43Z There are 89253 rows in 624 pages for object "Production.TransactionHistoryArchive".
 2017-03-26T09:58:43Z DBCC results for 'Production.ProductSubcategory'.
 2017-03-26T09:58:43Z There are 37 rows in 1 pages for object "Production.ProductSubcategory".
 2017-03-26T09:58:43Z DBCC results for 'Person.BusinessEntityAddress'.
 2017-03-26T09:58:43Z There are 19614 rows in 110 pages for object "Person.BusinessEntityAddress".
 2017-03-26T09:58:43Z DBCC results for 'member'.
 2017-03-26T09:58:43Z There are 6 rows in 1 pages for object "member".
 2017-03-26T09:58:43Z DBCC results for 'Purchasing.ProductVendor'.
 2017-03-26T09:58:43Z There are 460 rows in 5 pages for object "Purchasing.ProductVendor".
 2017-03-26T09:58:43Z DBCC results for 'Person.BusinessEntityContact'.
 2017-03-26T09:58:43Z There are 909 rows in 6 pages for object "Person.BusinessEntityContact".
 2017-03-26T09:58:43Z DBCC results for 'Production.UnitMeasure'.
 2017-03-26T09:58:43Z There are 38 rows in 1 pages for object "Production.UnitMeasure".
 2017-03-26T09:58:43Z DBCC results for 'Purchasing.Vendor'.
 2017-03-26T09:58:43Z There are 104 rows in 2 pages for object "Purchasing.Vendor".
 2017-03-26T09:58:43Z DBCC results for 'Person.ContactType'.
 2017-03-26T09:58:43Z There are 20 rows in 1 pages for object "Person.ContactType".
 2017-03-26T09:58:43Z DBCC results for 'Sales.CountryRegionCurrency'.
 2017-03-26T09:58:43Z There are 109 rows in 1 pages for object "Sales.CountryRegionCurrency".
 2017-03-26T09:58:43Z DBCC results for 'Person.CountryRegion'.
 2017-03-26T09:58:43Z There are 238 rows in 2 pages for object "Person.CountryRegion".
 2017-03-26T09:58:43Z DBCC results for 'Production.WorkOrder'.
 2017-03-26T09:58:43Z There are 72591 rows in 528 pages for object "Production.WorkOrder".
 2017-03-26T09:58:43Z DBCC results for 'Purchasing.PurchaseOrderDetail'.
 2017-03-26T09:58:43Z There are 8845 rows in 64 pages for object "Purchasing.PurchaseOrderDetail".
 2017-03-26T09:58:43Z DBCC results for 'Sales.CreditCard'.
 2017-03-26T09:58:43Z There are 19118 rows in 187 pages for object "Sales.CreditCard".
 2017-03-26T09:58:43Z DBCC results for 'Production.Culture'.
 2017-03-26T09:58:43Z There are 8 rows in 1 pages for object "Production.Culture".
 2017-03-26T09:58:43Z DBCC results for 'Production.WorkOrderRouting'.
 2017-03-26T09:58:43Z There are 67131 rows in 696 pages for object "Production.WorkOrderRouting".
 2017-03-26T09:58:43Z DBCC results for 'Sales.Currency'.
 2017-03-26T09:58:43Z There are 105 rows in 1 pages for object "Sales.Currency".
 2017-03-26T09:58:43Z DBCC results for 'Purchasing.PurchaseOrderHeader'.
 2017-03-26T09:58:43Z There are 4012 rows in 42 pages for object "Purchasing.PurchaseOrderHeader".
 2017-03-26T09:58:43Z DBCC results for 'Sales.CurrencyRate'.
 2017-03-26T09:58:43Z There are 13532 rows in 96 pages for object "Sales.CurrencyRate".
 2017-03-26T09:58:43Z DBCC results for 'Sales.Customer'.
 2017-03-26T09:58:43Z There are 19820 rows in 121 pages for object "Sales.Customer".
 2017-03-26T09:58:43Z DBCC results for 'HumanResources.Department'.
 2017-03-26T09:58:43Z There are 16 rows in 1 pages for object "HumanResources.Department".
 2017-03-26T09:58:43Z DBCC results for 'Production.Document'.
 2017-03-26T09:58:43Z There are 13 rows in 1 pages for object "Production.Document".
 2017-03-26T09:58:43Z DBCC results for 'Sales.SalesOrderDetail'.
 2017-03-26T09:58:43Z There are 121317 rows in 1237 pages for object "Sales.SalesOrderDetail".
 2017-03-26T09:58:43Z DBCC results for 'Person.EmailAddress'.
 2017-03-26T09:58:43Z There are 19972 rows in 249 pages for object "Person.EmailAddress".
 2017-03-26T09:58:43Z DBCC results for 'HumanResources.Employee'.
 2017-03-26T09:58:43Z There are 290 rows in 7 pages for object "HumanResources.Employee".
 2017-03-26T09:58:43Z DBCC results for 'Sales.SalesOrderHeader'.
 2017-03-26T09:58:43Z There are 31465 rows in 685 pages for object "Sales.SalesOrderHeader".
 2017-03-26T09:58:43Z DBCC results for 'HumanResources.EmployeeDepartmentHistory'.
 2017-03-26T09:58:43Z There are 296 rows in 2 pages for object "HumanResources.EmployeeDepartmentHistory".
 2017-03-26T09:58:43Z DBCC results for 'HumanResources.EmployeePayHistory'.
 2017-03-26T09:58:43Z There are 316 rows in 2 pages for object "HumanResources.EmployeePayHistory".
 2017-03-26T09:58:43Z DBCC results for 'Sales.SalesOrderHeaderSalesReason'.
 2017-03-26T09:58:43Z There are 27647 rows in 86 pages for object "Sales.SalesOrderHeaderSalesReason".
 2017-03-26T09:58:43Z DBCC results for 'Sales.SalesPerson'.
 2017-03-26T09:58:43Z There are 17 rows in 1 pages for object "Sales.SalesPerson".
 2017-03-26T09:58:43Z DBCC results for 'Production.Illustration'.
 2017-03-26T09:58:43Z There are 5 rows in 1 pages for object "Production.Illustration".
 2017-03-26T09:58:43Z DBCC results for 'HumanResources.JobCandidate'.
 2017-03-26T09:58:43Z There are 13 rows in 11 pages for object "HumanResources.JobCandidate".
 2017-03-26T09:58:43Z DBCC results for 'Production.Location'.
 2017-03-26T09:58:43Z There are 14 rows in 1 pages for object "Production.Location".
 2017-03-26T09:58:43Z DBCC results for 'Person.Password'.
 2017-03-26T09:58:43Z There are 19972 rows in 235 pages for object "Person.Password".
 2017-03-26T09:58:43Z DBCC results for 'Sales.SalesPersonQuotaHistory'.
 2017-03-26T09:58:43Z There are 163 rows in 2 pages for object "Sales.SalesPersonQuotaHistory".
 2017-03-26T09:58:43Z DBCC results for 'Person.Person'.
 2017-03-26T09:58:43Z There are 19972 rows in 3809 pages for object "Person.Person".
 2017-03-26T09:58:43Z DBCC results for 'Sales.SalesReason'.
 2017-03-26T09:58:43Z There are 10 rows in 1 pages for object "Sales.SalesReason".
 2017-03-26T09:58:43Z DBCC results for 'Sales.SalesTaxRate'.
 2017-03-26T09:58:43Z There are 29 rows in 1 pages for object "Sales.SalesTaxRate".
 2017-03-26T09:58:43Z DBCC results for 'Sales.PersonCreditCard'.
 2017-03-26T09:58:43Z There are 19118 rows in 60 pages for object "Sales.PersonCreditCard".
 2017-03-26T09:58:43Z DBCC results for 'Person.PersonPhone'.
 2017-03-26T09:58:43Z There are 19972 rows in 148 pages for object "Person.PersonPhone".
 2017-03-26T09:58:43Z DBCC results for 'Sales.SalesTerritory'.
 2017-03-26T09:58:43Z There are 10 rows in 1 pages for object "Sales.SalesTerritory".
 2017-03-26T09:58:43Z DBCC results for 'Person.PhoneNumberType'.
 2017-03-26T09:58:43Z There are 3 rows in 1 pages for object "Person.PhoneNumberType".
 2017-03-26T09:58:43Z DBCC results for 'Production.Product'.
 2017-03-26T09:58:43Z There are 504 rows in 13 pages for object "Production.Product".
 2017-03-26T09:58:43Z DBCC results for 'sys.queue_messages_1977058079'.
 2017-03-26T09:58:43Z There are 0 rows in 0 pages for object "sys.queue_messages_1977058079".
 2017-03-26T09:58:43Z DBCC results for 'sys.queue_messages_2009058193'.
 2017-03-26T09:58:43Z There are 0 rows in 0 pages for object "sys.queue_messages_2009058193".
 2017-03-26T09:58:43Z DBCC results for 'Production.vProductAndDescription'.
 2017-03-26T09:58:43Z There are 1764 rows in 70 pages for object "Production.vProductAndDescription".
 2017-03-26T09:58:43Z DBCC results for 'sys.queue_messages_2041058307'.
 2017-03-26T09:58:43Z There are 0 rows in 0 pages for object "sys.queue_messages_2041058307".
 2017-03-26T09:58:43Z DBCC results for 'sys.filestream_tombstone_2073058421'.
 2017-03-26T09:58:43Z There are 0 rows in 0 pages for object "sys.filestream_tombstone_2073058421".
 2017-03-26T09:58:43Z DBCC results for 'sys.syscommittab'.
 2017-03-26T09:58:43Z There are 0 rows in 0 pages for object "sys.syscommittab".
 2017-03-26T09:58:43Z DBCC results for 'Sales.SalesTerritoryHistory'.
 2017-03-26T09:58:43Z There are 17 rows in 1 pages for object "Sales.SalesTerritoryHistory".
 2017-03-26T09:58:43Z DBCC results for 'sys.filetable_updates_2105058535'.
 2017-03-26T09:58:43Z There are 0 rows in 0 pages for object "sys.filetable_updates_2105058535".
 2017-03-26T09:58:43Z DBCC results for 'sys.plan_persist_query_text'.
 2017-03-26T09:58:43Z There are 0 rows in 0 pages for object "sys.plan_persist_query_text".
 2017-03-26T09:58:43Z DBCC results for 'Person.vStateProvinceCountryRegion'.
 2017-03-26T09:58:43Z There are 181 rows in 2 pages for object "Person.vStateProvinceCountryRegion".
 2017-03-26T09:58:43Z DBCC results for 'sys.plan_persist_query'.
 2017-03-26T09:58:43Z There are 0 rows in 0 pages for object "sys.plan_persist_query".
 2017-03-26T09:58:43Z CHECKDB found 0 allocation errors and 0 consistency errors in database 'AdventureWorks2014'.
 2017-03-26T09:58:43Z DBCC execution completed. If DBCC printed error messages, contact your system administrator.
2017-03-26T09:58:44Z Database [AdventureWorks2014] integrity check done in 00:00:21.7554891 [hh:mm:ss.ddd].
2017-03-26T09:58:44Z Database [AdventureWorks2014] integrity check is OK.

As you can see it the last line, the integrity for the database is OK which means you are assured to recover this database if anythings happens. It will also alert you if not OK, This can help you to alert the database administrator.

Disclaimer: The scripts can be used at your own risk, I am not the best scripter in the world and I am sure some of you can actually improve these scripts for your environment. These scripts are created as an example by myself and not by Arcserve and therefore are not supported by Arcserve!
Download UDP: http://okt.to/eXmslE
Arcserve High Availability Live Webcast: Every Tuesday, 10:00 BST: Register: http://okt.to/mR4fEN
Arcserve UDP Live Demo: Every Friday, 10:00 BST: Register: http://okt.to/fALlGx

 

Please follow and like vDutchy:
LinkedIn
FACEBOOK
Google+
Google+
http://vdutchy.com/sql-server-assured-recovery-test/
RSS
Follow by Email

Leave a Reply