sp_query_store_flush_db, Query Store, and Backups

One of the cool things about Query Store is that you can back it up with the database.

This isn’t perfect for every situation, because you must back up the whole database. If you’ve got a multi-terabyte database, your Query Store isn’t super portable! But still, it’s great to have an option.

There are a few things worth knowing about how Query Store works with backups and restores.

If Query Store is read/write when you back up a database, it restores the same way

Let’s say your production database is collecting data with Query Store. When you restore a full backup elsewhere, the Query Store data from production restores with it, and Query Store stays in the same read-write state.

Maybe you want this, maybe you don’t!

It’s a good idea to check if Query Store is enabled and its settings after a restore. You can do this simply with the following query:

SELECT *
FROM sys.database_query_store_options;
GO

This is a database level query, so use the database before you run it.

If you want to back up very recent activity in Query Store, run sys.sp_query_store_flush_db before the backup

Query Store was designed to be clever, and to minimize its impact on your performance. Query Store only flushes its data from memory to disk periodically. You get to control this by setting the data flush interval in the Query Store settings for a database. (Read more about this in Microsoft Documentation here.)

The default value for Query Store data flush is 15 minutes. That means that in the case of a crash, you might lose up to around 15 minutes of activity.

Even if you’ve lowered this, you might want to make sure that a backup contains the very latest activity, particularly if you’re taking the backup to get Query Store data for someone to look at.

You can manually flush Query Store data to disk before the backup by running the built in procedure, sys.sp_query_store_flush_db in that database.

Want to play around with some sample code?

Here is some very simple code that creates a database, enabled query store, and lets you easily play around with and test sys.sp_query_store_flush_db.