Query Store and Log Backups

A question that came up recently around Query Store is what happens when there are log backups in use on the database. Let’s talk about it.

Query Store and Log Backups

The core of the answer is very simple. Query Store, like any other data written to a database, whether a system table or a user table, is a logged operation. So, when you backup the database, you’re backing up Query Store data. When you backup the logs, you’re also backing up Query Store data. A point in time will include all the data written to the Query Store at that point.

However, that’s the kicker. At what point was the Query Store information written to disk? By default, there’s a fifteen minute cycle before the Query Store moves the data from memory to disk. After it gets written, it will be available through your log backups. Before it gets written though, if you take a log backup, then whatever is in memory is not going to be included. So it does come to down to getting the right point in time.

You can control things though. If you wanted to, let’s say as part of of taking the tail log backup, ensure that anything in memory for Query Store was written to disk first, you can. Run the following command and everything in memory for Query Store gets flushed to disk:

EXEC sys.sp_query_store_flush_db;

For a very tiny amount of additional detail, read here.

Conclusion

There’s really only a very minor gotcha around the Query Store when it comes to log backups. If it’s on the disk for the point in time you’re recovering to, it’s available. If it was in memory at that point in time, it’s not. If you are concerned about the Query Store and log backups, just make sure you flush the data to disk more frequently or using the command.

I have lots more information on the Query Store to share. You can even hear me talk about it in person.

Last chance to sign up for the full-day seminar I’m putting on before SQLSaturday Boston, on September 21st. You can go here to register.

Another event in Europe will be at SQLSaturday Munich on October 26, 2018. Go here now to join the class.