A Fine Slice Of SQL Server
Steve Jones didn’t follow his own advice:
In the previous post, I did these things:
- took a backup
- added a table and data
- took a second backup
- truncated the table
- took a third backup
If I restore the default last backup, I get my table without data. You can read that post to see how I got here.
Let’s restore things.
Read on to see how.
Comments closedMatthew McGiffen lays out the requirements:
When we talk about protecting our at-rest data, the item that we are likely to be most concerned about is the security of our backups. Backups are generally – and should be – stored off the server itself, and often we will ship copies offsite to a third party where we don’t have control over who can access the data, even if we trust that that will be well managed.
From SQL Server 2014 the product has included the ability to encrypt data while creating a backup. This feature is available in both the standard and enterprise editions of SQL Server, so it is something you can use even when TDE may not be a feature that is available to you.
Click through for a primer on the topic.
Comments closedMatthew McGiffen shares some advice:
Database backups continue to work without change when you have TDE enabled. The only difference is that the backups contain encrypted data that cannot be read without the certificate and private key. There are a couple of points that are worth discussing though.
Click through for several notes, including a warning to those still on SQL Server 2016 and woefully under-patched.
Comments closedSteve Jones didn’t keep ’em separated:
I had a question on multiple backups in a file and had to check my syntax. This post shows how to see which backups are in a file.
Note: Don’t do this. Put backups in separate files.
Still, if you didn’t follow Steve’s good advice here (or you have an edge case situation where you, for some reason, need to store multiple backups in the same file), there’s a way to check what’s in a file.
Comments closedAndrew Pruski has a video for us:
A while back I posted a blog on how to recover data with crash consistent snapshots.
Snapshots are pretty handy in certain situations so I thought I’d show you them in action!
Want to watch the video? Click through for the video. Just make note that there’s no audio on it, so don’t turn your speakers way up in the hopes that you can hear Andrew.
Comments closedMatt Pearson and Luke Davies restore a PostgreSQL database:
I had a conversation with another DBA about interview questions, and one interview topic that came up was using pg_basebackup to restore a database. I had a horrible realisation that I had not done a restore using pg_basebackup in PostgreSQL 15. With modern backup tools, using pg_basebackup is like using a manual screwdriver instead of an electrically-powered screwdriver; it gets the job done, but much more effort is involved.
However, sometimes pg_basebackup is the only tool available.
So, in this blog, we’ll look at PostgreSQL’s recovery options and their implications for restoring. We’ll also look at a simple restore using pg_basebackup when a user fails. I’ll be using a PG 15 database for these tests.
Click through for the process and a demonstration.
Comments closedPerry Skountrianos takes us through some recent changes:
In SQL Server 2019 (15.x), the ADR cleanup process is single threaded within a SQL Server instance. Beginning with SQL Server 2022 (16.x), this process uses multi-threaded version cleanup (MTVC), that allows multiple databases under the same SQL Server instance to be cleaned in parallel.
MTVC is enabled by default in SQL Server 2022 and uses one thread per SQL instance.
Read on to see how you can change that, as well as additional product updates.
Comments closedAndrea Allred recovers from a mistake:
Recently, the wrong table got dropped and we needed to bring it back. I had never done a restore in an Azure Managed Database before so I learned something really fast.
Click through for the process. And yeah, it is quite easy, though I’ve noticed that restore times are a bit slower than if you were using local hardware on-premises.
One neat trick with database restores in Azure SQL DB: you can’t restore over an existing database, something a client wanted me to do last week. What you can do, however, is restore the database under a new name, so we might have messedupdb and then messedupdb_restore. Well, in this case, messedupdb had no changes since “the incident,” so what we were able to do was rename messedupdb to messedupdb_dropme and rename messedupdb_restore to messedupdb. Azure SQL DB happily rolls on with this and after ensuring that the database was now in prime condition, we could drop the old version. It’s a little more complex than simply restoring over the existing database, but all the relevant metadata Azure SQL DB needs stayed in sync along the way, so the process was smooth.
Chad Callihan backs that database up:
Keeping on the recent PowerShell trend, let’s use PowerShell to accomplish a primary task of any database administrator: backups. With PowerShell and dbatools, you can do a simple backup or add a range of options to fit your needs.
I’d also like to call out that it’s really easy to set configuration options with dbatools, such as buffer count and max transfer size.
Comments closedJose Manuel Jurado Diaz diagnoses an error:
Today, we worked on a service request that our customer got the following error message: BACKUP WITH COPY_ONLY cannot be performed until after the next automatic BACKUP LOG operation [SQLSTATE 42000] (Error 41937) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013), running a manual backup.
Click through to learn when you might see this error and what you can do about it.
Comments closed