A Fine Slice Of SQL Server
In this video, I show how to restore SQL Server databases using SQL Server Management Studio, T-SQL, and the dbatools PowerShell module.
Originally, I had plans on covering SSMS + T-SQL in one video, and then dbatools in a second. Then I decided, well, why not just cover both of them in the same video? And this is how we get to half-hour long videos.
Comments closedI wrote a blog post a couple of weeks ago entitled Is pg_dump a Backup Tool?. In that post, I argued in the affirmative, but also said that it’s probably shouldn’t be your primary backup mechanism. For that, you probably shouldn’t directly use anything that is included in PostgreSQL itself, but rather a well-maintained third-party backup tool such as barman or pgbackrest. But today, I want to talk a little more about why I believe that pg_dump is both amazingly useful for solving all kinds of PostgreSQL-related problems and also just a great piece of technology.
The core value proposition of pg_dump is that the output is human-readable text. You’ll get DDL commands that you can use to recreate your database objects, and you’ll get COPY commands (or INSERTs, if you so request) that you can use to reload your table data. That is not really an advantage if you’re just trying to back up and restore an entire database cluster, because converting all of your data from PostgreSQL’s internal formats into text and back again is going to use a bunch of CPU resources. If you instead take and restore a physical backup, you can avoid all of that overhead.
Read on for scenarios in which pg_dump can be quite useful.
Comments closedIn this video, I show how to perform a variety of database backup operations via PowerShell, using the dbatools PowerShell module. I also show how easy it is to test a database backup using dbatools.
I finally have a video shorter than 10 minutes long. Don’t worry, the next one will blow right past that figure.
Comments closedIn this video, I show how to perform a variety of database backup operations via T-SQL, as well as how (and why) to back up to NUL and how to back up a database to a network share.
This one is not quite as lengthy as the prior video in the series: just 20 minutes instead of 30. That said, I do cover quite a bit of content around taking backups, something that every infrastructure DBA should be familiar doing.
Comments closedMd Ullah restores a database using Azure Data Factory:
Prerequisite
- Azure SQL Managed Instances are located across two distinct subscriptions.
- Azure Blob storage same subscriptions SQL Managed Instances are located
- Azure Data Factory (ADF) instance
Read on for necessary permissions and a note to turn off Transparent Data Encryption before giving this a go.
Comments closedRobert Haas shares some thoughts:
Recently, I’ve been hearing a lot of experienced PostgreSQL users reiterate this line: “pg_dump is not a backup tool.” In fact, the documentation has recently been updated to avoid saying that it is a backup tool, to widespread relief. Experienced PostgreSQL users and developers have been publicly called out for having the temerity to assert that pg_dump is, in fact, a backup tool. I find this narrative deeply frustrating, for two reasons.
Click through for those reasons.
Comments closedIn this video, I cover the three recovery models available to SQL Server, describe (most of) the types of backups you can take, and spend a bit of time covering Recovery Point Objective & Recovery Time Objective.
The advice for video length that people tend to give is 8-10 minutes. I typically average closer to 15 minutes per video. This one is nearly 30 minutes long because there’s just so much information to cover, even with me repeatedly saying “Don’t worry, I’ll cover this bit in a future video.”
Comments closedI’ve started a new video series:
In this video, I explain why it is so important to back up your databases. From there, we see one technique for taking these backups in SQL Server: using SQL Server Management Studio. We review some of the backup metadata you can collect and also see how to compress and encrypt backups.
The first few videos in the series have all gone a bit longer than I expected, and that’s even with me pointedly ignoring most everything to do with tape drives.
Comments closedGrant Fritchey provides an important public service announcement:
Please, let me reiterate: The only valid test of a backup is a restore.
THE
ONLY
VALID
TEST
OF
A
BACKUP
IS
A
RESTORE.
Grant is absolutely correct here. And do you know how hard it is to test a SQL Server backup? With dbatools, it’s this hard: Test-DbaLastBackup. That one cmdlet restores a backup (under a different name so it doesn’t clobber your existing database), runs DBCC CHECKDB against the restored data, drops the test database after the fact, and even allows you to do all of that against a different SQL Server instance.
Incidentally, my next YouTube series just happens to be around backup and restoration in SQL Server, so stay tuned if you’re interested.
Comments closedJosephine Bush makes and restores a backup:
I wanted to test a migration of Azure PostgreSQL from simple to flexible. I wanted a simple db to restore onto my single server. I may not have needed to do this, but I was then curious how one would go about dumping and restoring on Azure Postgres in general. I plan to use the Azure migration service, but since I started this dump/restore exercise, I thought I would document it here.
Read on for the process.
Comments closed