MS SQL DB Backup and Restore with Powershell

Microsoft SQL Server is such a popular product that needs no introduction. Like every robust piece of software it requires knowledge and experience to be maintained properly… and that’s is the reason for DBA existence, right? I’m just joking!

Keys to success

Every successful DBMS implementation, or more in general IT System, that I found in my experience in different companies have these four pillars for me well covered and enforced:

1. design
2. documentation
3. automation
4. testing

In my opinion the lack of one of those can lead to a big bottleneck that will reduce any possibility to improve the overall system, if it is not enough it will make it simply very difficult to maintain.

On top of that, every Backup/Recovery strategy depends by the requirements and the resources available.

Requirements

Before dive into backup/restore strategies, I will also assume that you’re familiar with backup concepts:

• Principles ( e.g. 3-2-1 rule etc..)
• Types (full,differential, incremental)
• DR (disaster recovery)
• Business Continuity
• RPO, RTO, PiTR and so on
• Dabase Recovery Models (FULL, SIMPLE, BULK-LOGGED)

And I give also for granted that you can read and write some basic T-SQL scripts or Create and Manage Maintenance Plans in SSMS (Sql Server Management Studio).

Implementing some real world backup examples

In most companies I found different backup solutions according to size and needs, as always there is no right or wrong, but mostly depends on your needs and requirements.

SSMS MAINTENANCE PLANS (UI and T-SQL)
Implementing a maintenance plan with SSMS (with database integrity, re-organize index, backup the database and manages the retention of the database and notification if any error occurs). Splitting all plans if required in sub-plans by task : daily, weekly, monthly and yearly backup with different folders and retention periods.

