Query Store Backing up within the BAK

  • I do development and do not have access too client servers or our own production servers.  When there are performance issues we will take a bak local and restore it. Some DBs restore with the query store information while others look empty.  Is there a way to ensure that data gets in there.  I can't seem to google to figure out how.  I just can find that the data for query store is stored in the primary file group.

    As for the settings in the properties for query store just assume they are default mostly.  Nothing special in the settings.  I can see data in the management views

    Lastly these servers are 2016 and 2017

     

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • If Query Store is enabled on a given database, when that database gets backed up, the information within Query Store will also be backed up. No extra steps are necessary to make this happen. It's just a part of the database, like any other table, procedure or view. The page-by-page copy that a BACKUP database command does, includes the Query Store data.

    Now, if you're not seeing Query Store data, it's likely one of three things. First, you're not actually using a BACKUP/RESTORE process to move the databases around, but instead are using some type of import/export or something else. Second, Query Store is not enabled on that database, so there is no Query Store data in it. Third, someone is running the command to clear the Query Store data either before the backup is taken, or after the restore is processed.

    Those are the mostly likely causes from the info provided.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Thanks for the reply.  This must just be one of those strange behaviors.  All three of those reasons I thought about and none of them are this case.  The client resolved it by giving me access to the server so I could do the research necessary.

  • Hmmm... Sorry. I can't think of anything else it could be. A backup includes Query Store and there's no step you have to do or command or anything. It's just built right in.

    Ah, maybe, unlikely, but possible, the dates on the systems are different so Query Store thought it had to do a cleanup? Yeah, probably not, but I'm trying to come up with all the reasons why the data might be gone.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • The only other things I can thing of would be the Edition of the local instance or the compatibility mode.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply