A Fine Slice Of SQL Server
Mikey Bronowski continues a series on replacing SSMS click-and-click-and-click with dbatools cmdlets:
Taking backup is an essential task for everyone working with any kind of data. SSMS offers a wide range of options for taking backups, and at least the same you can achieve using dbatools.
Mikey covers backup and restoration, history and throughput, and even some stuff on snapshots.
Comments closedValerie Parham-Thompson gives us a few methods for backing up PostgreSQL databases:
There are at least four ways to back up a Postgres database: SQL dump, filesystem snapshots, continuous archiving, and third-party tools.
For each, notes on the recovery point objective (or RPO, which is a measure of how up-to-date your application and business needs will require the data to be) and recovery time objective (or RTO, which is a measure of how quickly the restore needs to be completed after an outage begins) are provided. You should weigh these indicators against your business requirements for the data in question.
Read on to learn more about each of these processes.
Comments closedArun Sirpal asks and answers a question:
Question, can you take a built-in backup ( a backup that Microsoft takes as part of the service) and create a new database from it on a NEW server in a different region?
Let’s find out.
Yes, let us.
Comments closedGrant Fritchey shows how you can back up a database on Amazon’s RDS:
Which results in the following:
Msg 262, Level 14, State 1, Line 1
BACKUP DATABASE permission denied in database ‘HamShackRadio’.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Completion time: 2020-06-26T08:34:23.5511314-04:00In short, by default, you can’t backup SQL Server databases on RDS. However, that’s by default. We can make some changes.
Read on to see the proper way of backing up a database hosted in RDS.
Comments closedDavid Fowler shows how you can back up databases to Azure Blob Storage:
SQL Server has given us the option to backup our databases directly to Azure BLOB storage for a while now but it’s not something that I’ve had all that much call to use until recently.
So this is just going to be a quick walk through on how you can backup your on premise SQL Servers to Azure BLOB storage. I’m going to assume that you’ve already got an Azure account, if you haven’t, you get set up a free trial which will see you good for this demo.
Performance typically won’t be as good as backing up locally to disk, so if you need the fastest backup performance and cloud storage, the best route would be to write backups to disk and have a separate process which migrates them to Blob Storage, S3, or wherever. But in many cases, doing this directly can work out just fine, especially if you are already using an Azure-based VM.
Comments closedJohn Morehouse gives us one way to restore a database in Docker:
Here are the steps that we will take to make this work:
1. Download one of the sample databases from I have a “mssql” directory in my local profile to make things easier
2. Make sure the container is started. You can issue a “docker ps” command terminal to see which containers are running
3. Create a directory within the container
4. Copy the sample database backup file into the directory you just created
5. Restore the database onto the SQL instance that is running within the container
The set of steps is fine and it’s what I normally do, though someone did suggest to set up an external volume linking, e.g., /var/opt/mssql/backups outside the container. That way, you can drop your backup file in and it’ll be there without the copy step.
Comments closedWilliam Assaf diagnoses an issue:
If you’ve tried doing Backup to URL with SQL Server using a Shared Access Signature (SAS) certificate and received this error:
Error: 18204, Severity: 16, State: 1.BackupDiskFile::CreateMedia: Backup device ‘https://account.blob.core.windows.net/container/folder/msdb_log_202005170801.trn’ failed to create. Operating system error 50(The request is not supported.).Cannot open backup device ‘https://account.blob.core.windows.net/container/folder/msdb_log_202005170801.trn’. Operating system error 50(The request is not supported.). [SQLSTATE 42000] (Error 3201) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013). NOTE: The step was retried the requested number of times (1) without succeeding. The step failed.
You may have received the same error I encountered.
This error popped up only after startup of SQL Server. To resolve the problem, we’d recreate the SAS key, using the same cert in the same script, and the backups would start working again. This affected all types of SQL database backups.
William did some troubleshooting and explains the core problem. It’s a weird one.
Comments closedDave Bland talks about surviving an audit:
Don’t volunteer information
Only provide the evidence they are asking for, nothing more. You want to avoid giving them things that they do not ask for. By doing so, you might expose something that you don’t want them to see. The last thing you want to do, is give them a reason for documenting a finding. Another thing to remember is the audits have a defined scope.
This is some of the best advice about audits out there, particularly when combined with the other points Dave makes about being honest and doing the right thing. Dave also ties in some information about backup history as a particular auditing example.
Comments closedStuart Moore covers some of the pains of database restoration in two posts. First, why dbatools’ Restore-DbaDatabase is a complicated as it is:
At first glance Restore-DbaDatabase looks like a slow lumberig complex beast. In reality it’s not that bad.
It’s the result of design decisions I took in wanting a solid versatile command that could cope with everything that people would want from it.
In this post, we’ll go through the main decisions/points of contention one by one
Stuart then covers the limitations of Restore-DbaDatabase:
Like all tools, Restore-DbaDatabase isn’t able to do everything that everyone wants it to. Certainly, at the moment I’d like it to write it’s own blog posts and fetch me a cold beer, but that doesn’t happen
A lot of the below isn’t complaining about people asking for features. If we can do it, we will, and we’re keen to make this work for as many people in as many situations as possible
But quite a few requests over the years have been non starters for a number of reasons.
Read them both; they’re part of Stuart’s 31 Days of Backup and Restore with dbatools series.
Comments closedStuart Moore takes us through backup tuning options in dbatools:
Backup-DbaDatabase produces a lot of output, but the one you’re really going to be interested in is Duration. You’re job is going to be to minimise this as much as possible without impacting database performance.
To test changes in isolation you’ll want to remove the biggest variables in backup performance, network and storage speed. To do this, there’s a magical backup device that’s got near infinite speed and storage you can use. This is the black hole known as the bit bucket, /dev/null or NUL:, everything thrown into this pit disappears as fast as it’s put in.
Read the whole thing. And if you’re interested in a systematic method of testing and understanding the impact of these settings on your system, I have a presentation on the topic.
Comments closed