Most of us use UI tools to interact with databases during development activities. When the application is installed on test / user’s machines, these tools may not be available for use.

This article is to help you in one of such situations.

If you need to take database backup but SSMS is not installed then you have two ways

• You can write SQL Script and runt it using SQLCMD
• You can write PowerShell script

This article explains how to use the PowerShell script to take backup of multiple databases.

The Problem Statement

You want to take database backups of all those databases where the name matches with some text.

For ex. You may want to take all the database which matches with your ‘application-database-name’.

Let’s understand few commands…

This section explains 3 commands which we are going to use.

Step 1: Connect to SQL Instance

You can use below PowerShell command to connect with the SQL instance

Let’s take look at some example scenarios on how this command will look like below. Please note that you need to replace computer and instance keywords from below command to connect to appropriate instance.

Get-ChildItem "SQLSERVER:\SQL\Computer\Instance\Databases"

We use “.” to connect to SQL instance on our local machine, while using SSMS. Please note that you will not be able to use “.” in below command as it means something else in the PowerShell world.

Instead of “.”, please use ‘localhost’ or ‘local’ text to connect with SQL instance on same machine.

Below are some examples to help you understand this:

Example 1: If you want to take database backups from the SQLExpress instance, then your command would be as below

Get-ChildItem "SQLSERVER:\SQL\localhost\SQLExpress\Databases"

Example 2: If you want to take database backups from the default SQL instance, then the command would be as below

Get-ChildItem "SQLSERVER:\SQL\localhost\DEFAULT\Databases"

Step 2: Apply filter on database name

This is pure PowerShell syntax. You can filter the objects returned by first command by using the where clause as shown below. This command filters the results of objects returned by command before the pipe (|) symbol.

| Where-Object {$_.name  -Like "*SomeText*"}

Step 3: Take the backup

The Backup-SqlDatabase is the command which is used for this purpose. If you know database name for which you want to take backup, you can directly specify the -DBName and -Instance parameters to take the backups.

The backup container switch is used to specify the folder where you want to keep the backup (.bak) files. By default, if this switch is not specified, then they are kept in the default backup location which is inside program files folder.

Backup-SqlDatabase -BackupContainer "c:\mybackupfolder\"

The Complete Solution

If we combine all the syntax we have understood in previous section, the complete command would be:

Get-ChildItem "SQLSERVER:\SQL\Computer\Instance\Databases" `
  | Where-Object {$_.name  -Like "*sometext*"}  `
  | Backup-SqlDatabase -BackupContainer "c:\mybackupfolder\"

This would create backups of all the databases which contains “sometext” in the name. The bak files would be saved in the “c:\mybackupfolder\”.

The generated bak file would have very long name, consisting of following part:

• The machine and instance details of SQL
• The database name
• The timestamp details at the end.

I hope you enjoyed reading this article. If this article helped you, please do comment and let me know.