A customer was asking about SQL Prompt recently and how their history is stored and if it can be moved to another machine. This post shows how this works, and how you could move it. There is also a video walk through at the bottom.
Caution: This is history for your specific queries, and moving the database means you overwrite history on the new machine. Don’t do this to share code. Do this only if you are upgrading your own machine.
This is part of a series of posts on SQL Prompt. You can see all my posts on SQL Prompt under that tag.
SQL Prompt History
History in SQL Prompt is a slightly hidden, but amazing piece of technology. A number of customers find it incredibly valuable to see the history of their queries, especially when they’ve gotten busy with a number of different pieces of code.
In SSMS, when SQL Prompt is installed, it is keeping track of what you write in queries. Don’t worry, this is local, and it’s on your machine. Let’s look at this.
Say I have a query window, like this:
I can’t remember who the users are, but I add a new query to this window, as shown here.
All that is captured in SQL History. In one of the menu bars in SSMS, you can see this listed with other Redgate products.
When I click this and open it, I see my query windows in the upper left, with the window text in the right, and below on the left, a timeline.
This is the latest version, but if I click down, I see the previous version without the SELECT.
I can go back further and see before I added the ALTER ROLE statement.
Finding History
All of my data is stored in my local profile. I can browse the path under AppData Local for my user, and inside there is a Redgate folder. Under that is SQL Prompt 10 (or your version). If I sort by date modified, I see my SqlHistory.db file, which is the database of my SQL History in Prompt.
If I were changing to a new machine and wanted history to move with me, I’d copy this file to the new machine. Unfortunately, I can’t put this in a VCS, as the path is set.
As I cautioned above, this isn’t something you send to colleagues. This is an individual file of your work and only copy this to a new machine when you are upgrading, not trying to sync work. Use a VCS for proper code control.
Browsing the Database
This is a SQLLite database, and any tools that let you look at the data work. I have the sqllitebrowser tool on Windows, which works great. I can click “Open Database in this tool and browse to my file.
Inside here, I see my schema and tables and if I scroll down to the bottom of the QueryVersions table, I can see my query in the last entry in the Contents field. Note the query on the right looks like the one in SSMS.
If I click up a few, I see the other version.
No magic here, just good solid programming that saves you a lot of time and helps you deal with the chaos of software development and the frailty of the human mind.
If you haven’t tried SQL Prompt, download the eval and give it a try. I think you’ll find this is one of the best tools to increase your productivity writing SQL.
Video Walkthrough
I made a video of how this works if you’d prefer to watch this. You can also see all my SQL Prompt Tips.