Day 2 – Backup Basics

One of the most basic things a DBA needs to do is to take reliable backups. Before you undertake any piece of work you need to have somewhere to roll back to in case it all goes horribly wrong.

The dbatools Backup-DbaDatabase function can really help out here.

While it can be automated with SQL Agent jobs, it’s not it’s real strong point. For that you’re better off sticking with Ola Hallengren’s maintenance solution.

The Basics

At it’s most basic all Backup-DbaDatabase needs is a sql instance:

Backup-DbaDatabase -SqlInstance server1\instance 

This will take a full backup of every database on instance server1\instance into the instance’s default backup location

This assumes that you’re running Windows authentication on your SQL instance. If not, you need to provide the credentials to connect with.

You do this with the SqlCredential parameter like so:

 
Backup-DbaDatabase -SqlInstance server1\instance -SqlCredential $credential

To create a credential you have 2 options:

Using Get-Credential. This will pop up a box for you to enter the username and password to create the credential:

$credential = Get-Credential

Or you can create one on the command line, though this will leave your password in the command history:

$password = ConvertTo-SecureString 'P@ssw0rd' -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential ('username', $password)

Just in case you don’t know what the default backup location for an instance is, you can grab it like so:

Get-DbaDefaultPath -SqlInstance server\instance

If you want to backup a specific database or more then you can use the -Database parameter

Backup-DbaDatabse -SqlInstance server1\instance -Database db1, db4

which will backup db1 and db4. or you can pipe in values from Get-DbaDbDatabase like so:

Get-DbaDatabase -SqlInstance server\instance | Where-Object {$_.name -like '*test'} | Backup-DbaDatabase

Which will back up every database whose name ends in test on the server\instance. Really hand for when the devs want to deploy something new

Other backup types

Now, it’s not always just about the full backups. Of course we can handle differential and log backups, just a parameter away

Get-DbaDatabase -SqlInstance server\instance | Where-Object {$_.name -like '*test'} | Backup-DbaDatabase -Type Differential

Get-DbaDatabase -SqlInstance server\instance | Where-Object {$_.name -like '*test'} | Backup-DbaDatabase -Type Log

There is one other type of backup that is very important to be aware of, the COPYONLY backup. When a SQL Server database is in full recovery mode you want to make sure that you don’t interrupt the backup chain. This can ruin your chances of performing a successful restore.

For instance, if you’re using Differential backups. Each differential backup is the sum of all changes from a specific full backup. If you take a new backup, all subsequent differentials are based on that one. That can be a problem if you’re just grabbing a backup to refresh test or to investigate an issue.

A COPYONLY backup does not break the chain, so it can be removed or deleted once it’s no longer needed without breaking your recovery plan

To take one of these, just use the -CopyOnly switch:

Get-DbaDatabase -SqlInstance server\instance | Where-Object {$_.name -like '*test'} | Backup-DbaDatabase -CopyOnly

Testing a backup command

Maybe you want to try something out and see what’s going to happen, but you don’t want to actually do the backup?

That’s what the -OutputScriptOnly switch is for. This will go through all the same processing as a full run would, the only difference is that Backup-DbaDatabase will just spit out the T-SQL without running the command.

Running this:

Backup-DbaDatabase @Script:appsplat -Database roles1 -FileCount 3 -OutputScriptOnly

will not perform a backup, but will produce this for you:

BACKUP DATABASE [roles1] TO DISK = N'/var/opt/mssql/data\roles1_202004281714-1-of-3.bak', DISK = N'/var/opt/mssql/data\roles1_202004281714-2-of-3.bak', DISK = N'/var/opt/mssql/data\roles1_202004281714-3-of-3.bak' WITH NOFORMAT, NOINIT, NOSKIP, REWIND, NOUNLOAD, STATS = 1

We’ll be covering what some of those other options are in a post in a couple of days

Backup-DbaDatabase Output

Backup-DbaDatabase produces a lot of output that can be of interest. Quite a lot of it isn’t shown at first glance so we’ll take a look at what’s available:

$output = Backup-DbaDatabase @Script:appsplat -Database roles1 -FileCount 3

Now, the standard output looks like this:

Which gives you the basic information. But if you then ask for everything in the output you get a lot more information:

That’s just about every stat you might want for your backup, or everything you might want to test for after taking the backup.

Conclusion

Today we’ve looked at the basics of what Backup-DbaDatabase can do, tomorrow we’ll be looking at handling backup files

All posts in this series can be found at 31 Days of dbatools Backup and Restores

After sifting through a huge heap of great session submission we finally got it narrowed down to 10 sessions for the US day and the European day.

We’ve got some really top quality speakers and sessions for you. We’ve got introductions to Power BI, Azure Data Factor, Containers, SQL Queries and using Azure Notebooks for Data Science

To have a look for yourself the agendas are here:

Data Platform Discovery Day 2020 – US Agenda

Data Platform Discovery Day 2020 – European Agenda

You can attend one, or both if you don’t mind a very early morning or very late night. Registration is for the entire day, but you can drop in and out for the sessions you want to see.

To register head over to these links:

Data Platform Discovery Day – US Registration

Data Platform Discovery Day – European Registration

More details are available from the event website – Data Platform Discover Day website

With the current pandemic enforced usage of Teams over chatting at the water machine I wanted a way to hide the background.

My wife’s a nurse, so the home ‘office’ is hers for patient confidentiality atm, which means I’m nomading around the house. This means my background varies a lot, so having something different than an untidy shelf of books, the kitchen counter or a blinding window glare behind me would be nice

There’s plenty of windows hints on having a custom Teams background on Windows, but not so much about having them on a Mac. With a bit of digging it turn out that if you want a custom Teams background on your Mac it’s quite simple after all.

All you need to do is to drop a file into:

~/Library/Application Support/Microsoft/Teams/Backgrounds/Uploads

If you’re not used to *nix paths, the ~ translates to your home directory. So for me on my Macbook Pro with the username stuart, that ends up being

/Users/stuart/Library/Application Support/Microsoft/Teams/Backgrounds/Uploads

Just drop the images you want Teams to use as a custom background into that folder and they’ll show up in the gallery.

It seems Teams isn’t picky about which file format you use for a custom background. I’ve tried png, jpeg and gif, and they all work fine. Unfortunately animated gifs don’t work, which is both a good and a bad thing depending on your personal love of gifs

With a number of the large in person Data Platform conferences and meetups having to cancel or take a hiatus due to the current Covid-19 outbreak, Matt Gordon (b | t) and myself decided to do something about it.

So we came up with Data Platform Discovery Day. A 2 day online event aimed at those people who are looking for a start in the Data Platform world or are wanting to learn some fundamentals about an aspect of it.

Each day with run alongside business hours across a different continent, and will feature ten 50 minute sessions. Each day will have different speakers, and if you want there’s nothing to stop you attending both if you fancy an early morning or late night

Day 1 is running during US daytime on 29th April. The first session will begin at 9am Eastern Time, and then a new session will being at the top of each hour

Day is running during European daytime on 30th April. The first session will begin at 9am BST (UTC+1), again with a new session starting at the top of each hour.

Registration for the events with start once we’ve finalised the speakers.

And talking of speakers, the Call for Papers for both events are open. You can submit to both events if you want. Please remember that sessions are 50 minutes, and content should ideally be Level 100 material.

US Data Platform Discovery Day call for papers

European Data Platform Discover Day call for papers

We’d love to encourage first time speakers as well, so if you’d like ask any question then please get in touch with me (twitter) or Matt (twitter)

So I’m busy working on my new dbaSecurityScan module, busily trying to write all the pester tests for the appveyor pipeline

Part of the testing for the module involves building a number of test scenarios, each of which of which needs a database spinning up for it.

On the Windows builds, that’s nice and easy as I just use this in the test script to loop through all the scenarios and run the .sql files :

