SSMS Quick Tip: Never lose your script from overwrite again!

Twenty something years ago when I started my SQL Server career there was an amazing tool called Query Analyzer. Honestly I’d say if Microsoft did nothing more than bring this tool back and call it Azure Data Studio I would have been excited and beyond happy.

I bring up this relic of a tool because it’s here that I got into the habit of using the “Insert File” shortcut that was on the toolbar. If you’ve worked as a DBA or SQL developer for any reasonable amount of time you can probably relate to having 20+ query windows open. Back then I’d have people come up to my desk and they ask about a perf issue or to kill a SPID. The problem is that I’d open a saved script and then type/edit things in it and leave it open. This was all good until one day the company started restarting machines nightly and the tool would auto save on close. Ouch! Everything was gone or completely broken. Sure you can put your things in Git now-a-days but this is much easier, trust me.

I said “Quick” tip right? Let’s get to it! This is so simple you’re going to laugh and when you start this habit you’ll never go back. Instead of using the “Open File” shortcut to open a script, click New Query and then choose “Edit” & “Insert File As Text…”.  You’ll see this link has been added to my standard toolbar in the screen shots below. More on this in a moment. For now just look at the before and the after.

Open a script with the “Open File” button: Note that the saved file name is listed. Saving anything here overwrites your file. Yikes!

Now Choose “New Query” instead and then “Insert File As Text…” from the Edit menu.

Note that the query script has been “inserted” but the file name says “SQLQuery10.sql”. Yay! No chance to overwrite this one!

If you’d like to add the “Insert File as Text…” button to your toolbar here’s a few screen shots showing how:

Customize the toolbar and click “Add Command”.

Choose “Edit” on Categories and then select “Insert File”.

Move the command to your preferred location with the “Move Up” or “Move Down” buttons.

And now it’s there