Learn more about SQL Server tools

   
   















































Check SQL Server Database Consistency and Backups Without Impacting Performance

MSSQLTips author Richard Vantrease By:   |   Read Comments (1)   |   Related Tips: More > PowerShell
Problem

Two key responsibilities of a DBA are ensuring that the databases do not get damaged, and that they can be recovered if they do get damaged. If a database does get damaged, detecting the damage as quickly as possible is very important. DBCC is our tool for detecting database damage. Backups are our tools for recovering damaged databases. The problem is that DBCC is a fairly intensive operation on a SQL Server. In addition, sometimes database backups are not good when we try to restore them. How do we run DBCC without impacting the performance of our production databases, and how do we ensure that our backups are usable when we need them.

Solution

In the article Minimize performance impact of SQL Server DBCC CHECKDB Robert Pearl mentions running DBCC on another server. This has the added benefit of testing backups as well. The backups are restored on another server, then DBCC is run against them. This way we know for certain that our full backups are in good condition, and then when we run DBCC, we know that the database is also OK.

In this article, I am going to walk you through one method of running DBCC checks on another server. My solution is PowerShell centric. In a previous tip Monitor Disk Space Using PowerShell, I described why I would use PowerShell as opposed to sticking with T-SQL.

Summary of Process

  • Backup databases to a folder available via a fileshare
  • Get a list of databases to check using DBCC
  • Step through the databases doing the following
    • Restore the latest full backup of each database
    • Run DBCC CHECKDB against the database
    • Capture DBCC results
    • Drop the database and repeat the process

Scenario

My scenario is simple. I have a production SQL Server; PRODSQL. I also have a second server that is not production DBCCSQL. This server is where I will restore the databases from PRODSQL and run DBCC against them.

Backup Configuration

Since I will be restoring backups from PRODSQL to DBCCSQL, we need to make sure the PRODSQL backups are available from DBCCSQL. The easiest way to do this is to make sure they are on a fileshare. In an ideal environment, backups are not written to the server where the databases are, but instead written to a high-speed fileshare on another server. This provides two benefits; it protects backups if the server has a catastrophic failure and it provides a performance boost by not tying up the disk IO path during both the read of the database, and the write of the backup.

Get a list of databases

To get a list of databases, we will open a connection to the production server using PowerShell. The we will run a SQL query to get a list of databases. We will be calling SQL Server Management Objects (SMO) from PowerShell. See my previous tip Monitor Disk Space Using PowerShell which describes how to use PowerShell. Arshad Ali describes SMO in Getting started with SQL Server Management Objects. To use SMO from PowerShell, we will need to load .Net assemblies. I will open up a PowerShell console on DBCCSQL and enter the following command.

[system.Reflection.Assembly]::LoadWithPartialName("Microsoft.SQLServer.Smo")
 

Next we will connect to the production SQL Server PRODSQL. Enter the following PowerShell command. It will create a variable called $Server that is populated with a SMO Server Object.

$server = New-Object Microsoft.SqlServer.Management.Smo.Server "PRODSQL"
 

To get a list of databases, we can use the $Server object, which contains a property called "Databases". Databases is actually an array of type SMO Database Object. To show a list of the databases, type either of the following commands in PowerShell:

$Server.Databases | format-table name -Auto

$Server.Databases | ft name -Auto
 

Both commands are the same, they take the databases property of the $Server object and pipe it to the format-table cmdlet.  The databases property is a collection of Database objects.  The first one uses the full cmdlet name, while the second one simply uses an alias for the cmdlet.

Step Through The Databases

Having a list of databases is nice, but what we really need to do is to step through the list of databases and perform an action with each one. Let's convert our code to go through each database one at a time. We will use PowerShell's foreach command. 

NOTE: I converted the command for getting the server to use a variable.  Where possible, hard-coded names should not be used in scripts.  Using variables makes it easer to later convert the scripts to be re-usable.

[system.Reflection.Assembly]::LoadWithPartialName("Microsoft.SQLServer.Smo")

$ServerName = 'PRODSQL'

$server = New-Object Microsoft.SqlServer.Management.Smo.Server $ServerName

foreach($Database in $Server.Databases)
{
    "Processing database $($Database.Name):"
} 

Get The Most Recent Backup

Now that we have a list of databases, we need to find the most recent full database backup for each database. The query looks something like this:

SELECT TOP 1 
    bs.Server_Name,
    db.name AS DBName,
    bs.backup_finish_date,
    mf.physical_device_name
FROM [master].[dbo].[sysdatabases] db 
LEFT OUTER JOIN [msdb].[dbo].[backupset] bs 
    ON  bs.database_name = db.name 
    AND bs.type = 'D' 
JOIN msdb.dbo.backupmediaset ms
  on ms.media_set_id = bs.media_set_id
