Hey DBAs! Powershell is your friend!

In the last few months I supported several clients to upgrade their “old” SQL Server instances to the last version. I really like the side by side approach for several reasons, top two are the simple rollback procedure and the chance to clean some unsued objects like old logins, unused jobs. I know that DBAs usually keep something that is not used anymore just because “you’ll never know, just in case” 😊.

The last part of the upgrade process is usually a backup on the old instance and a restore on the new one for each database. This part is usually simple, but when you have lots of DBs, doing the same task lot of times could lead to errors, that’s why I like to have things scripted.

I noticed that lot of DBAs wrote down T-SQL Scripts, but again this could lead to a pains: you have to bounce from one query window to another or even worst from one RDP session to another.

What I suggest here is to use PowerShell, using sqlps module.

Here’s the links to the documentation.

/en-us/powershell/module/sqlps/backup-sqldatabase?view=sqlserver-ps /en-us/powershell/module/sqlps/restore-sqldatabase?view=sqlserver-ps

It’s easy to set up a command like this:

and restore the database using something like this:

In this way I can backup and restore databases from one instance to the other in a single place: my PowerShell window.

From this point, if you know a little PowerShell, it’s really easy to let PowerShell generates the scripts for you:

Really simple: a query to retrieve all databases, a loop to make substitutions in commands and place them in a powershell script.

The result will look like this:

There are lots of Cmdlets in SQLPS, almost everything that you can do in SQL Server Management Studio can be done also in PowerShell.

Take a look at the documentation:

/en-us/powershell/module/sqlps/?view=sqlserver-ps

Have Fun.

Stefano

Disclaimer: I just want to show an alternative to T-SQL. The commands and the scripts listed above are just for learning purpose, every environment needs a specific assessment. Lots of things needs to be considered. In other words: don’t run them on production environments!