Learn more about SQL Server tools

solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips














































Scripting out TSQL commands generated by SQL Server Management Studio

MSSQLTips author Greg Robidoux By:   |   Read Comments   |   Related Tips: More > SQL Server Management Studio

Problem
With SQL Server Management Studio you have the ability to do just about everything you can do using T-SQL commands.  One problem with using the GUI is that it is difficult to remember everything you clicked on to reproduce the behavior a second time.  Another issue is that most of what you do through the GUI is done immediately which may not always be the best scenario.  How can I capture what SQL Server is doing so I can reproduce the behavior or run the commands at a later time.

Solution
Another nice new addition to SQL Server Management Studio is the ability to script out most of the commands and operations that you can do through the GUI.  Let's take a look at doing a database backup as an example. 

Let's say we want to backup the AdventureWorks database, but we don't want to run the backup immediately we just want to generate the code.  One option would be to use Books Online and look at the syntax and another is to let the GUI generate the code.

Letting the GUI produce the script

The first thing we need to do is go through the steps of creating a backup via the GUI.  To do this we just right click on the database name and select Tasks -> Back Up.  After this we get a screen that looks like the following.

These are the parameters we have set on the General tab.

These are the parameters we have set on the Options tab.

Once we have selected all of the parameters that we want for our backup, click on the "Script" option in the top middle of the window.  If you click on the down arrow next "Script" you will see four options.

  • Script Action to New Query Window - this will open a new query window and dump the T-SQL code into this new query window
  • Script Action to File - this will allow you to save the T-SQL code to a file
  • Script Action to Clipboard - this will copy the T-SQL to the clipboard so you can paste it into another application
  • Script Action to Job - this will create a SQL Agent job with the T-SQL code and allow you to schedule the job

Here is the output we get when we select any of these options, but I used the "Script Action to New Query Window".  I also reformatted the output a little to make it a little easier to read.  As you can see this is a pretty easy way to generate a lot of code quickly that you can then customize to meet your needs.

BACKUP DATABASE [AdventureWorks] 
   
TO  DISK = N'C:\SQL_Backup\AdventureWorks_full_20080522.BAK' 
   
WITH  DESCRIPTION N'Full backup of the AdventureWorks database'
   
NOFORMAT
   
INIT,  
   
NAME N'AdventureWorks-Full Database Backup'
   
SKIP
   
NOREWIND
   
NOUNLOAD,  
   
STATS 10
   
CHECKSUM
GO
DECLARE @backupSetId AS INT
SELECT 
@backupSetId position 
FROM msdb..backupset 
WHERE database_name=N'AdventureWorks' 
   
AND backup_set_id=(SELECT MAX(backup_set_id
               
FROM msdb..backupset 
               
WHERE database_name=N'AdventureWorks' )
IF @backupSetId IS NULL 
BEGIN 
   RAISERROR
(N'Verify failed. Backup information for database ''AdventureWorks'' not found.'161
END
RESTORE 
VERIFYONLY 
   
FROM  DISK = N'C:\SQL_Backup\AdventureWorks_full_20080522.BAK' 
   
WITH  FILE = @backupSetId,  
   
NOUNLOAD,  
   
NOREWIND
GO

Next Steps

  • Next time you have the need to write some code to perform a certain T-SQL task that you know you can do via the GUI look at using this as alternative option instead of writing all of the code
  • Another option for capturing what the GUI is doing is to use a trace.  In this tip, How does Enterprise Manager get its data,  we look at how you can capture what is occurring.
  • This feature is also available in SQL Server Management Studio Express


Last Update: 5/23/2008


About the author
MSSQLTips author Greg Robidoux
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.