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.
Sir,
If my database (source) is having encryption on and i would like to restore it to a different db(destination) under same instance.
What should be the powershell command for same ? Kindly assist on this.