However, I’m developing on my Macbook Pro as I want this module to be nicely platform agnostic. SQL Server is running nicely in docker and dbatools can connect to it happily. But I just can’t get sqlcmd to work, lots of TCP Provider: Error code 0x102 and TCP Provider: Error code 0x2AF9 messages no matter what I try. So being short on time I though I’d try something else, and hopefully this will work when I get Linux build running as well

The scripts I want to run create databases, users, schemas and whole lot more, so there’s lots of batch separators (; and GO) in them.Invoke-DbaQuery is a great function, but it doesn’t like working with batch separators (which is not through want of trying, it’s just really tricky). So time to drop back to some raw SMO to run the scripts in with ExecuteNonQuery()

I’ve added a variable to my builds that lets me pick when I want to run via SQLCMD or via SMO. At the minute I’m just using it on my machine, but if I run into problems later, it’s nice to now I can just toggle by looking at which platform it’s running.

I’m currently migrating a lot of SQL Server instances onto newer virtual machines. Quite a few of these instances talk to each other via Linked Server for various historical reasons. And a lot of that chat is done via distributed transactions, which means configuring MS Distributed Transaction Coordinator.

So one of the first things I need to do, is check that that DTC is working between the 2 boxes. This is dead simple with official PowerShell module for msdtc. It’s as simple as running Test-Dtc and then working through any errors. So, we just run this:

Test-Dtc -LocalComputerName Source -RemoteComputerName Destination

and as this is a post about fixing something, it won’t suprise you that I’m going to get an error message:

