{"id":4794,"date":"2012-10-21T00:01:00","date_gmt":"2012-10-21T00:01:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2012\/10\/21\/weekend-scripter-easily-back-up-all-sql-server-databases-by-using-powershell\/"},"modified":"2012-10-21T00:01:00","modified_gmt":"2012-10-21T00:01:00","slug":"weekend-scripter-easily-back-up-all-sql-server-databases-by-using-powershell","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/weekend-scripter-easily-back-up-all-sql-server-databases-by-using-powershell\/","title":{"rendered":"Weekend Scripter: Easily Back Up All SQL Server Databases by Using PowerShell"},"content":{"rendered":"

Summary<\/b>: 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.<\/p>\n

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.<\/p>\n

Note<\/b>   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<\/a>, I introduced the SQL Server 2012 provider for Windows PowerShell. In the second article<\/a>, <\/i>I talked about the properties and methods that become available once you store the instance in a variable. In the third article<\/a>, I talked about using Windows PowerShell to work with SQL Server database objects.<\/p>\n

The easy way to find the last backup of a SQL database<\/h2>\n

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<\/b> <\/i>drive, type dir<\/b>, <\/i>and then choose the appropriate properties.<\/p>\n

Note<\/b>   The db<\/b> <\/i>drive refers to the databases collection node and was created yesterday.<\/p>\n

The command and associated output are shown here.<\/p>\n

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

Name                       : Northwind<\/p>\n

LastBackupDate             : 10\/15\/2012 7:48:26 PM<\/p>\n

LastDifferentialBackupDate : 1\/1\/0001 12:00:00 AM<\/p>\n

LastLogBackupDate          : 10\/15\/2012 8:09:39 PM<\/p>\n

 <\/p>\n

Name                       : pubs<\/p>\n

LastBackupDate             : 10\/15\/2012 7:48:26 PM<\/p>\n

LastDifferentialBackupDate : 1\/1\/0001 12:00:00 AM<\/p>\n

LastLogBackupDate          : 10\/15\/2012 8:09:39 PM<\/p>\n

 <\/p>\n

Name                       : ReportServer$INSTANCE_A<\/p>\n

LastBackupDate             : 10\/15\/2012 7:48:26 PM<\/p>\n

LastDifferentialBackupDate : 1\/1\/0001 12:00:00 AM<\/p>\n

LastLogBackupDate          : 10\/15\/2012 8:09:40 PM<\/p>\n

 <\/p>\n

Name                       : ReportServer$INSTANCE_ATempDB<\/p>\n

LastBackupDate             : 10\/15\/2012 7:48:26 PM<\/p>\n

LastDifferentialBackupDate : 1\/1\/0001 12:00:00 AM<\/p>\n

LastLogBackupDate          : 1\/1\/0001 12:00:00 AM<\/p>\n

Back up the database<\/h2>\n

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

dir | Backup-SqlDatabase<\/p>\n

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

Backup-SqlDatabase -serverInstance sql1\\Instance_a -BackupAction log -Database pubs<\/p>\n

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

Note<\/b>   Unfortunately, the Backup-SqlDatabase<\/b> cmdlet does not appear to accept piped input when backing up transaction logs.<\/p>\n

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).<\/p>\n

$db = dir<\/p>\n

$db.name |<\/p>\n

foreach {Backup-SqlDatabase -serverInstance sql1\\Instance_a -BackupAction log -Database $_ -ea 0}<\/p>\n

To check the last backup date and recovery model, I pipe the database objects to the Select-Object<\/b> cmdlet and display the RecoveryModel<\/b> <\/i>and the LastBackupDate<\/b> for the database as shown here.<\/p>\n

[sql1]: PS db:\\> dir | select name, recoverymodel, lastbackupdate<\/p>\n

 <\/p>\n

Name                                       RecoveryModel LastBackupDate<\/p>\n

—-                                       ————- ————–<\/p>\n

Northwind                                           Full 10\/15\/2012 7:48:26 PM<\/p>\n

pubs                                                Full 10\/15\/2012 7:48:26 PM<\/p>\n

ReportServer$INSTANCE_A                             Full 10\/15\/2012 7:48:26 PM<\/p>\n

ReportServer$INSTANCE_ATe…                      Simple 10\/15\/2012 7:48:26 PM<\/p>\n

To view the backup set files, I use the EnumBackupSetFiles<\/b> <\/i>method from the specific database that I am interested in working. The following code retrieves the pubs<\/b> <\/i>database object, and then enumerates the backup set files.<\/p>\n

$pubs = Get-Item pubs<\/p>\n

$pubs.EnumBackupSetFiles()<\/p>\n

The code and the output associated with the code are shown here.<\/p>\n

\"Image<\/a><\/p>\n

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.<\/p>\n

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

Ed Wilson, Microsoft Scripting Guy<\/b><\/p>\n","protected":false},"excerpt":{"rendered":"

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. […]<\/p>\n","protected":false},"author":596,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[146,3,176,375,61,45],"class_list":["post-4794","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-databases","tag-scripting-guy","tag-sql-server","tag-sql-server-2012","tag-weekend-scripter","tag-windows-powershell"],"acf":[],"blog_post_summary":"

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. […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/4794","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/596"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=4794"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/4794\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=4794"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=4794"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=4794"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}