JOIN msdb.dbo.backupmediafamily mf
  on mf.media_set_id = ms.media_set_id 
WHERE db.name = ''
AND mf.physical_device_name LIKE '\\%'
ORDER BY bs.backup_finish_date desc 

Since we cannot guarantee that the path where the database files will be restored will match the original location, we will need to get a list of database files so that we can create MOVE statements for the restore. To get the list of database files, we will use the following T-SQL with the backup device name from the above query.

RESTORE FILELISTONLY FROM DISK = ''

OK, so let's put some of this together...

NOTE: The Server object $Server that we created earlier has a property called ConnectionContext which is a ServerConnection object that contains a method called ExecuteWithResults We can use this method to run SQL commands directly from PowerShell. ExecuteWithResults returns a .Net System.Data.DataSet object. This object contains a property called tables that is a collection of System.Data.Table objects. In our case it will only contain one table object. Because it is a collection, it will be object number [0].

One other thing I should mention here.  Single quoted and double quoted strings are treated differently in PowerShell.  Single qouted strings are literals, while double quoted strings use something called Variable Expansion.  This means that if you insert a variable in a double qouted string, it is expanded to show it's value instead of it's name. 

Example: 'The server name is $($Server.Name)' displays exactly as it is written

The server name is $($Server.Name)

"The server name is $($Server.Name)" displays as: The server name is PRODSQL

If double quotes are used, you can embed single quotes in the string like this: "The server name is '$($Server.Name)' " displays as: The server name is 'PRODSQL'

WHEW! I hope you got all that. Now finally, let's see what it looks like.

[system.Reflection.Assembly]::LoadWithPartialName("Microsoft.SQLServer.Smo")

$ServerName = 'PRODSQL'

$server = New-Object Microsoft.SqlServer.Management.Smo.Server $ServerName

foreach($Database in $Server.Databases)
{
    "Processing database $($Database.Name):"

    $CMD = "
    SELECT TOP 1 
        bs.Server_Name,
        db.name AS DBName,
        bs.backup_finish_date,
        mf.physical_device_name
    FROM [master].[dbo].[sysdatabases] db 
    LEFT OUTER JOIN [msdb].[dbo].[backupset] bs 
        ON  bs.database_name = db.name 
        AND bs.type = 'D' 
    JOIN msdb.dbo.backupmediaset ms
      on ms.media_set_id = bs.media_set_id
    JOIN msdb.dbo.backupmediafamily mf
      on mf.media_set_id = ms.media_set_id 
    WHERE db.name = '$($Database.Name)'
    AND mf.physical_device_name LIKE '\\%'
    ORDER BY bs.backup_finish_date desc" 

    $Backups = ($Server.ConnectionContext.ExecuteWithResults($CMD)).Tables[0]
  
    foreach($Backup in $Backups)
    {
        $CMD = "RESTORE FILELISTONLY FROM DISK = '$($Backup.physical_device_name)'"
    
        $DBFiles = ($Server.ConnectionContext.ExecuteWithResults($CMD)).Tables[0]
    
        #Let's go ahead and list the files
        $DBFiles | ft -Auto
    }
}

Now that we have a list of the backups and of each logical file for each database, it is time to construct the restore statements. The first part of the restore statement will be the same for each database. Each database will be restored as DBCC_Check_**DatabaseName**. The path for the backup file is already available from the above query. So we can make a string for the first part of the restore statement that will look like this:

$CMD = "
    RESTORE DATABASE [DBCC_Check_$($Backup.DBName)] 
    FROM  DISK = N'$($Backup.physical_device_name)' 
    WITH  FILE = 1,  
    NOUNLOAD,  
    STATS = 10"

Before we can finish out the restore statements, we will need to have the default path for database and log files. The default path for database and log files happens to be stored in properties of the Server object that we already have from the above code.  Unfortunately, we need the server object for the DBCCSQL server, not the PRODSQL server.  We can get to it by simply connecting to another server as follows:

$ServerName = 'DBCCSQL'
$DBCCServer = New-Object Microsoft.SqlServer.Management.Smo.Server $ServerName

[string]$DBCCData = $DBCCServer.DefaultFile
[string]$DBCCLog  = $DBCCServer.DefaultLog

"Default data directory = $DBCCData"
"Default log directory  = $DBCCLog"

Since a database can be made up of multiple files, and the path for the files might not stay the same from one server to the next it is important to customize the restore statement for each database. To do this, I will loop through all of the logical files in the backup set, and add a MOVE clause for each one to the restore statement created above.

foreach($File in $DBFiles)
{
    $LogicalName  = $File.LogicalName
    $PhysicalName = $File.PhysicalName
    $FileType     = $File.Type
  
    if($FileType = 'D')
    { 
        $FilePath = "$DBCCData\$($PhysicalName.Split('\')[-1])" 
    } else { 
        $FilePath = "$DBCCLog\$($PhysicalName.Split('\')[-1])" 
    }
    $CMD += "`r,MOVE N'$LogicalName' TO N'$FilePath'"
}

