Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
1 2 3 4 5 6 7 | 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.
1 2 3 4 5 | $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.
