Summary: Microsoft Scripting Guy, Ed Wilson, shows how to use the SQL Server 2012 provider for Windows PowerShell to easily back up all databases on an instance.

Microsoft Scripting Guy, Ed Wilson, is here. I am still playing around with SQL Server 2012, and, today, I want to check on my backups by using Windows PowerShell.

Note   This is the fourth article in a series of articles discussing using Windows PowerShell to manage SQL Server 2012. You should read the first three articles in this series prior to reading this article. In the first article, I introduced the SQL Server 2012 provider for Windows PowerShell. In the second article, I talked about the properties and methods that become available once you store the instance in a variable. In the third article, I talked about using Windows PowerShell to work with SQL Server database objects.

The easy way to find the last backup of a SQL database

It is extremely easy to find the last backup dates of a SQL Server 2012 database by using the Windows PowerShell provider. I enter my db drive, type dir, and then choose the appropriate properties.

Note   The db drive refers to the databases collection node and was created yesterday.

The command and associated output are shown here.

[sql1]: PS db:\> dir | select name, *backupdate | fl

Name                       : Northwind

LastBackupDate             : 10/15/2012 7:48:26 PM

LastDifferentialBackupDate : 1/1/0001 12:00:00 AM

LastLogBackupDate          : 10/15/2012 8:09:39 PM

Name                       : pubs

LastBackupDate             : 10/15/2012 7:48:26 PM

LastDifferentialBackupDate : 1/1/0001 12:00:00 AM

LastLogBackupDate          : 10/15/2012 8:09:39 PM

Name                       : ReportServer$INSTANCE_A

LastBackupDate             : 10/15/2012 7:48:26 PM

LastDifferentialBackupDate : 1/1/0001 12:00:00 AM

LastLogBackupDate          : 10/15/2012 8:09:40 PM

Name                       : ReportServer$INSTANCE_ATempDB

LastBackupDate             : 10/15/2012 7:48:26 PM

LastDifferentialBackupDate : 1/1/0001 12:00:00 AM

LastLogBackupDate          : 1/1/0001 12:00:00 AM

Back up the database

To back up every database on my specific instance (the one to which the db drive points), I use the get-ChildItem cmdlet (dir is the alias) and pipe the results to the Backup-SqlDatabase cmdlet as shown here.

dir | Backup-SqlDatabase

To back up the transaction log for a specific database, I use the Backup-SqlDatabase cmdlet, but specify a BackupAction of log. I also need to specify the serverInstance as well as the database name. The following command backs up the transaction log for the pubs database.

Backup-SqlDatabase -serverInstance sql1\Instance_a -BackupAction log -Database pubs

If I want to back up the transaction logs for all of the databases on the Instance_a instance of my sql1 server, I can use the Get-ChildItem cmdlet to obtain a listing of all of the databases, and then pipe the results to the Foreach-Object cmdlet.

Note   Unfortunately, the Backup-SqlDatabase cmdlet does not appear to accept piped input when backing up transaction logs.

The following code backs up the transaction logs for all databases that do not have a simple recovery method (an expected error generates for databases in simple recovery mode).

$db = dir

$db.name |

foreach {Backup-SqlDatabase -serverInstance sql1\Instance_a -BackupAction log -Database $_ -ea 0}

To check the last backup date and recovery model, I pipe the database objects to the Select-Object cmdlet and display the RecoveryModel and the LastBackupDate for the database as shown here.

[sql1]: PS db:\> dir | select name, recoverymodel, lastbackupdate

Name                                       RecoveryModel LastBackupDate

—-                                       ————- ————–

Northwind                                           Full 10/15/2012 7:48:26 PM

pubs                                                Full 10/15/2012 7:48:26 PM

ReportServer$INSTANCE_A                             Full 10/15/2012 7:48:26 PM

ReportServer$INSTANCE_ATe…                      Simple 10/15/2012 7:48:26 PM

To view the backup set files, I use the EnumBackupSetFiles method from the specific database that I am interested in working. The following code retrieves the pubs database object, and then enumerates the backup set files.

$pubs = Get-Item pubs

$pubs.EnumBackupSetFiles()

The code and the output associated with the code are shown here.

That’s about it for messing around with the SQL Server 2012 database backup. Join me tomorrow when I will kick off a new week and show you the easy way to work with the SQL Error Log.

I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

Ed Wilson, Microsoft Scripting Guy