How to Backup SQL Server Database Using PowerShell

Tagged: PowerShell

Problem#

As engineer, it is common to have a task where we want to backup Sql Server database. For example, there is a bug in your application and you want to reproduce the bug in your local machine, thus you need to backup the database. To achieve this task, we can use PowerShell besides doing manual backup through SQL Server Management Studio (SSMS).

In this blog post, we will walk you through how to backup Sql Server database using PowerShell.

Using SqlServer Module#

To use this module, first we need to install the module.

Install-Module -Name SqlServer

Then, we can use Backup-SqlDatabase cmdlet to backup the database by specifying the instance, database name, backup file location, etc.

Backup-SqlDatabase -ServerInstance 'U4-3X34FG3' -Database 'AgrM7Loc541' -BackupFile 'C:\temp\AgrM7Loc541.bak'

After you run the script, you will see the progress bar until the operation is completed.

Using DbaTools Module#

To use this module, first we need to install it from PowerShell Gallery.

Install-Module dbatools

Then, we can use Backup-DbaDatabase cmdlet to backup the database. Beforehand, we need to create connection to the instance and store it into a variable. The variable later will be used by Backup-DbaDatabase cmdlet to run actual script for database backup.

TrustServerCertificate is optional in case you get The certificate chain was issued by an authority that is not trusted error message.

By default, this cmdlet will use Windows Authentication credential.

$server = Connect-DbaInstance -SqlInstance 'U4-3X34FG3' -TrustServerCertificate
Backup-DbaDatabase -SqlInstance $server -Path C:\temp -Database 'AgrM7Loc541'

After executing the script, you will see the progress bar until the operation is completed.

The complete result will look as follows:

You can also connect with other alternative credential like SQL Server Authentication by using SqlCredential parameter. In this case, we use sa account.

$server = Connect-DbaInstance -SqlInstance 'U4-3X34FG3' -SqlCredential (Get-Credential sa) -TrustServerCertificate
Backup-DbaDatabase -SqlInstance $server -Path C:\temp -Database 'AgrM7Loc541'

It will prompt you user and password when you run the script.

Using SqlConnection and SqlCommand from .NET Framework#

We can also use SqlConnection and SqlCommand classes from .NET Framework. But, remember we have to enclose the script with try-finally block to ensure the connection is released regardless error happens or not to avoid memory leak.

This example uses Windows Authentication connection string.

using namespace System.Data.SqlClient

try {    
    $server = 'U4-3X34FG3'
    $database = 'AgrM7Loc541'

    $connectionString = 'Server={0};Database={1};Trusted_Connection=True' -f $server, $database
    $sqlConnection = New-Object SqlConnection $ConnectionString
    
    $filePath = 'C:\temp\AgrM7Loc541.bak'
    $cmdText = "BACKUP DATABASE [{0}] TO DISK='{1}'" -f $database, $filePath
    $sqlCmd = New-Object SqlCommand $cmdText, $sqlConnection

    $sqlConnection.Open()
    $sqlCmd.ExecuteNonQuery()

    ## This message will be displayed if 'Open' method does not throw an exception
    Write-Host 'Open database connection'
}
catch  {
    Write-Host $_.Exception.Message
}
finally {
    ## Close the connection to release the resource / free memory
    $sqlConnection.Close()

    Write-Host 'Close database connection'
}

After executing the script, there will be messages displayed as follows:

Conclusion#

To backup sql server database, we can use either Backup-SqlDatabase cmdlet from SqlServer module or Backup-DbaDatabase cmdlet from dbatools module.

We can also use SqlConnection and SqlCommand classes from .NET Framework. To implement this method, we have to apply dispose pattern where the code is enclosed in try-finally block so that if error happens the connection must be released to avoid memory leak.