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:
- design
- documentation
- automation
- 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:
|
1 |
get-module sqlserver -listavailable |
Then to if is not installed (run powershell as administrator):
|
1 |
install-module -name sqlserver -allowclobber |
What you now have will be more than 1 hundred new cmd-lets:
|
1 2 |
PS> (get-command -module sqlserver -Type Cmdlet | Measure-Object).count 102 |
And if you want the list:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 |
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:
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.
|
1 2 3 4 5 6 7 8 9 |
# 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:
|
1 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 |
$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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
$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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
$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.
this is just what I am looking for! Thanks!
unfortunately I got a
“Backup-SqlDatabase : Failed to connect to server SERVER1\Default”
when running the backup part.
funny is, that the same string of $SQLInstance=SERVER1\Default” withing “list installed Databases on Server” works well as expected..
why??
Hi Joe,
Thanks for your comment, I’m happy you’ve found it useful.
If it’s not a named instance backup-sqldatabase cmd-let doesn’t need “\default”, so please try $SQLInstance=”SERVER1″ and it should not throw any error.
Thanks,
Regards
Hey Paolo, great article!
Would you care to provide the syntax for the Restore-SqlDatabase but with relocating the database files? In many cases this is necessary when the backup is from a different source \server\organization then the restore.
Also, I have a database that is backed up to two .bak files. What would the PS script to restore it look like in this case?
Thank you!
Thanks for your kind comment, The official documentation of the cmd-let is pretty good: https://docs.microsoft.com/en-us/powershell/module/sqlserver/restore-sqldatabase?view=sqlserver-ps that is exactly what you’re looking for setting it manually or using the -AutoReolocate switch.
and there is a parameter called -RelocateFile
Regarding the 2 bak files.. I guess you have the full backup and the differential, in this case, the first restore-sqldatabase cmd-let will use the full backup with the -NoRecovery switch and the second restore will use the differential bak (and it will restore with recovery). In short, there will be 2 lines of code, that’s how I would do it.
As a reminder: Test everything on a lower environment and if you want to review the OUTPUT T-SQL there is the option of reviewing the output script with the -script switch.
Hi Paolo,
A great article and exactly what I was looking for. I have a dilemma here if you can guide me:
When I execute this statement below, the backup is performed successful and stores the backup file to default backup directory (not stored in a network share; stored in a local drive of host computer)
Backup-SqlDatabase -ServerInstance “InstanceName” -Database “Test”
There is no transaction log backup performed before and after this full backup was taken and when I want to use the same backup file to restore the “Test” database using the command below, I am getting the following error:
Restore-SqlDatabase -ServerInstance “InstanceName” -Database “Test” -BackupFile “C:\ProgramFiles\Microsoft SQL Server\Backup\Test.bak
Restore-SqlDatabase: System.Data.SqlClient.SqlError: The tail of the log for the database “Test” has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.
At line: XXX char: XX
+ Restore-SqlDatabase -ServerInstance “InstanceName” -Datab..
+ CategoryInfo: InvalidOperation: (:) [Restore-SqlDatabase], SmoException
+FullyQualifiedErrorId: ExecutionFailed, Microsoft.SqlServer.Management.PowerShell.RestoreSqlDatabaseCommand
Could you please advise how this can be resolved?
Hi Shekhar,
I’m glad that you find the article useful.
If you want to perform a database restore you just need to add one option:
-RestoreAction Backup
In your case:
Restore-SqlDatabase -ServerInstance “InstanceName” -Database “Test” -RestoreAction Backup -BackupFile “C:\ProgramFiles\Microsoft SQL Server\Backup\Test.bak
Look careful at the documentation for this cmd-let:
https://docs.microsoft.com/en-us/powershell/module/sqlserver/restore-sqldatabase?view=sqlserver-ps
This parameter, for example, is optional, but useful and should make clearer what action you want to perform.
Once again, RestoreAction can be one of these: Database, Files, OnlinePage, OnlineFiles, Log.
I hope that what is what you were looking for!
Thanks
Hi Paolo,
Thank you so much for your time in providing the detailed feedback and suggestion. After adding the additional parameter (RestoreAction Backup) into the script as you had suggested shown below, I am encountering an error (shown below after the script) that state to specify one of the following enumerator names:
Restore-SqlDatabase -ServerIstance “InstanceName” -Database
Test -RestoreAction Backup -BackupFile “D:\Program Files\Microsoft SQL Server\Backup\Test.bak”
Restore-SqlDatabase” Cannot bind parameter ‘RestoreAction’. Cannot convert value “Backup” to type “Microsoft.SqlServer.Management.Smo.RestoreActionType”. Error: Unable to match the identifier name Backup to a valid enumerator name. Specify one of the following enumerator names and try again: Database, Files, OnlinePage, OnlineFiles, Log”
At
+ CategoryInfo: InvalidArgument: (:) [Restore-SqlDatabase], ParameterBindingException
+ FullyQualifiedErrorId: CannotConvertArgumentNoMessage, Microsoft.SqlServer.Management.PowerShell.RestoreSqlDatabaseCommand
Could you please guide me again on this issue?
Hi Shekhar,
I’ve enumerated the RestoreAction options and I wrote ‘backup’ by mistake instead of Database, my bad.
Restore-SqlDatabase -ServerInstance “InstanceName” -Database “Test” -RestoreAction Database -BackupFile “C:\ProgramFiles\Microsoft SQL Server\Backup\Test.bak"Thanks,
Regards
Hi Paolo! Thanks for the script! How can I change the script to backup differential copies and logs?
And a script for selective recovery by time?
Hi Aleksey,
Thanks, I’m glad you found this article useful.
The -incremental parameter indicates that a differential backup is performed.
https://docs.microsoft.com/en-us/powershell/module/sqlserver/backup-sqldatabase?view=sqlserver-ps#syntax
Have a look at the examples in the link below, if your database is in full recovery mode you can use the transaction logs for your point in time recovery:
https://docs.microsoft.com/en-us/powershell/module/sqlserver/restore-sqldatabase?view=sqlserver-ps#examples
Regards
Hi Paolo,
Excellent write up! Please could you tell me what would be the command to restore from multiple .bak files. I have 6 chunks of bak file to restore my database.
Thanks in advance.
Hi Shenaz,
Thanks. Regarding your question, a good place to start is the documentation. The Backupfile parameter is a list of strings as a type of object. Have you tried to pass multiple bak files?
I hope this answers your question.
Another good tip is using the script parameter to check the SQL output to the actual script that you would run for instance in SSMS.
Regards
How can I capture the results of restore-sqldatabase? For instance, let’s assume the backup file is corrupt for whatever reason. Right now, assuming it’s bad, and you fire the script off as is, it will fail, but if you have additional commands post-restore, those commands will fire anyway. Is there a way to capture the output so if non-zero, it exits with the message? I’ve tried to do
$restoreResults = Restore-SqlDatabase 2>&1 3>&1
from a deliberately bad backup file, and it just keeps going, and $restoreResults is null.
I want the script to exit if Restore-SqlDatabase fails for whatever reason.
Hi Mark,
Thanks for your comment, sorry to get back to you after with a delay of a couple of weeks.
If I’ve understood correctly what you want to achieve you should just use a try/catch for intercepting the specific error and in the catch have an exit int value (e.g. exit 1) and some other log/actions when it happens.