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.

One Reply to “MS SQL DB Backup and Restore with Powershell”

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.