Blog Post

Moving SQL Prompt History to Another Machine

,

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:

2024-01-09 12_59_53-SQLQuery10.sql - ARISTOTLE_SQL2022.compare2 (ARISTOTLE_Steve (121)) - Microsoft

I can’t remember who the users are, but I add a new query to this window, as shown here.

2024-01-09 13_00_25-SQLQuery10.sql - ARISTOTLE_SQL2022.compare2 (ARISTOTLE_Steve (121))_ - Microsoft

All that is captured in SQL History. In one of the menu bars in SSMS, you can see this listed with other Redgate products.

2024-01-09 13_00_35-SQLQuery10.sql - ARISTOTLE_SQL2022.compare2 (ARISTOTLE_Steve (121))_ - Microsoft

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.

2024-01-09 13_00_45-Moving SQL Prompt History to Another Machine - Open Live Writer

This is the latest version, but if I click down, I see the previous version without the SELECT.

2024-01-09 13_00_54-

I can go back further and see before I added the ALTER ROLE statement.

2024-01-09 13_01_00-SQL History - Microsoft SQL Server Management Studio

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.

2024-01-09 13_10_37-SQL Prompt 10

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.

2024-01-09 13_13_43-Choose a database 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.

2024-01-09 13_14_32-DB Browser for SQLite - C__Users_Steve_AppData_Local_Red Gate_SQL Prompt 10_SqlH

If I click up a few, I see the other version.

2024-01-09 13_14_40-DB Browser for SQLite - C__Users_Steve_AppData_Local_Red Gate_SQL Prompt 10_SqlH

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.

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating