Sql-server – SQL Server backup status report

availability-groupsbackupsql serversql-server-2012

We have 30-40 SQL server instances in different servers (2012 with AlwaysOn). Is there any kind of report that can be made which captures the daily backup status and sends a mail, perhaps, or at least can be appended in an Excel sheet manually and then sent later? Any help would be appreciated.

Best Answer

There are various ways to build a report with that information. You could do this with PowerShell or TSQL using linked servers or SSIS.

There are sample scripts on the web and this forums site for pulling backup status for a server or given database. Just need to come up with method to pull it all together for a central report in your environment.

Update

It will depend on what you mean by daily backup status. I will also assume this is going to be a manual process, but for the most part I would generally follow the steps below, for starters:

Use PowerShell:

  1. Build T-SQL query with desired dataset I wanted for a single server.
  2. Create PowerShell script that has function to execute that query (use Invoke-SqlCmd or System.Data.SqlClient)
  3. Export that data to CSV (e.g. $reportdata | Export-Csv -path MyFile.csv -Delimiter "|")
  4. Import that CSV file into Excel

I wrote this function sometime back for an SSIS package to provide to a client as an example, but it should work in your case of just passing in the query to execute in order to get an Excel file. It works the same as if you went into Excel to the Data ribbon and selected "From text" to get external data:

function SqlTo-Excel {
    [cmdletbinding()]
    param (
        [string]$server,
        [string]$database,
        [string]$csvFile,
        [string]$excelFile,
        $query,
        [string]$delimiter = '|'
    )

    if (Test-Path $excelFile)
    {
        Remove-Item $excelFile -Force
    }
    if (Test-Path $csvFile)
    {
        Remove-Item $csvFile -Force
    }

    $sqlcn = New-Object System.Data.SqlClient.SqlConnection
    $sqlcn.ConnectionString = "Data Source=$($server);Database=$($database);Integrated Security=SSPI;"
    $sqlcn.Open()

    $sqlcmd = $sqlcn.CreateCommand()
    $sqlcmd.CommandText = $query
    $sqldata = $sqlcmd.ExecuteReader()
    $dt = New-Object System.Data.DataTable
    $dt.Load($sqldata)
    $dt | Export-Csv -Path $csvFile -NoClobber -NoTypeInformation -Delimiter "|"
    $sqlcn.Close()

    $xl = New-Object -ComObject "Excel.Application"
    $xl.Visible = $false
    $wb = $xl.Workbooks.Add()
    $ws = $wb.WorkSheets.Item(1)

    $txtConnector = ("TEXT;" + $csvFile)
    $connector = $ws.QUeryTables.Add($txtConnector,$ws.Range("A1"))
    $qTable = $ws.QueryTables.Item($connector.name)

    $qTable.TextFileOtherDelimiter = $delimiter

    $qTable.TextFileParseType = 1
    $qTable.TextFileColumnDataTypes = ,2 * $ws.Cells.Columns.Count
    $qTable.AdjustColumnWidth = 1

    $qTable.Refresh()
    $qTable.Delete()

    $wb.SaveAs($excelFile,51)
    $xl.Quit()

    [System.Runtime.InteropServices.Marshal]::ReleaseComObject($xl)
    [System.Runtime.InteropServices.Marshal]::ReleaseComObject($wb)
    [System.Runtime.InteropServices.Marshal]::ReleaseComObject($ws)

    Remove-Variable xl,wb,ws,qTable,connector,txtConnector,q,csvFile,excelFile
}

Use T-SQL

This could go anywhere from just using Registered Servers in SSMS to get the results for all servers at one time; or from a single server that can talk to them all creating linked servers to each one.

To automate a T-SQL option only, would require a bit more if you want to email it, but you could dump all the data into a single table and then BCP it out to Excel possibly. I am not much for using BCP when I have PowerShell to get it straight into the format I want.

Related Question