Using powershell with SQL Server and Excel
In my current role as Database Admin, I check our servers each day. I want to check all the typical DBA stuff such as making sure backups were successful, that a DBCC has been run recently, that the data and log files still have room to breathe, etc.?
Doing this manually works well when you only have a few servers and/or databases to check, but gets really tedious as the numbers grow. Also, how do I actually show (for audit purposes, for example) that I actually checked them?
I decided to use Microsoft powershell automate the process. However, when I started trying to push data into excel, I found there don't seem to be many clear examples to be found. I guess the assumption must be that you either want to work with excel objects or sql smo, but never both at the same time.
Perhaps, you would also like to automate your morning routine and free up some time for other, more interesting DBA pursuits. If so, then this post is for you. Simply copy my code below, study it to make sure you know what it is doing, tweak it as you see fit and run it.
Here is the script:
# Here I create an Excel workbook with a sheet named "ServerData"
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $True
$Workbook = $Excel.Workbooks.Add()
$Sheet = $Workbook.activesheet
$Sheet.Name = "ServerData"
# Declaring a row variable and pointing to the first row
$row = 1
#Create column headers
$Sheet.Cells.Item($row,1) = "Server"
$Sheet.Cells.Item($row,1).Font.Bold = $True
$Sheet.Cells.Item($row,2) = "Database"
$Sheet.Cells.Item($row,2).Font.Bold = $True
$Sheet.Cells.Item($row,3) = "DB Status"
$Sheet.Cells.Item($row,3).Font.Bold = $True
$Sheet.Cells.Item($row,4) = "DB Owner"
$Sheet.Cells.Item($row,4).Font.Bold = $True
$Sheet.Cells.Item($row,5) = "DB Size"
$Sheet.Cells.Item($row,5).Font.Bold = $True
$Sheet.Cells.Item($row,6) = "Space Available"
$Sheet.Cells.Item($row,6).Font.Bold = $True
$Sheet.Cells.Item($row,7) = "Available PCT"
$Sheet.Cells.Item($row,7).Font.Bold = $True
$Sheet.Cells.Item($row,8) = "Last Full Backup"
$Sheet.Cells.Item($row,8).Font.Bold = $True
$Sheet.Cells.Item($row,9) = "Last Diff Backup"
$Sheet.Cells.Item($row,9).Font.Bold = $True
$Sheet.Cells.Item($row,10) = "Last Log Backup"
$Sheet.Cells.Item($row,10).Font.Bold = $True
$Sheet.Cells.Item($row,11) = "Last DBCC"
$Sheet.Cells.Item($row,11).Font.Bold = $True
$Sheet.Cells.Item($row,12) = "Data Name"
$Sheet.Cells.Item($row,12).Font.Bold = $True
$Sheet.Cells.Item($row,13) = "Data File"
$Sheet.Cells.Item($row,13).Font.Bold = $True
$sheet.Cells.Item($row,14) = "Data Size"
$Sheet.Cells.Item($row,14).Font.Bold = $True
$Sheet.Cells.Item($row,15) = "Data Used Space"
$Sheet.Cells.Item($row,15).Font.Bold = $True
$Sheet.Cells.Item($row,16) = "Log Name"
$Sheet.Cells.Item($row,16).Font.Bold = $True
$Sheet.Cells.Item($row,17) = "Log Size"
$Sheet.Cells.Item($row,17).Font.Bold = $True
$Sheet.Cells.Item($row,18) = "Log Used Space"
$Sheet.Cells.Item($row,18).Font.Bold = $True
$Sheet.Cells.Item($row,19) = "Log File"
$Sheet.Cells.Item($row,19).Font.Bold = $True
# I need a list of SQL Servers. I'll get them from a text file I created...
# For each server I find in the list, I need to create an object to query using SMO...
ForEach ($instance in Get-Content "C:\Users\charlies\Documents\__Projects\PowerShell\sqlservers.txt") {
?[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
?$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance
?# Get the list of databases on this server
?$dbs=$s.Databases
?# and for each database I find:
?ForEach ($db in $dbs) {
??# I don't really care about the system databases, so I'll ignore them by specifying that the DBID has to be greater than 4.
??if ($db.ID -gt 4) {
领英推荐
???$dbname?= $db.Name
???$edition = $db.DatabaseEngineEdition
???$dbowner = $db.Owner
???$dbsize?= $db.Size
???$dbspaceAvailable = $db.SpaceAvailable
???$dbLastFullBackup = $db.LastBackupDate
???$dbLastDiffBackup = $db.LastDifferentialBackupDate
???$dbLastLogBackup?= $db.LastLogBackupDate
???$dbLastGoodDBCC??= $db.LastGoodCheckDbTime
???# I'll report differently depending on whether the database is on-line or not...
???if (-not $db.IsAccessible){
????$row++
????$Sheet.Cells.Item($row,1) = $instance
????$Sheet.Cells.Item($row,2) = $dbname
????$Sheet.Cells.Item($row,3) = "OFFLINE"
????$Sheet.Cells.Item($row,4) = $dbowner
????$Sheet.Cells.Item($row,5) = $dbsize
????$Sheet.Cells.Item($row,6) = $dbspaceAvailable / 1024.
????$Sheet.Cells.item($row,7) = [math]::Round(((($dbspaceAvailable / 1024.)/$dbsize)*100),2)
????$Sheet.Cells.Item($row,8) = $dbLastFullBackup
????$Sheet.Cells.Item($row,9) = $dbLastDiffBackup
????$Sheet.Cells.Item($row,10) = $dbLastLogBackup
????$Sheet.Cells.Item($row,11) = $dbLastGoodDBCC
???}
???if ($db.IsAccessible) {
????$fileGroups = $db.FileGroups
????ForEach ($fg in $fileGroups) {
?????If ($fg) {
??????$row++
??????$Sheet.Cells.Item($row,1) = $instance
??????$Sheet.Cells.Item($row,2) = $dbname
??????$Sheet.Cells.Item($row,3) = "ONLINE"
??????$Sheet.Cells.Item($row,4) = $dbowner
??????$Sheet.Cells.Item($row,5) = $dbsize
??????$Sheet.Cells.Item($row,6) = $dbspaceAvailable / 1024.
??????$Sheet.Cells.item($row,7) = [math]::Round(((($dbspaceAvailable / 1024.)/$dbsize)*100),2)
??????$Sheet.Cells.Item($row,8) = $dbLastFullBackup
??????$Sheet.Cells.Item($row,9) = $dbLastDiffBackup
??????$Sheet.Cells.Item($row,10) = $dbLastLogBackup
??????$Sheet.Cells.Item($row,11) = $dbLastGoodDBCC
??????$mdfInfo = $fg.Files | Select Name, FileName, Size, UsedSpace
??????$Sheet.Cells.Item($row,12) = $mdfInfo.Name
??????$Sheet.Cells.Item($row,13) = $mdfInfo.FileName
??????$Sheet.Cells.Item($row,14) = $mdfInfo.size
??????$Sheet.Cells.Item($row,15) = $mdfInfo.UsedSpace
??????$logInfo = $db.LogFiles | Select Name, FileName, Size, UsedSpace
??????$Sheet.Cells.Item($row,16) = $logInfo.Name
??????$Sheet.Cells.Item($row,17) = $logInfo.Size
??????$Sheet.Cells.Item($row,18) = $logInfo.UsedSpace
??????$Sheet.Cells.Item($row,19) = $logInfo.FileName
?????}
????}
???}
??}
?}
}
# Finally, I need to format the output as a table.
$Sheet.UsedRange.EntireColumn.AutoFit()
$ListObject = $Sheet.ListObjects.Add([Microsoft.Office.Interop.Excel.XlListObjectSourceType]::xlSrcRange, $Sheet.UsedRange, $null ,[Microsoft.Office.Interop.Excel.XlYesNoGuess]::xlYes)
$ListObject.TableStyle = "TableStyleMedium2"