Press "Enter" to skip to content

Category: Backups

T-SQL Snapshot Point-in-Time Recovery to Azure VM

Published 2024-09-05 by Kevin Feasel

Anthony Nocentino continues a series on T-SQL snapshot backups:

In this post, the third in our series on using T-SQL Snapshot Backup, I will guide you through using the new T-SQL Snapshot Backup feature in SQL Server 2022 to take a snapshot backup and then perform point-in-time database restores using that snapshot backup as the base, but this time using an Azure Virtual Machine. We will explore how to manage Azure storage-level operations, such as taking snapshots, cloning snapshots, and executing an instantaneous point-in-time database restore from the snapshot with minimal impact on your infrastructure. Additionally, I will demonstrate a PowerShell script that utilizes dbatools and Azure Az modules to automate the process.

Read on for the script and plenty of details.

Comments closed

Thoughts on T-SQL Snapshot Backups

Published 2024-09-04 by Kevin Feasel

Anthony Nocentino has a two-parter for us. First is the idea of how T-SQL snapshot backups work:

Traditional SQL Server backups can struggle with large databases, resulting in longer backup times and resource contention. T-SQL Snapshot Backup, a new feature in SQL Server 2022, addresses these challenges by allowing storage-based snapshots to be coordinated through T-SQL. This feature delivers faster, more efficient backups, especially for large-scale environments with the most aggressive of recovery objectives.

The follow-up is for point-in-time recovery from a T-SQL snapshot backup:

In this post, the second in our series, I will guide you through using the new T-SQL Snapshot Backup feature in SQL Server 2022 to take a snapshot backup and perform point-in-time database restores using a snapshot backup as the base of the restore. We will explore how to manage storage-level operations, such as cloning snapshots and executing an instantaneous point-in-time restore of a database from the snapshot with minimal impact on your infrastructure. Additionally, I will demonstrate a PowerShell script that utilizes dbatools and the PureStoragePowerShellSDK2 modules to automate the process.

Check out both posts and be on the lookout for subsequent entries in the series.

Comments closed

pg_dump and the Backup Tool Debate

Published 2024-08-26 by Kevin Feasel

Gulcin Yildirim Jelinek explains the debate around whether pg_dump is a backup tool or not:

Recently, while writing about the vulnerability affecting pg_dump, the topic of decommissioning pg_dump came up on Twitter. Unlike the nostalgic feelings many had for Pluto, there was less reluctance to see pg_dump reclassified. In fact, some people were eager to retire it as a backup utility, and I even got a bit of pushback for still referring to pg_dump as one

I was talking to my colleague Simona the other day, and she mentioned that everybody in Postgres circles says, “pg_dump is not a backup tool,” but perhaps it’s not always explained well why it is not.

Read on for that explanation.

Comments closed

Checking SQL Server Backup Status with sp_CheckBackup

Published 2024-08-19 by Kevin Feasel

Jeff Iannucci announces a new tool:

Whether you are backing up your SQL Server databases with Ola Hallengren’s scripts, Maintenance Plans, or some third-party software, your backups are your lifeline for recovering your data in case of a disaster.

So…are your databases recoverable? Do you know WHERE your most recent backups were written? Do you know WHEN they were written? Did you verify the backups for recoverability? Have you backed up any encryption certificates associated with your backups?

And if you think know the answers to these questions…are you sure? And can you confirm your Recovery Point Objective (RPO)?

Read on to learn more about the procedure and grab a copy from the GitHub repo.

Comments closed

Backing SQL Server up to S3 Buckets

Published 2024-08-15 by Kevin Feasel

David Fowler backs up a database:

Way back in the mists of time I wrote a post on how to backup SQL server to an S3 bucket using TNTDrive, https://sqlundercover.com/2018/06/18/backup-your-on-premise-sql-server-directly-to-an-aws-s3-bucket/.

Back then, if we wanted to backup SQL to S3 we needed to use a third party tool. Since SQL 2022 things have changed and we’ve now got the option to backup directly to S3 in a similar way that we can backup to Azure BLOB store.

And, going one step further, you can also use PolyBase to read data from S3 buckets in SQL Server 2022.

Comments closed

Filesystem Access for Database Restoration via dbatools

Published 2024-08-07 by Kevin Feasel

Andy Levy shares a lesson learned:

While performing an instance migration this spring, I happened upon something I didn’t expect in [dbatools](https://dbatools.io/). It should have been a simple backup/restore copy of the databases, with the backup files residing on a fileshare on the destination server after being copied there. I kept getting a warning that the backup files I was attempting to restore couldn’t be read, and the restores (via Restore-DbaDatabase) wouldn’t execute.

I checked permissions on the server over and over again. Both on the filesystem and for the share that I was attempting to read from. Even more curious, if I executed the restore database statements directly from within Management Studio, the databases restored without issue.

After doing quite a bit of digging, I managed to find the reason.

Read on to learn more about necessary permissions, as well as the issue Andy hit, as well as the solution.

Comments closed

Restoring a MySQL Table from Filesystem Backup

Published 2024-07-29 by Kevin Feasel

Chad Callihan recovers from a missing database backup:

There may be no worse feeling than needing a database backup and not having one. It ranks right up there with running a DELETE statement and missing the WHERE clause. God help you if you if you suffer both of those together. If you come across that situation with a MySQL database, you might be able to recover what you need.

Read on to see how. Even so, I’d be concerned about what happens if there are foreign key constraints involved.

Comments closed

A Comparison of Backup Types between SQL Server and Oracle

Published 2024-07-24 by Kevin Feasel

Sergey Gigoyan compares two sets of backups:

It is a well-known fact that backing up databases is one of the most common tasks for any database administrator (DBA), regardless of the relational database management system (RDBMS) used. However, the definition, naming, and structure of backup types can differ for each RDBMS. Therefore, if a DBA switches from one RDBMS to another, dealing with backup types can sometimes be very confusing. For instance, switching to Oracle databases after working with SQL Server databases for a long time can cause some difficulties. DBAs can work through these challenges by understanding the differences in database backup types.

Click through for the comparison. This a good reminder that, just because two things have the same name, doesn’t mean they do the same thing.

Comments closed