A Fine Slice Of SQL Server
Tom Collins makes SQL Server pat its head and rub its tummy:
Question: Is it possible to take a SQL Server database transaction log backup and full backup at the same time ? Are there any conflicts ?
Read on for the answer. Tom gives us a quick answer as well as some more detailed conditions.
Comments closedRich Benner reminds us to clear that backup history:
One thing we regularly come across with a new client is large system databases. Something that SQL Server should do but doesn’t is clearing up system tables. One of the more common issues is a very large backup history which causes bloat in the msdb database. Let’s find out how to clear backup history.
These tables store information about backups that you have taken, which is extremely useful … for a time. After that useful time, it’s just unnecessary data that is taking up space on your server. There’s a number of related tables that store backup history, and it’s not simple to delete data from them all. Most don’t have dates stored in the table so you have to join them together and make sure you delete in the correct order.
Read on for a built-in procedure, as well as a warning. It’s interesting to see, sometimes, just how much space msdb is taking up with stuff people don’t realize. Backup history is usually one of the offenders, along with SQL Agent history and (especially on pre-2016 instances) SSIS history.
Comments closedRobert Haas says, don’t delete that file:
I’m sure you already know what I’m going to tell you: “Of course you need that backup_label file. How could you even think that you don’t need that backup_label file?” Well, you’re right. That is what I’m going to say. But do you know why you need that backup_label file? If you were to remove that backup_label file (or fail to create in the first place, in cases where that is your responsibility), what exactly is the bad thing that would happen to you?
Read on to learn what the file does, how you get it, and why it’s so important to keep around.
Comments closedMatthew McGiffen joins two great flavors:
In SQL Server you can also compress your encrypted backups. Unlike TDE this has been possible with Backup Encryption since the feature was first made available, and there have been no issues that have required fixing – though as always you should still test that restores work correctly. As mentioned in my post about compression with TDE, compressing backups has benefits not just in terms of file size but potentially also in reduced backup times as the time taken to write to disk is smaller.
Read on for more information. Microsoft did the right thing: they compress first and then encrypt; otherwise, you’re not getting any benefit from the compression.
Comments closedBacking up your PostgreSQL database is a critical task for ensuring the safety and availability of your data. In the event of a hardware failure, software error, or other disaster, having a recent backup of your database can mean the difference between a brief outage and a catastrophic data loss. In this blog post, we’ll cover best practices for backing up PostgreSQL database.
Click through for some notes on various backup utilities (pg_dump, pg_dumpall, pg_basebackup), when you might want to use each, and a few more topics.
Comments closedSteve 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 closed