CUSTOM BACKUP SCRIPTS (T-SQL / PowerShell)
T-SQL is an effective way to manage and perform backups. A good collection of scripts to implement is offered by Ola Hallengren (https://ola.hallengren.com/) available on his personal website. Just as a note remember that if you want to use those scripts, his “SQL Server Maintenance Solution” is licensed under the MIT license.
T-SQL is not the only option, in fact powershell can be a valid alternative.

BACKUP SOLUTION USING THIRD PARTY TOOLS (UI and T-SQL)
There are a lot of backup solutions available on the market and not just a few de facto standards, so I can guarantee that any software development company specialized in backup solution offers at least one product that can suits your need but to list all or just part of them is out of the scope for this article.

Where To Start

What I think all those three solutions is one form or another implement is T-SQL and in my opinion and your hands-on experience is necessary to find what are the drawbacks in implementing each one of these in your environment.

So I suggest to try all of three solutions in a test environment. There is no way to understand which is the best for your company if you don’t test it and if your expectations are met. The test environment will produce more that a simple Proof of Concept, but will show features and limits.

The first obvious step, is starting from what out of the box SQL Server Management Studio and Maintenance plans can offer. You can even start with a wizard if your not comfortable with the UI.
Second step is view the code that is used and executed by SQL Server as part of your backup plan. Once you have enough confidence start with powershell to run the same backup task and install some third party solution to learn and improve your custom solution or purchase the solution if it suits your needs.

I will split this topic in smaller part starting with this comparison between.

Depending what you want to achieve I will try to demonstrate that without a UI, powershell can be a good tool for implement your design, document it in a code format, automate it and test it.

Powershell SQL Server Module

If you want to check if your module is already installed on your system:

get-module sqlserver -listavailable

Then to if is not installed (run powershell as administrator):

install-module -name sqlserver -allowclobber

What you now have will be more than 1 hundred new cmd-lets:

PS>  (get-command -module sqlserver -Type Cmdlet | Measure-Object).count
102

And if you want the list:

PS>get-command -module sqlserver -Type Cmdlet | Select-Object -ExpandPropert
Add-RoleMember
Add-SqlAvailabilityDatabase
Add-SqlAvailabilityGroupListenerStaticIp
Add-SqlAzureAuthenticationContext
Add-SqlColumnEncryptionKeyValue
Add-SqlFirewallRule
Add-SqlLogin
Backup-ASDatabase
Backup-SqlDatabase
Complete-SqlColumnMasterKeyRotation
ConvertFrom-EncodedSqlName
ConvertTo-EncodedSqlName
Convert-UrnToPath
Disable-SqlAlwaysOn
Enable-SqlAlwaysOn
Export-SqlVulnerabilityAssessmentBaselineSet
Export-SqlVulnerabilityAssessmentScan
Get-SqlAgent
Get-SqlAgentJob
Get-SqlAgentJobHistory
Get-SqlAgentJobSchedule
Get-SqlAgentJobStep
Get-SqlAgentSchedule
Get-SqlBackupHistory
Get-SqlColumnEncryptionKey
Get-SqlColumnMasterKey
Get-SqlCredential
Get-SqlDatabase
Get-SqlErrorLog
Get-SqlInstance
Get-SqlLogin
Get-SqlSmartAdmin
Grant-SqlAvailabilityGroupCreateAnyDatabase
Import-SqlVulnerabilityAssessmentBaselineSet
Invoke-ASCmd
Invoke-PolicyEvaluation
Invoke-ProcessASDatabase
Invoke-ProcessCube
Invoke-ProcessDimension
Invoke-ProcessPartition
Invoke-ProcessTable
Invoke-Sqlcmd
Invoke-SqlColumnMasterKeyRotation
Invoke-SqlVulnerabilityAssessmentScan
Join-SqlAvailabilityGroup
Merge-Partition
New-RestoreFolder
New-RestoreLocation
New-SqlAvailabilityGroup
New-SqlAvailabilityGroupListener
New-SqlAvailabilityReplica
New-SqlAzureKeyVaultColumnMasterKeySettings
New-SqlBackupEncryptionOption
New-SqlCertificateStoreColumnMasterKeySettings
New-SqlCngColumnMasterKeySettings
New-SqlColumnEncryptionKey
New-SqlColumnEncryptionKeyEncryptedValue
New-SqlColumnEncryptionSettings
New-SqlColumnMasterKey
New-SqlColumnMasterKeySettings
New-SqlCredential
New-SqlCspColumnMasterKeySettings
New-SqlHADREndpoint
New-SqlVulnerabilityAssessmentBaseline
New-SqlVulnerabilityAssessmentBaselineSet
Read-SqlTableData
Read-SqlViewData
Remove-RoleMember
Remove-SqlAvailabilityDatabase
Remove-SqlAvailabilityGroup
Remove-SqlAvailabilityReplica
Remove-SqlColumnEncryptionKey
Remove-SqlColumnEncryptionKeyValue
Remove-SqlColumnMasterKey
Remove-SqlCredential
Remove-SqlFirewallRule
Remove-SqlLogin
Restore-ASDatabase
Restore-SqlDatabase
Resume-SqlAvailabilityDatabase
Revoke-SqlAvailabilityGroupCreateAnyDatabase
Save-SqlMigrationReport
Set-SqlAuthenticationMode
Set-SqlAvailabilityGroup
Set-SqlAvailabilityGroupListener
Set-SqlAvailabilityReplica
Set-SqlAvailabilityReplicaRoleToSecondary
Set-SqlColumnEncryption
Set-SqlCredential
Set-SqlErrorLog
Set-SqlHADREndpoint
Set-SqlNetworkConfiguration
Set-SqlSmartAdmin
Start-SqlInstance
Stop-SqlInstance
Suspend-SqlAvailabilityDatabase
Switch-SqlAvailabilityGroup
Test-SqlAvailabilityGroup
Test-SqlAvailabilityReplica
Test-SqlDatabaseReplicaState
Test-SqlSmartAdmin
Write-SqlTableData

Let’s now focus on a couple of them with a simple example to start:

• Backup-SqlDatabase
• Restore-SqlDatabase

List all databases

On a Windows Server 2016, I’ve installed SQL Server 2017 and imported a sample database “AdventureWorks2017”. If you are looking for a sample database for your demo as well you can find it on this GitHub repository.
I’ve used a default instance.

# List Installed Databases on your Server
$SQLInstance = "WIN-LIQ6AUQQ9DO\Default"
Get-ChildItem "SQLSERVER:\SQL\$SQLInstance\Databases"

Name                 Status           Size     Space  Recovery Compat. Owner                     Collation
                                            Available  Model     Level
----                 ------           ---- ---------- -------- ------- -----                     ---------
AdventureWorks2017   Normal      336.00 MB   57.45 MB Simple       140 WIN-LIQ6AUQQ9DO\Administr SQL_Latin1_General_CP1
                                                                       ator                      _CI_AS

Backup-SqlDatabase

Next step can be backup all databases for example:

Get-ChildItem "SQLSERVER:\SQL\$SQLInstance\Databases" |  Backup-SqlDatabase -BackupContainer C:\Backups -CopyOnly

I’ve set a different folder and added a copy only option.

Let’s now be more specific:

$SQLInstance = "WIN-LIQ6AUQQ9DO"
$DBName = "AdventureWorks2017"
$SharedFolder = "\\WIN-LIQ6AUQQ9DO\backups"
$Date = Get-Date -format yyyyMMdd
Backup-SqlDatabase  -ServerInstance $SQLInstance `
                    -Database $DBName `
                    -CopyOnly `
                    -CompressionOption on `
                    -BackupFile "$($SharedFolder)\$DBName-$date.bak" `
                    -BackupAction Database `
                    -checksum 

Cool, right? But what is doing under the hood? Let’s add a Verbose, that will show us the sql commands.

$SQLInstance = "WIN-LIQ6AUQQ9DO"
$DBName = "AdventureWorks2017"
$SharedFolder = "\\WIN-LIQ6AUQQ9DO\backups"
$Date = Get-Date -format yyyyMMdd
Backup-SqlDatabase  -ServerInstance $SQLInstance `
                    -Database $DBName `
                    -CopyOnly `
                    -CompressionOption on `
                    -BackupFile "$($SharedFolder)\$DBName-$date.bak" `
                    -BackupAction Database `
                    -checksum ` 
                    -verbose

This will be the output:

PS C:\Windows\system32> $SQLInstance = "WIN-LIQ6AUQQ9DO"
$DBName = "AdventureWorks2017"
$SharedFolder = "\\WIN-LIQ6AUQQ9DO\backups"
$Date = Get-Date -format yyyyMMdd
Backup-SqlDatabase  -ServerInstance $SQLInstance `
                    -Database $DBName `
                    -CopyOnly `
                    -CompressionOption on `
                    -BackupFile "$($SharedFolder)\$DBName-$date.bak" `
                    -BackupAction Database `
                    -checksum -Verbose 
VERBOSE: Performing the operation "Backup-SqlDatabase" on target "[WIN-LIQ6AUQQ9DO]".
VERBOSE: BACKUP DATABASE [AdventureWorks2017] TO  DISK = N'\\WIN-LIQ6AUQQ9DO\backups\AdventureWorks2017-20181005.bak' WITH  COPY_ONLY, NOFORMAT, NOINIT, NOSKIP, REWIND, N
OUNLOAD, COMPRESSION,  STATS = 10, CHECKSUM
VERBOSE: 10 percent processed.
VERBOSE: 20 percent processed.
VERBOSE: 30 percent processed.
VERBOSE: 40 percent processed.
VERBOSE: 50 percent processed.
VERBOSE: 60 percent processed.
VERBOSE: 70 percent processed.
VERBOSE: 80 percent processed.
VERBOSE: 90 percent processed.
VERBOSE: Processed 26304 pages for database 'AdventureWorks2017', file 'AdventureWorks2017' on file 4.
VERBOSE: 100 percent processed.
VERBOSE: Processed 2 pages for database 'AdventureWorks2017', file 'AdventureWorks2017_log' on file 4.
VERBOSE: BACKUP DATABASE successfully processed 26306 pages in 1.130 seconds (181.868 MB/sec).

Check the help for all the interesting examples.

Restore-SqlDatabase

As you can imagine the restore process is simple as well:

$DBName = "AdventureWorks2017"
$SharedFolder = "\\WIN-LIQ6AUQQ9DO\backups"
$Date = Get-Date -format yyyyMMdd
Restore-SqlDatabase  -ServerInstance $SQLInstance -Database "$DbNAme" `   -RestoreAction Database -BackupFile "$($SharedFolder)\$DBName-$date.bak" -verbose
VERBOSE: Performing the operation "Restore-SqlDatabase" on target "[WIN-LIQ6AUQQ9DO]".
VERBOSE: RESTORE DATABASE [AdventureWorks2017] FROM  DISK = N'\\WIN-LIQ6AUQQ9DO\backups\Adve
ntureWorks2017-20181005.bak' WITH  NOUNLOAD,  STATS = 10
VERBOSE: 10 percent processed.
VERBOSE: 20 percent processed.
VERBOSE: 30 percent processed.
VERBOSE: 40 percent processed.
VERBOSE: 50 percent processed.
VERBOSE: 60 percent processed.
VERBOSE: 70 percent processed.
VERBOSE: 80 percent processed.
VERBOSE: 90 percent processed.
VERBOSE: 100 percent processed.
VERBOSE: Processed 26304 pages for database 'AdventureWorks2017', file 'AdventureWorks2017' 
on file 1.
VERBOSE: Processed 2 pages for database 'AdventureWorks2017', file 'AdventureWorks2017_log' 
on file 1.
VERBOSE: RESTORE DATABASE successfully processed 26306 pages in 1.231 seconds (166.946 MB/se
c).

This article is longer than my standard. I’ve received one compelling comment that pushed me to write a little bit more. You’re feedback is always very much appreciated.