Press "Enter" to skip to content

Category: Backups

Restoring SQL Managed Instance Backups to SQL Server 2022

Mladen Andzic has a preview around how we can take a Managed Instance backup and go on-premises:

Restoring a backup file is the easiest way to copy a SQL Server database to another instance. It allows you to create a copy of your production database for easier troubleshooting or debugging of an issue, to provide a copy of a database to your end users or eligible third parties, or as a light-weight business continuity/disaster recovery solution to restore functionality on another instance of SQL Server. These are just a few use cases, and the list is much longer and there are some very inventive ways of using backup-restore in the wild.

This article explains the challenges of cross-release restore to an older version of SQL engine and announces the private preview of a capability to restore a backup of a database taken from Azure SQL Managed Instance to instance of SQL Server 2022. 

So much SQL Server functionality has been built with the idea of getting you from on-premises into the cloud (specifically Azure) but it’s good to see them spend some development effort on the entirely reasonable and realistic scenario that Azure is not the best choice for a company and there are many such companies still willing to throw money at Microsoft for a good product.

Comments closed

Searching for SQL Server Backup Locations

David Fowler can’t remember where those backups went:

Sometimes I find remembering where a particular server sends its backups to a nightmare.

You might have servers backing up to different locations, you might have different locations for individual databases and different locations for your fulls, diffs and logs. You might be trying to get your head around a customer’s set up, where the backups make no logical sense at all.

Whatever you’re up to, at some point, for some reason you’re going to need to access your backup location to get at the files.

Read on for a Powershell script which can help out with this task.

Comments closed

Point-in-Time Recovery with Azure SQL DB and Managed Instances

Ahmed Mahmoud looks at point-in-time recovery and answers some frequently asked questions:

On some occasions, after the failover is initiated, the current Primary DR will start a new backup chain from that point and old backups are available on the current secondary DR. If we want to restore the backups which exists in Secondary it will not allow us to perform, apparently restore cannot be initiated on the Primary as the backup is not available.

Also, sometimes we observe in secondary DR for few databases, PitR restore point is available and for few databases it shows “no restore point available”    

Read on to understand why that happens and what you can do about it.

Comments closed

Using S3 Object Storage in MinIO with SQL Server 2022

Anthony Nocentino takes us through an example of integrating with MinIO via its S3 integration:

In this post, I will walk you through how to set up MinIO, so you can use it to work with SQL Server 2022’s s3 object integrations. Working with s3 and SQL Server requires a valid and trusted TLS certificate. This can be a pain for some users and environments. So I’m writing this post so you can get off the ground running with this new feature set in SQL Server 2022. The certificate we’re working with here is self-signed. You could get a real certificate for your environment, and that’s encouraged. But this walk-through intends to get you up and running fast so that you can test out SQL Server’s s3 object integrations. We’re using MinIO’s free GNU AGPL v3 edition and running it in a docker container for our s3 compatible object storage and SQL Server 2022 CTP 2.0, which is also running in a container.

Click through for the demo, in which Anthony sets everything up and then backs up a database in SQL Server 2022 to MinIO.

Comments closed

Backups to S3 in SQL Server 2022

Anthony Nocentino tries out backup to S3 in SQL Server 2022:

In s3 object storage, a file is broken up into as many as 10,000 parts. In SQL Server, the each part’s size is based on the parameter MAXTRANSFERSIZE since this is the size of the write operation performed into the backup file. The default used for backups to s3 compatible storage is 10MB. So 10,000 * 10MB means the largest file size for a single file is about 100GB. And for many databases, that’s just not big enough. So what can you do…first you can use compression. That will get more of your data into a single file.

This right here is the pain. Anthony shows a few ways to extend this number but there’s still a hard cap on maximum backup size, one we don’t have on-premises.

Comments closed

Creating a SQL Server 2022 dacpac

Kevin Chant gets an upgrade:

In this post I want to cover how you can create a dacpac for SQL Server 2022 databases using sqlpackage. So that you keep the new SQL Server 2022 compatibility level when you deploy new databases.

Just to clarify, a dacpac file is a special type of file which contains details about SQL Server database objects. Which you can use to deploy database updates to other SQL Server databases.

Read on for initial thoughts, a post-upgrade experience, and more.

Comments closed

Seeding AG Replicas from Snapshots in SQL Server 2022

Anthony Nocentino is excited about using storage snapshots in SQL Server 2022:

But what if I told you that you could seed your Availability Group from a storage-based snapshot and that the re-seeding process can be nearly instantaneous?

In addition to saving you time, this process saves your database systems from the CPU, network, and disk consumption that comes with direct seeding and using backups and restores to seed.

This process described in this post is imlemented on Pure Storage’s FlashArray and works cloud scenarios on Pure’s Cloud Block Store.

Click through to see how.

Comments closed

Pre-Staging Log Shipping Backups with dbatools

Jess Pomfret doesn’t waste time:

Log shipping is a SQL Server feature used for disaster-recovery where the transaction log backups are ‘shipped’ from your production instance to a secondary instance. This enables you to cutover to this secondary server in the event of a disaster where you lose your primary instance. Log shipping is not a new feature but is still quite popular.

Recently I was tasked with setting up Log Shipping for a reasonably large production database. I didn’t want to initialize the secondary database with a new full backup as I was already taking full and log backups of the database. In this case we have the option of initialising the database by restoring the full & log backups up to the latest point in time and then configuring log shipping.

Read on to see how you can use dbatools to do this easily.

Comments closed

Backups with Checksum

Chad Callihan tempts Betteridge’s Law of Headlines:

When you’re specifying WITH CHECKSUM as you’re backing up databases, SQL Server will use checksums to help catch any inconsistencies with pages. This seems like a setting that you should always use and would expect to be a default setting. So why doesn’t SQL Server include it by default?

Using the principle that a backup isn’t valid until it’s verified, CHECKSUM acts as a useful but not sufficient check.

Comments closed