Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Query Store and Log Backups

DZone's Guide to

Query Store and Log Backups

Let's take a quick look at how log backups affect the Query Store in SQL Server as well as explore some code.

· Database Zone ·
Free Resource

RavenDB vs MongoDB: Which is Better? This White Paper compares the two leading NoSQL Document Databases on 9 features to find out which is the best solution for your next project.  

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 back up the database, you're backing up Query Store data. When you back up 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 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. Please let me know your thoughts or any questions you may have in the comments. 

Get comfortable using NoSQL in a free, self-directed learning course provided by RavenDB. Learn to create fully-functional real-world programs on NoSQL Databases. Register today.

Topics:
database ,sql server ,query store ,logs ,backups

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}