Learning Last Backup Informations In SQL Server With PowerShell

Çağlar Özenç

3 years ago

In today’s article, I will tell you about Learning Last Backup Informations In SQL Server With PowerShell.

This article highlights how to use Windows PowerShell to get database properties using SMO ( SQL Server Management Object ).

Notice how easy it is to check database properties using fairly common syntax.

One of the challenges I faced when starting a SQL Server DBA was checking the last backup date of a database.

One way to do this is to find out which tables in the MSDB database contain records from the backup history.

What’s really tricky here is the fact that you’ll have to look at the tables and their related relationships that MSDB doesn’t have.

You should trust what he has to say at SQL Server Books Online.

Also, the MSDB database will only contain records for databases with backups. What about those who don’t have backups?

You can check all the backups taken on the server via SQL Server Management Studio with the script below.

SELECT
T1.Name AS DatabaseName ,
COALESCE ( CONVERT ( VARCHAR ( 12 ), MAX ( T2.backup_finish_date ), 101 ), ‘Not Yet Taken’ ) ASLastBackUpTaken
FROM sys.sysdatabases T1 LEFT OUTER JOIN msdb.dbo.backupset T2
ON T2.database_name = T1.name
GROUP BY T1.Name
ORDER BY T1.Name

SELECT

T1.Name AS DatabaseName ,

COALESCE ( CONVERT ( VARCHAR ( 12 ), MAX ( T2.backup_finish_date ), 101 ), ‘Not Yet Taken’ ) ASLastBackUpTaken

FROM sys.sysdatabases T1 LEFT OUTER JOIN msdb.dbo.backupset T2

ON T2.database_name = T1.name

GROUP BY T1.Name

ORDER BY T1.Name

Using Powershell, you can check your backups using the code block below.

$instance=”SQL_SERVER_INSTANCE_NAME“;
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’)| out-null
$s = new-object (‘Microsoft.SqlServer.Management.Smo.Server’) $instance
$dbs = $s.Databases
$dbs | select Name,LastBackupDate, LastLogBackupDate | format-table -autosize

$instance=”SQL_SERVER_INSTANCE_NAME“;

[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’)| out-null

$s = new-object (‘Microsoft.SqlServer.Management.Smo.Server’) $instance

$dbs = $s.Databases

$dbs | select Name,LastBackupDate, LastLogBackupDate | format-table -autosize

The only thing to note here are the last two lines – the line that creates an instance of the database object and displays and formats several database object properties.

The first few lines will be the same for any PowerShell script that will access SQL Server using SMO.