Get script for every action in SQL Server Management Studio

By:   |   Comments (4)   |   Related: > 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

SQL Server Management Studio (SSMS) provides a very good option to generate scripts for any operation performed through the GUI

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

SSMS options to generate script for actions

  • 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

  • Right click on SQL Server 2008 instance in SSMS and select Properties
  • Click on Advanced option in left panel
  • Select 'Transact-SQL access enabled" for the Filestream Access Level option
  • Before clicking OK to save the setting, click on arrow pointer next to the Script option

click on SQL Server 2008 instance in SSMS and select Properties

  • 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.

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


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

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

  • 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

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

  • Select third option "Script Action to Clipboard"
  • Click OK button to disable the particular login or Cancel to just have the script.

Now you can paste the script as needed to confirm the operation.


Example 4: Create database backup through SSMS (non express edition) and directly create a schedule job for this action

  • Right click on appropriate database in SSMS
  • Go to Tasks and click on Back Up option
  • Provide backup name and path along with other required customized options
  • Before clicking OK to save the action, click on arrow pointer provided with Script option

Create database backup through SSMS (non express edition) and directly create a schedule job for this action

  • Select the fourth option "Script Action to Job"

A job configuration window will open with the create backup script already present in the job step. Note: this option will not be enabled in SSMS SQL Server Express edition.


Shortcuts

Instead of using the options through the menu in upper part of the GUI frame, we can also use shortcuts for any of the four options. These are noted below:

 use shortcuts for any of the four options

Next Steps
  • Use this valuable feature of SSMS and make sure to save every script that is manipulating data, objects, environment or permissions on your database server
  • Click here to enable script generation for table operations through SSMS GUI
  • T-SQL code generated through this option may be reused in future
  • For effective use of SSMS have a look at these valuable tips


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Atif Shehzad Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Thursday, November 8, 2012 - 1:59:37 AM - Atif Shehzad Back To Top (20256)

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.

 


Wednesday, November 7, 2012 - 11:32:57 AM - Ben Back To Top (20246)

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.


Tuesday, November 6, 2012 - 10:23:59 PM - Atif Shehzad Back To Top (20237)

@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.

 

 


Tuesday, November 6, 2012 - 3:50:05 PM - Ben Back To Top (20233)

Can you script an instance change?  So like:

CHANGE INSTANCE ServerName\InstanceName  ?















get free sql tips
agree to terms