Backup Databases Using Workflows

Standard

This post will show you how to backup databases using workflows in parallel. I will show a scenario where an instance will backup all the databases at almost the same time.

You probably heard about the new PowerShell module for SQL Server called “SqlServer”. If not you don’t do much with PowerShell or you’re probably not a member of the PASS PowerShell VC. If you haven’t heard from it you can become a member of the of the virtual chapter for PowerShell and if you like instant messaging, become member of the Slack SQLServerCommunity.

The Challenge

I wanted to test something since my last presentation where I discussed using workflows in PowerShell. One of the strong features of a workflow is to go parallel which could potentially reduce the time backup all the databases.
Of course we need to take into account that starting backups for all the databases on an instance could be a possible problem due to performance on the disk or over the network.

Let’s explore the new PowerShell module for SQL Server and see if there is a backup cmdlet.

And there is a cmdlet for that:

backupparallel_backupcmdletfind

Let’s see some examples of the cmdlet:

There are quite a lot of examples so let’s pick one:

backupparallel_backupexample

While I can execute this script to backup all the databases one after the other, I could also use some techniques to backup all my databases at the same time.

The Script

The script will allows you to enter an instance name from which it will connect to and backup all the databases present:

I could go even further than that by including a server parameter to execute the backups on multiple my servers at the same time. This seems very nice in theory but I think nobody will ever implement such a solution because that would be too intense for most environments.

The Result

The ISE will not show you that things are executed simultaneously but it does show the current state of a backup.

backupparallel_resultise

When I look in SSMS I can retrieve all the commands executed with BACKUP in it.

backupparallel_resultssmsAs you see the server is pretty busy backing up all the databases.

Conclusion

It’s possible to use a workflow to execute your backups. You have to take into consideration that there is a downside. If you execute all the backups at once you’ll probably get issues with throughput if you’re dependent on a slow network for example.

You could always add another parameter to filter out specific databases to make sure you execute it as a specific set.

Hope this helps you out and maybe inspire you to alter some traditional scripts to get more work done at the same time.

 

Leave a Reply