By: Richard Vantrease | Comments (1) | Related: > 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
- Add error handling - Check out this link: Hey, Scripting Guy! How Can I Use Try/Catch/Finally in Windows PowerShell?
- Add reporting if a DBCC error is found
About the author
Richard Vantrease is a lead SQL Server DBA for a large company. His specialties include architecture, the data engine and automation.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
