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:

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:

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

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

And if you want the list:

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.

Backup-SqlDatabase

Next step can be backup all databases for example:

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

Let’s now be more specific:

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

This will be the output:

Check the help for all the interesting examples.

Restore-SqlDatabase

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

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.

13 Replies to “MS SQL DB Backup and Restore with Powershell”

  1. 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??

    1. 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

  2. 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!

    1. 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
      and there is a parameter called -RelocateFile that is exactly what you’re looking for setting it manually or using the -AutoReolocate switch.

      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.

  3. 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?

    1. 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

  4. 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?

    1. 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

  5. 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?

  6. 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

  7. 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.

    1. 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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.