How to Audit Backups on Multiple Servers with Powershell?

# Step 1 - use the following script, saving it as auditbackups.ps1 in C:\Users\XU-Acer\Documents>

$a= "Localhost","Xu-Acer-PC\I2"

$a | % `

     {

        $ServerName = $_;

        cd sqlserver:\

        # I have two instances on a single box, so the path is different

        If ($ServerName -eq "localhost" )

        {

            cd sql\$ServerName\default\databases;

                          

         

          <##### If you really want to use Powershell instead of SQL Server to create a table, see below:

            $ns = 'Microsoft.SqlServer.Management.Smo'

            $svr = new-object ("$ns.Server") $inst

            #Create reusable datatype objects

            $dtint = [Microsoft.SqlServer.Management.Smo.Datatype]::Int

            $dtvchar100 = [Microsoft.SqlServer.Management.Smo.Datatype]::NVarChar(100)

            $dtdatetm = [Microsoft.SqlServer.Management.Smo.Datatype]::DateTime

            #Reference your database.

            $db = $svr.Databases["MyTestDB"]

            #Create the table in the dbo schema

            $tbcomp = new-object ("$ns.Table") ($db, "BackupInfo2", "dbo")

            #Create the ServerName column

            $colconame = new-object ("$ns.Column") ($tbcomp, "ServerName", $dtvchar100)

            $colconame.Nullable = $false

            $tbcomp.Columns.Add($colconame)

       

            #Create the LastFullDate column

            $colconame = new-object ("$ns.Column") ($tbcomp, "LastFullDate", $dtvchar100)

            $colconame.Nullable = $false

            $tbcomp.Columns.Add($colconame)

            #Create the LastDiffDate column

            $colconame = new-object ("$ns.Column") ($tbcomp, "LastDiffDate", $dtvchar100)

            $colconame.Nullable = $false

            $tbcomp.Columns.Add($colconame)

            #Create the LastLogDate column

            $colconame = new-object ("$ns.Column") ($tbcomp, "LastLogDate", $dtvchar100)

            $colconame.Nullable = $false

            $tbcomp.Columns.Add($colconame) 

      

            #Create the ExecutionTime column

            $colconame = new-object ("$ns.Column") ($tbcomp, "ExecutionTime", $dtdatetm)

            $colconame.Nullable = $false

            $tbcomp.Columns.Add($colconame)            

            #Create the table

            $tbtcomp.Create();

           

            Done with the table creation ####################>

        }

        Else

        {

            cd sql\$ServerName\databases;

       

        }

                                     

    

        $DBs=Dir;

        $DBs | `

                 % {

                     $LastBackupDate=$_.LastBackupDate;

                     $LastDifferentialDate=$_.LastDifferentialBackupDate;

                     $LastLogDate=$_.LastLogBackupDate;

                     $ExecutionTime= Get-Date;

                     Invoke-Sqlcmd -ServerInstance localhost -Database "MyTestDB" -Query "Insert BackupInfo Select '$ServerName','$LastBackupDate', '$LastDifferentialDate','$LastLogDate','$ExecutionTime'" -suppressprovidercontextwarning

                     "Processing Server: "+ $ServerName + " Database: " +$_.Name + " === Done!!!"

                    

                     #If you use PS for the table creation, use the code below:

                     #Invoke-Sqlcmd -ServerInstance localhost -Database "MyTestDB" -Query "Insert BackupInfo2 Select '$ServerName','$LastBackupDate', '$LastDifferentialDate','$LastLogDate','$ExecutionTime'" -suppressprovidercontextwarning

                   

                    }

                    cd C:\Users\XU-Acer\Documents

     }

# Step 2 - execute the code above from the script file location:

PS C:\Users\XU-Acer\Documents> .\auditbackups.ps1