Finally, we need to restore the database, run the DBCC statements and then drop the database. Note that I change the StatementTimeout before the restore and DBCC statements. For larger databases, it is likely that the default statement timeout of 10 minutes will be exceeded.

$DBCCServer.ConnectionContext.StatementTimeout = 45000

$DBCCServer.ConnectionContext.ExecuteNonQuery($CMD)

$DBCCServer.ConnectionContext.ExecuteNonQuery('DBCC FREEPROCCACHE')
$DBCCServer.ConnectionContext.ExecuteNonQuery('DBCC FREESYSTEMCACHE(''All'')')
$DBCCServer.ConnectionContext.ExecuteNonQuery("DBCC CheckDB('DBCC_Check_$($DB.DatabaseName)')")
$DBCCServer.ConnectionContext.ExecuteNonQuery("DROP DATABASE [DBCC_Check_$($DB.DatabaseName)]")

The complete PowerShell script to restore the database and run DBCC CheckDB

NOTE: We will need to create a connection to the Restore/DBCC server in order to restore and check the databases.

[system.Reflection.Assembly]::LoadWithPartialName("Microsoft.SQLServer.Smo")

$ServerName = 'PRODSQL'

$DBCCServerName = 'DBCCSQL'

$Server = New-Object Microsoft.SqlServer.Management.Smo.Server $ServerName

$DBCCServer = New-Object Microsoft.SqlServer.Management.Smo.Server $DBCCServerName

[string]$DBCCData = $DBCCServer.DefaultFile
[string]$DBCCLog  = $DBCCServer.DefaultLog

$DBCCConn = $DBCCServer.ConnectionContext

foreach($Database in $Server.Databases)
{
    "Processing database $($Database.Name):"

    $CMD = "
    SELECT TOP 1 
        bs.Server_Name,
        db.name AS DBName,
        bs.backup_finish_date,
        mf.physical_device_name
    FROM [master].[dbo].[sysdatabases] db 
    LEFT OUTER JOIN [msdb].[dbo].[backupset] bs 
        ON  bs.database_name = db.name 
        AND bs.type = 'D' 
    JOIN msdb.dbo.backupmediaset ms
      on ms.media_set_id = bs.media_set_id
    JOIN msdb.dbo.backupmediafamily mf
      on mf.media_set_id = ms.media_set_id 
    WHERE db.name = '$($Database.Name)'
    AND mf.physical_device_name LIKE '\\%'
    ORDER BY bs.backup_finish_date desc" 

    $Backups = ($Server.ConnectionContext.ExecuteWithResults($CMD)).Tables[0]
  
    $DBCCConn.StatementTimeout = 45000

    foreach($Backup in $Backups)
    {
        $CMD = "RESTORE FILELISTONLY FROM DISK = '$($Backup.physical_device_name)'"
    
        $DBFiles = ($DBCCConn.ExecuteWithResults($CMD)).Tables[0]
    
        $CMD = "
            RESTORE DATABASE [DBCC_Check_$($Backup.DBName)] 
            FROM  DISK = N'$($Backup.physical_device_name)' 
            WITH  FILE = 1,  
            NOUNLOAD,  
            STATS = 10"

        foreach($File in $DBFiles)
        {
            $LogicalName  = $File.LogicalName
            $PhysicalName = $File.PhysicalName
            $FileType     = $File.Type
  
            if($FileType = 'D')
            { 
                $FilePath = "$($DBCCData)DBCC_$($PhysicalName.Split('\')[-1])" 
            } else { 
                $FilePath = "$($DBCCLog)DBCC_$($PhysicalName.Split('\')[-1])" 
            }
            $CMD += "`r,MOVE N'$LogicalName' TO N'$FilePath'"

        }
        $DBCCConn.ExecuteNonQuery($CMD)
        $DBCCConn.ExecuteNonQuery('DBCC FREEPROCCACHE')
        $DBCCConn.ExecuteNonQuery('DBCC FREESYSTEMCACHE(''All'')')
        $DBCCConn.ExecuteNonQuery("DBCC CheckDB('DBCC_Check_$($Database.Name)')")
        $DBCCConn.ExecuteNonQuery("DROP DATABASE [DBCC_Check_$($Database.Name)]")
    }
}

References

Next Steps


Last Update: 1/21/2014


About the author
MSSQLTips author Richard Vantrease
Richard Vantrease is a lead SQL Server DBA for a large company. His specialties include architecture, the data engine and automation.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Thursday, January 23, 2014 - 11:10:56 PM - manu Read The Tip

Thanks for sharing the code and a simple method of checking consistency.




 
Sponsor Information