How To Backup MS SQL Express Databases? #PowerShell

Happy Twenty Fifteen! The first post of the new year deals with a common question I am confronted with from time to time: Do you have a script to backup MS SQL Express? Yes, I have. The script requires the SQLPS PowerShell Module that will be installed automatically with newer versions of MS SQL Express. Basically, it simplifies the usage of its Backup-SqlDatabase Cmdlet:

With this post I share my approach to facilitate the re-usage of SQL commands within PowerShell scripts. You should continue reading if you often deal with SQL. And even if you’re not about to use PowerShell scripts against SQL databases you can take some inspiration on how to build a smart automation solution.

In order to issue SQL command text against an ODBC database connection I prefer my function Invoke-SQL. The function either accepts an existing OdbcConnection object or a connection string in order to create a connection on the fly. By default the function returns $true if the execution of the given SQL statement succeeded. With the -PassThru switch the function loads the results into a DataTable object and returns it for further processing. I uploaded Invoke-SQL to the Microsoft TechNet Gallery. Get your copy from there.

Before I proceed to the SqlCommand hash table let me explain why you need it. The Invoke-SQL example below shows how to pass a simple SELECT statement to an existing ODBC connection ($DBConnection) and save the query’s result into the DBResult variable:

So much for the simple scenario. As you know SQL command texts can be far more complex than SELECT-foo-FROM-bar and often span multiple lines. With PowerShell it is good practice to use here-strings to deal with multi-line SQL commands. Take a look at the next example that shows the concept (meaning that you shouldn’t care about the content of the SQL query):

Ok. And now put the case that you have 15, 20 and more of such rather sophisticated SQL commands and you have to re-use them over and over but with different values. Take a look at the value for Location.ID in the previous example. It is hard coded. Therefore, in order to re-use the $SelectVMM here-string you need to leverage the copy-paste-align method (which is error-prone and bloats scripts with redundant code). Or is there another, better, smarter way? Yes, there is. Take a look at the slightly altered example below:

As you can see I replaced the hard coded value from the here-string with the placeholder {0}. And afterwards, in order to re-use the here-string, I used PowerShell’s format operator to replace that placeholder with a specific value, and saved the resulting here-string into a new variable. That’s nice. There’s still room for improvement though. Finally, I bring that SqlCommand hash table into play…

Basically, the hash table is a collection of named here-strings each containing a generalized SQL command text like above. It could look like this for example:

PowerShell

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

$SqlCommand=@{

GetValue=@'

SELECT {0}

FROM {1}

'@

GetLatestRecord=@'

SELECT TOP 1 *

FROM {0}

'@

SetIdValue=@'

UPDATE {0}

SET {1}='{2}',timestamp=GETUTCDATE()

WHERE Id={3}

'@

SelectVMM=@'

SELECT vmm.hostname, vmm.id

FROM myVmmServers as vmm

JOIN myDataCenters as DC ON (DC.id = vmm.dcid)

JOIN myDCLocations as Location ON (Location.id = DC.lcid)

WHERE Location.id = '{0}'

'@

}

With that SqlCommand hash table in memory SQL scripting is as easy as:

Invoke-SQL is designed to issue any valid SQL command text against an ODBC database connection. The function either accepts an existing OdbcConnection object or a connection string in order to create a connection on the fly. By default the function returns $true if the execution of the given SQL statement didn’t fail. With the PassThru switch the function loads the results into a DataTable object and returns it for further processing. Invoke-SQL returns nothing on error opening the ODBC connection or executing the SQL command text.

The tech book publisher Packt asked me to review Sherif Talaat‘s book “Windows PowerShell 4.0 for .NET Developers“, subtitled “A fast-paced PowerShell guide, enabling you to efficiently administer and maintain your development environment“. According to their own statement Packt’s “books focus on practicality, recognising that readers are ultimately concerned with getting the job done“. The book is available for purchase on www.packtpub.com.

To put it in a nutshell, from my perspective Sherif delivers exactly what the book’s subtitle and Packt Publishing promises: it’s a well-made balancing act between being fast-paced, easy-to-follow and providing the reader (that is a .NET developer) with essential information on how to leverage PowerShell.

This book is like distilled water: it delivers pure information in order to take the reader from “101” to a professional level in PowerShell. It’s about 140 pages only. That’s definitely no huge tome and therefore it’s self-evident that it lacks of deeper background information, trivia, and cleverly thought out examples that unveil the proverbial Power of PowerShell. But, whenever useful the author cross-referenced the book to resources with further information. Ideally, the reader already has some basic scripting knowledge and hands-on experience with one of the .NET programming languages – not least because the book is aimed at .NET developers who want to learn how to use PowerShell.

Chapter 4 — PowerShell and Web Technologies — is about working with web services & requests, RESTful APIs, and JSON.

Chapter 5 — PowerShell and Team Foundation Server — covers the TFS cmdlets and shows how to get started and work with them.

Did I miss something? Yes, there’s no information about Desired State Configuration. But that’s as far as it goes.

Considering the fact that we are about to enter a new era in IT where developers and operators need to work closely together (or in one person) to continuously deliver services in automated IT infrastructures, a .NET developer should at least get a copy of this book in order to be prepared for the best 😉