"The OleTx CID on SOURCE and DESTINATION is the same. The CID should be unique to each computer."
At C:\Windows\system32\WindowsPowerShell\v1.0\Modules\MsDtc\TestDtc.psm1:266 char:13
+             throw ([string]::Format($Strings.SameCids, "OleTx", $Loca ...
+             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: ("The OleTx CID ...each computer.":String) [], RuntimeException
    + FullyQualifiedErrorId : "The OleTx CID on SOURCE and DESTINATION is the same. The CID should be uniqu
   e to each computer."

But in much more fetching shared of red and yellow, announcing that “The OleTx CID on SOURCE and DESTINATION is the same. The CID should be unique to each computer.”

The cause is really simple to grasp. When the Distributed Transaction Coordinator is installed it registers a GUID to identify it, the theory being a GUID clash should be a vanishingly rare occurance.

That is, until someone’s cloning Virtual Machines. So I have a batch of shiny new VMs that all think they’re the same instance of DTC. That’s not so good. It used to be the accepted fix was to manually remove the the distributed transaction coordinator, clean the registry, restart and then reinstall everything. That sounds like a lot of work to me!

The msdtc module makes it very simple to do, so we’re staring off here:

PS C:\Windows\system32> Get-Dtc | Select-Object *


DtcName               : Local
KtmRmEndpointCid      : 72c409a9-9c7b-4d24-9e0c-b946a2e5aa4c
OleTxEndpointCid      : 3eb9ce34-4d2c-48cf-9ebe-d6e888f9b0ca
Status                : Started
UisEndpointCid        : c5f16d32-01c9-4b65-be57-4521fa4bb934
VirtualServerName     : SOURCE
XAEndpointCid         : fcec2fe2-eab2-4277-853a-6ea4d7736430
PSComputerName        :
CimClass              : root/MsDtc:DtcInstance
CimInstanceProperties : {DtcName, KtmRmEndpointCid, OleTxEndpointCid, Status...}
CimSystemProperties   : Microsoft.Management.Infrastructure.CimSystemProperties

While DTC normally installs with a default log path, we’ll just make sure and grab it first. Then it’s just as simple as Uninstall-DTC and Install-DTC:

$logPath = (Get-DtcLog).path
Uninstall-Dtc -Confirm:$false
Install-Dtc -LogPath $logPath -StartType AutoStart

And to check it’s worked, lets query dtc and check:

PS C:\Windows\system32> Get-Dtc | Select-Object *


DtcName               : Local
KtmRmEndpointCid      : 72c409a9-9c7b-4d24-9e0c-b946a2e5aa4c
OleTxEndpointCid      : 3eb9ce34-4d2c-48cf-9ebe-d6e888f9b0ca
Status                : Started
UisEndpointCid        : c5f16d32-01c9-4b65-be57-4521fa4bb934
VirtualServerName     : VMCLSTR-IVANTI
XAEndpointCid         : fcec2fe2-eab2-4277-853a-6ea4d7736430
PSComputerName        :
CimClass              : root/MsDtc:DtcInstance
CimInstanceProperties : {DtcName, KtmRmEndpointCid, OleTxEndpointCid, Status...}
CimSystemProperties   : Microsoft.Management.Infrastructure.CimSystemProperties

And there we have a new unique OleTxEndPointCid and we’re good to go

A couple of days ago I walked through getting information about data deletion uing the SQL Server Transaction Log. We looked how you could find when and by whom data had been removed from a SQL Server table.

So you’ve got the when, and you’ve got the who, now how do you precede? Remember you could be holding someone’s job or professional reputation in your hands.

As a long time DBA I’m well aware that applications are not always the best written things, and instructions don’t always spell out correctly what an option is going to do.

In the case I was writing about I withheld the name from general knowledge, a single senior manager was made aware that I knew and would be investigating. Why did I do this?

In the case of this particular third party application , there were a couple.

• Deleting data within the app should write data to an internal audit trail, this hadn’t
• Users cannot generate their own SQL queries, so this wasn’t an ad hoc query either
• The single row delete was in it’s own transaction
• This particular app is badly written. We’ve seen it fail to correctly write it’s own ini files on exit
• It has a habit of crashing out for no reason

So a bit like a lot of applications out there. Now I had some time to investigate I started by going through logs on the client Windows machine and trying to replicate on my own.

Eventually I traced it back to a coding error in the application where they processed an Update as:

1. Delete old data
2. Insert new data
3. Write audit log

None of this happened in an encompassing transaction (from studying the Transaction Log). So when the app crashed just after step 1, the row was just left missing and nothing there.

To the user who was logged in at the time, they wouldn’t have been aware of the crash as the GUI doesn’t display app status. The crash also happened at 16:55, which is pretty much home time so they wouldn’t have looked at the record again

Now I had the evidence I could talk to the Senior manager and show that we needed to raise this with the supplier, and that the user was blameless, but that we should recommend to users that they should double check any updates to be sure.

Releasing the name to a less trusted source without this background information would have probably led to the user being blamed for something that wasn’t really their fault

So, just because you have the information it doesn’t mean it’s actionable with adding more context and knowledge around it.

It’s all been a bit quiet around here with lots on at work and training for a marathon. One piece of new I’ve not menitoned is that I’m now CISSP certified 👍. I sat the exam in December, but it’s taken a while for the paperwork and accreditation to get sorted and for everything to become official

The main thing I’ve taken away from studying for the certification is that process rules everything. As an in the trenches DBA we’re more worried about the specifics of SQL Server permissions or the Oracle auiting is correct. But why are we doing those things, and are we doing them correctly?

Correctly doesn’t just mean technically correct, we’re almost certainly doing them that way or things will break. What I mean is are we correctly implementing the processes and policies that drive the rest of the business?

For example, everyone loves backups (yeah, I know, this is my favourite thing as well). But how much should you be keeping? Do you really need 3 years of backups? Is that just increasing the amount of data you could loose in a breach? Are the older backups encrypted, and would restoring and encrypting them break the purposes they were kept for?

If you’re never going to use them why are you keeping them? If it’s just for a CYA audit reason, then why not just keep the audit logs? Less chance of leaking PII or Financial data if you’ve just got the bare bones of X did Y on Z.

This alignment with Organisational policy is a core reqiurement for doing SQL Server security correctly. It will allow you to concentrate on exactly what needs to be done and not just running around implementing ad-hoc fixes every time a hole appears.

Expect more posts on this topic over the coming months. And if you want a deep dive into SQL Server Security then I’m presenting a full day workshop at SqlBits 2020 – SQL Server Security from the Groud Up on Wednesday 1st April

Sometimes you have to give people a little more access to an Azure environment than you might like, and then there’s the chance of someone accidentally deleting a resource.

A resource deletion may not sound like too much of a big thing if you’re deploying Infrastructure as code, hey we’ll just terraform apply again and it’ll pop backup.

In theory that’s a great idea, just with one big problem. The new resource isn’t the old resource!

For an example, an Azure SQL Database server is a unique resource. If you delete one you lose any backups you’ve taken as they’re hosted on the server. Spinning up a new one isn’t going to get them back! A phone call to MS Support may if you’re quick and lucky

To avoid this you want to be user Azure Resource Locks. Think of these as the Azure version of child proof locks on your kitchen drawers. Yes, they may occaisonally mean you’ve got an extra step to get a knife out, but the little on can’t get their hands on it.

Auzre Resource Locks

First thing about Azure Resource Locks is that they apply to everyone and every role. Even if you’ve the Owner role on a Resource Group via RBAC, if there’s an Azure Resource Lock on that Resource Group you’re going to be blocked until you’ve removed the lock

This is great because it prevents those “oh ****, that was the wrong subscription” moments

Locks apply downwards from the resource they’re applied to. So if you apply one on a Resource group then it’s lock applies to every resource within that resource group. Apply it to an Azure SQL Database server, and it will apply to all of the Databases on that server.

Azure Resource Lock Types

Resource locks come in 2 flavours

• CanNotDelete
• ReadOnly

CanNotDelete does what it says on the tin. Once this lock is applied the resource (and it’s children) can not be deleted, even if you use -force

ReadOnly implements CanNotDelete and also prevents any modification of the locked resource and it’s children

Setting Azure Resource Locks

You can set Azure Resource Locks via the Azure Portal, Azure CLI, Azure Powershell or ARM Templates. Below are how you can set the same CanNotDelete lock on the Lock Resource Group using each of the 4 options:

• Azure Portal

• ARM Template

Create a template.json file:

{
    "$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
    "contentVersion": "1.0.0.0",
    "parameters": {},
    "variables": {},
    "resources": [
        {
            "type": "Microsoft.Authorization/locks",
            "apiVersion": "2015-01-01",
            "name": "LockGroupNotDelete",
            "properties":
            {
                "level": "CanNotDelete",
                "notes": ""
            }
        }
    ],
    "outputs": {}
}

Which you’d deploy with:

New-AzResourceGroupDeployment -ResourceGroupName lock -Name lock -TemplateFile ./template.json

• Azure CLI:

az lock create --name LockGroupNotDelete --lock-type CanNotDelete --resource-group Lock

• Azure PowerShell:

New-AzResourceLock -LockName LockGroupNotDelete -LockLevel CanNotDelete -ResourceGroupName Lock

What you’ll see with Azure Resource Locks

So now we’ve seen how to create a resource lock, what are going to see if we try to delete the resourcegroup, just to prove it works and also so we know what to look out for when we bump into one we didn’t expect to see

• Azure Portal

Azure CLI

Azure PowerShell

As you can see the Resource Locks will stop you deleting the resource, which is nice. The errors messages are also nice and informative, so you know the resource is locked and at which scope the lock is placed. Which makes it easier to find the lock to remove it. Talking of removing locks:

Removing Azure Resource Locks

You can remove locks with any of the methods you can use to create them, so you’re free to mix and match how you do things.

• Azure Portal

• Azure CLI

az lock delete --name LockGroupNotDelete --resource-group Lock

• Azure PowerShell

Remove-AzResourceLock -ResourceGroupName lock -LockName LockGroupNotDelete