Get script for every action in SQL Server Management Studio

Problem

I am always conscious to keep a record of all operations performed on my database servers. Operations through T-SQL in an SSMS query pane can easily be saved in query files. For table modifications through SSMS designer I have predefined setting to generate T-SQL scripts. However there are numerous database and server level tasks that I use the SSMS GUI and I would like to have a script of these changes for later reference. Examples of such actions through the SSMS GUI are backup/restore, changing compatibility level of a database, manipulating permissions, dealing with database or log files or creating/manipulating any login/user. I am looking for any way to generate T-SQL code for such actions, so that it may be kept for later reference. Also I would like to be able to reuse this T-SQL code for database tasks or scheduled jobs if needed.

Solution

SQL Server Management Studio (SSMS) provides a very good option to generate scripts for any operation performed through the GUI. It is an effective way to save the T-SQL code of actions performed through SSMS. Here is list of some of the tasks categories for which you may generate T-SQL scripts from SSMS GUI actions

Changing any server instance level option

Changing any database level option

Managing server roles, logins, permissions

Managing database roles, users, permissions

Backup/Restore operations

Managing policies (SQL Server 2008)

The above mentioned categories cover almost all operations that you may require to have script for. Now here are the simple steps to use this powerful option of SSMS.

Open SSMS GUI for any required task

Configure values in the GUI window

Before clicking OK, find the Script option in the upper top of the GUI frame as shown below

Click on the down arrow pointer and four options will be displayed to manipulate the action script

Choose the appropriate option and click OK to complete the required task

The options shown are pretty self explanatory. You can directly open the script in a SSMS query window, directly save the script to a .SQL file or put the script in the Windows clipboard to paste where required. The last option is related to scheduled jobs and it will not be enabled in SSMS SQL Server Express edition.

Here are some scenarios to use this simple yet powerful option of SSMS. You may go through any of these examples to get familiar with the functionality.

Example 1: Enable filestream "Transact-SQL access enabled" option and open script for this action in a new SSMS query pane

Before clicking OK to save the setting, click on arrow pointer next to the Script option

Select first option "Script Action to New Query Window"

The script is now in a new SSMS query pane and you can click the OK button to complete the action or Cancel to just have the script.

It is notable that instead of choosing the option from the drop list through small arrow, if you click directly on the Script option the script will be created in a new query Window, because this is the default option.

Example 2: Create a new database through SSMS and save the script for this action in .SQL file

Right click on Databases folder

Choose to "New Database.." from menu

Enter name for new database and configure any other required options

Before clicking OK to save the setting, click on arrow pointer provided with Script option

Select second option "Script Action to File"

Save the script file by providing a name in the file save dialogue and you may click OK button to create the database or Cancel to just have the script.

Example 3: Disable a Login through SSMS and copy the script for this operation to clipboard

Right click on a login in Security folder in SSMS and select Properties

Click on Status option in left pane

Check the "Disabled" radio button

Before clicking OK to save the action, click on arrow pointer provided with Script option

Select third option "Script Action to Clipboard"

Click OK button to disable the particular login or Cancel to just have the script.

About the author

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter
I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.

That is some thing of different scope than generating script of SSMS GUI tasks. It relates to sql cmd and u have to provide instance info along with login credentials. Script for this instance change would not be generated.

My thought was to make multi-server deployments executable in a single script. A co-worker showed me a way to do it.

In SSMS go to the Query drop-down and turn on SQLCMD Mode.

In your script use this syntax:

:connect server\instance

GO

Note that it does not really change the connection of the tab/window, rather it only changes the connection during that part of the script until the script ends or another connection is specified. So when that execution is complete you will still be connected to the instance you were originally, before the command was issued.

@Ben. The basic concept delievred in this tip is that we may generate script for tasks executed through SSMS GUI. I think changing the instance name is not provided any where in SSMS GUI tasks, so its script would not be generated. If you have any scenario through SSMS GUI then please share.

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.