Tag: SSMS

SQL

How to script out ALL or FEW SQL Server Jobs using SSMS

Today, I have come across a requirement to generate the sql script for SQL Server Jobs (only few out of thousands). Most of us are familiar with an approach by right click on the sql job and choose ‘Script Job as’ to script out individually, but this is such a tedious job if you have couple of sql jobs to be scripted.

This post explains another simpler way using “Object Explorer Details“. It allows you to select multiple jobs to be scripted as below.

  1. Choose Object Explorer Details Menu from View main menu in SSMS.
  2. Select “SQL Server Agent” -> “Jobs” in Object explorer which lists all the SQL Agent Jobs in Details.
  3. Select the jobs (ALL or FEW) you want to script and then right click to select the scripting option.

The above steps will create all the selected jobs in a single query window as per the selection.

If you enjoyed this blog post, please feel free to share it with your friends!

SQL, SSMS

Dark theme in Sql Server Management Studio

Many people prefer dark theme for computer displays. It actually enhances the visual ergonomics by reducing eye strain and providing comfort of use at all conditions.Another important benefit, it conserves battery power, there by enabling device to run longer period of time.

Please refer this link for more details.

Now, let us take a look at the fact that dark theme can be applied to SSMS and how do we do and what are implications.

Writing this blog, I am using SSMS 18.2 version, the latest by now. In future, the facts may change if MS decided to make a change.

Do we have Dark theme by default available in SSMS 18.2?

The answer is NO. We do not have an option to change the SSMS into a dark theme by default.However, it provides a way for us to change to “Blue”, “Blue (Extra Contrast)” and “Light” as below.

How do we make SSMS in Dark theme?

There are two ways from my analysis:

1. Custom your SSMS window using Tools -> Options -> Environment -> Fonts and Colors.

The challenge here is to customize each and every items from the list and provide the color combinations. Honestly, this is something very challenging for me to identify the better combinations. There is a blog written by Aaron Betrand on the same, and its really helpful for the basic settings.

2. Change the SSMS settings

There is a file called ssms.pkgundef in the path – “Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE“. This file is where you will place registry entries that are to be removed during setup of your application. If you look at the content of this files, you can see the below code:


// Remove Dark theme
[$RootKey$\Themes\{1ded0138-47ce-435e-84ef-9ec1f439b749}]

The above code, actually removes the dark options from SSMS while setting up of SSMS. To enable dark theme for SSMS, you can simply comment the code as below and it will give the option of “Dark” in Tool -> Option -> General -> Color Theme as below.

Please be aware, this is a hack or undocumented way of changing SSMS config files. It may or may not work and its not supported by Microsoft. This may be made unavailable or documented because this may not be interoperable with other Windows display settings. So please be use this as an option at your own risk.

SQL, SSMS

How do we recover unsaved query window in SQL Server Management Studio?

How do we recover the unsaved query window in SQL Server Management Studio?

This is a simple tip to recover your unsaved query window in SSMS.

As a SQL server professional, most of us will work closely with SSMS in a daily basis.At times, we forget to save our work in the management studio and for some reason if the query window closes/SSMS crashes, we will be in a position of “lost everything”. You may be questioned on your best practises. Here is a way to recover the SSMS window content which would save your time and effort.

You can verify the below path and get the files:

C:\Users\<>\Documents<>\SQL Server Management Studio\Backup Files

The above path may change depends on the windows version.

How does it happen?

There is a setting in Management Studio that allows the Autorecover option enabled by default. We can modify the default settings as per our need to reduce the risk of loss.

Go to SSMS -> Tools -> Options -> Environment -> AutoRecover

There are two different settings:

1) Save AutoRecover Information Every Minutes

This option will save the SQL Query window file at certain interval specified. You can set this value carefully on how frequent that you need to save your SSMS cahnges.

2) Keep AutoRecover Information for Days

This option will preserve the AutoRecovery information for specified days.

SQL, SSMS

Cycle Clipboard ring in SQL Server Management Studio

“Cycle Clipboard” or “CTRL + SHIFT + V” is a short cut operation for pasting the items in the clipboard. This feature can paste last 20 items in the clipboard memory.

This is a very efficient method for developers to paste items not only the last copied, also any of the last 20 items. We need to just iterate through the clipboard memory by pressing CTRL+SHIFT+V until we get the desired item to be pasted.

Hope you enjoyed this tip, will reach you with more soon.

SQL, SSMS

TIP – SSMS : Color Code your Server

If you are a DBA and deal with lots of servers, especially, production servers, you need to be very careful on applying code changes or configurational changes. Any execution by mistake may lead you trouble, infact a very BIG trouble.

Here is a small TIP, you can follow while you connect to server – a color code method.

Step 1: While you connect the Server, select option .

Step 2: Go to Connection Properties and “Use custome color”.( I use RED for Production Servers, it is a personal choice anyway)

Once you connected with this option enabled, then whenever you connect to the server it uses RED color as the background for status bar.

Tip_SSMS

This tip was very useful for me for a long time, hope you will also enjoy…