The following article will explain how to backup and restore databases in SQL Server using PowerShell. PowerShell is a very popular shell for operative systems used to automate tasks using the command line and scripts. PowerShell was created by Microsoft in Windows, but now it is supported in Linux and macOS also.

Introduction

You can automate any software including SQL Server, Exchange, Windows, Azure, VM, IIS, or any other tool. You can get a lot of libraries and samples in the Powershell Gallery.

PowerShell comes with the Windows operative system, however, the module for SQL Server is not included by default.

PowerShell for SQL Server (sqlps) is installed with SQL Server, but you may need to update it to the last version. SQL Server Management Studio (SSMS) does not include PowerShell.

In this article, I will compile a list of FAQs about backup and restore SQL Server databases using PowerShell.

• How to install the sqlps module
• How to update the sqlps module
• How to backup an SQL Server database using PowerShell
• How to backup an SQL Server database using PowerShell to a specific path
• How to backup an SQL Server database using a PowerShell script
• How to enable execution policy for PowerShell scripts
• How to pass the database name as a parameter in a PowerShell script
• How to invoke an SQL Server script using PowerShell
• How to list all the databases to backup
• How to backup all the databases using PowerShell
• How to create a compressed backup using PowerShell
• How to restore an SQL Server database from a backup
• How to backup an SQL Server transaction log with PowerShell

Getting started

How to install the sqlps module

To start PowerShell write Powershell in the cmd. In PowerShell, to install the sqlps module, you can use the following command line:

Install-Module -Name SqlServer

How to update the sqlps module

By default, SQL Server is not installed with the last version of sqlps (SQL PowerShell). To update the sqlps module, use this command:

Update-Module -Name SqlServer

How to backup an SQL Server database using PowerShell

Once that you have the sqlps PowerShell module ready, you can backup a SQL Server database using the backup-sqldatabase cmdlet. The following example shows how to backup the database named testdb. We are assuming that the database already exists:

Backup-SqlDatabase -ServerInstance "." -Database "testdb"

By default, the backups are stored in a path like the following:

C:\Program Files\Microsoft SQL Server\MSSQLXX.MSSQLSERVER\MSSQL\Backup

How to backup an SQL Server database using PowerShell to a specific path

If you want to backup your SQL Server database in a different folder, you can use the -BackupFile option to specify the path. To back up to a specific path, you can use the following cmdlets:

Backup-SqlDatabase -ServerInstance "." -Database "testdb" -BackupFile "c:\sql\testDB.bak"

How to backup an SQL Server database using a PowerShell script

If you want to execute a PowerShell script instead of running the command, you can store the command in a script. Create a script named backup.ps1 with the following content:

Backup-SqlDatabase -ServerInstance "." -Database "testdb" -BackupFile "c:\sql\testDB.bak"

In order to execute the script, use the following command:

.\backup.ps1

How to enable execution policy for PowerShell scripts

If you have problems executing a script (by default it is restricted, and you will receive an error message the first time), run the set-execution policy to remove the restriction:

Set-ExecutionPolicy -ExecutionPolicy RemoteSigned

For more information about enabling PowerShell scripts, check the Set-ExecutePolicy documentation.

How to pass parameters in a PowerShell script to backup an SQL Server Database

Sometimes we need to pass parameters to a PowerShell script. The following script is named backupparameter.ps1 example will have 2 parameters. The database name ($databasename) and the path ($path). The types used are strings and we separate them by a comma. We specify that the scripts are mandatory with the Mandatory=$true:

Param([Parameter(Mandatory=$true)][string]$databasename,[string]$path) Backup-SqlDatabase -ServerInstance "." -Database $databasename -BackupFile $path

To invoke a PowerShell script and pass two parameters, use the following cmdlets:

.\backupparameter.ps1 testdb c:\sql\backuppar.bak

How to invoke an SQL Server script using PowerShell

First, we will create a script to backup a database using T-SQL named backup.sql:

BACKUP DATABASE [testdb] TO DISK = N'c:\sql\backuppar.bak'

Next, invoke the backup.sql script in PowerShell.

Invoke-Sqlcmd -InputFile "C:\sql\backup.sql" | Out-File -FilePath "C:\sql\results.out"

How to list all the databases to backup

You will need to change location in PowerShell:

Set-Location SQLSERVER:\SQL

Next, navigate to the location of the databases (use the dir command to check the object names):

cd server-name\default\databases>

Use a dir and you will list the SQL Server databases:

dir

To exclude a database that starts with SSIS, you can use the following cmdlet:

dir | Where-Object {$_.Name -notlike "SSIS*" }

How to backup all the databases using PowerShell

To back up all the databases move the database path (check the section 9 explained previously) and run the following command:

foreach ($database in (Get-ChildItem)) {
$dbName = $database.Name
Backup-SqlDatabase -Database $dbName -BackupFile "c:\sql\$dbName.bak" }

How to create a compressed backup using PowerShell

The following command will compress a backup created:

Backup-SqlDatabase -ServerInstance "." -Database "testdb -BackupFile "c:\sql\testDBcomp.bak" -CompressionOption On

How to restore an SQL Server database from a backup

The following cmdlet will show how to restore a database from a backup:

Restore-SqlDatabase -ServerInstance "." -Database "testDB" -BackupFile "c:\sql\testDB.bak"

How to backup an SQL Server transaction log with PowerShell

The following cmdlet allows to backup the transaction log:

Backup-SqlDatabase -ServerInstance "." -Database "testDB" -BackupAction Log

Conclusion

In this article, we learned how to create SQL Server backups using PowerShell scripts. PowerShell is a very useful tool to automate backup tasks. We learned how to create scripts, execute scripts, pass parameters, and backup multiple tables at the same time. We also learned how to restore a database from a backup. You can automate any tasks and with just one click you can create complex tasks in single or multiple scripts. It is also possible to integrate SQL Server with other tools using PowerShell like Azure, IIS, or automate tasks related to Windows and SQL Server or more.

I really hope you enjoy this article.

References

• SQL Server PowerShell
• Powershell To Back up SQL Server Database
• PowerShell to SQL Server examples