Automating "Generate SQL Script"

You can execute the above-created procedure either by passing right parameters or by using the default parameters as shown below

Exec sp__GenerateScript '',
  'D:\program files\Microsoft SQL Server\
    mssql\upgrade\',
  'D:\mssqlscripts\'

This statement will generate scripts for all of the databases on the current server and dump all of the scripts to d:\mssqlscripts\yyyymmdd folder, where yyyymmdd is today's date

or

Exec sp__GenerateScript

This statement will generate scripts for all of the databases on the current server and dump all of the scripts to d:\mssqlscripts\yyyymmdd folder, where yyyymmdd is today's date. This uses default parameters.

or

Exec sp__GenerateScript 'MSDB', 
  'D:\program files\Microsoft SQL Server\
    mssql\upgrade\',
  'D:\mssqlscripts\'

This statement will generate scripts for the database MSDB on the current server and dump all of the scripts to d:\mssqlscripts\yyyymmdd folder, where yyyymmdd is today's date.

Note

Parameter 1 is the database name. If you leave it blank, it scripts all the databases. Eg: '' or 'Master'. Default value is ''

Parameter 2 is the path information of scptxfr.exe Default value is 'C:\program files\Microsoft SQL Server\mssql\upgrade\'

Parameter 3 is the path information of the destination folder where you would like the scripts do be dumped. Default value is 'D:\MSSQLScripts\'

Parameter 4 is the various optional switches used in scptxfr.exe Default value is ' /X /Y /A /q /r /G /I '

All of the above Execute statements produce similar messages on the output widow as shown below. All the above Execute statements will create subfolders under d:\mssqlscripts, as shown in the figure FIG 1.2

Results

Generate Script - Started
Aug 27 2004 11:48PM
Information:D:\mssqlscripts\20040827 already exist. Skipping Folder Creation
"D:\program files\Microsoft SQL Server\mssql\upgrade\scptxfr.exe" /s SQL
  /d Anand /F D:\mssqlscripts\20040827 /X /Y /A /q /r /G /I
output
------------------------------------------------------------------------------
Scripting completed
NULL

"D:\program files\Microsoft SQL Server\mssql\upgrade\scptxfr.exe" /s SQL
  /d master /F D:\mssqlscripts\20040827 /X /Y /A /q /r /G /I
output
------------------------------------------------------------------------------
Scripting completed
NULL

"D:\program files\Microsoft SQL Server\mssql\upgrade\scptxfr.exe" /s SQL
  /d msdb /F D:\mssqlscripts\20040827 /X /Y /A /q /r /G /I
output
------------------------------------------------------------------------------
Scripting completed
NULL

"D:\program files\Microsoft SQL Server\mssql\upgrade\scptxfr.exe" /s SQL
  /d Northwind /F D:\mssqlscripts\20040827 /X /Y /A /q /r /G /I
output
------------------------------------------------------------------------------
Scripting completed
NULL

"D:\program files\Microsoft SQL Server\mssql\upgrade\scptxfr.exe" /s SQL
  /d pubs /F D:\mssqlscripts\20040827 /X /Y /A /q /r /G /I
output
------------------------------------------------------------------------------
Scripting completed 
NULL

"D:\program files\Microsoft SQL Server\mssql\upgrade\scptxfr.exe" /s SQL 
  /d pubs_subs /F D:\mssqlscripts\20040827 /X /Y /A /q /r /G /I 
output
------------------------------------------------------------------------------
Scripting completed 
NULL

"D:\program files\Microsoft SQL Server\mssql\upgrade\scptxfr.exe" /s SQL 
  /d Ship /F D:\mssqlscripts\20040827 /X /Y /A /q /r /G /I 
output
------------------------------------------------------------------------------
Scripting completed 
NULL

Aug 27 2004 11:50PM
Generate Script - Completed

Conclusion

This article examined how to automate the "Generate SQL Script" by using a stored procedure to take advantage of "scptxfr.exe" provided by Microsoft. Scheduling this procedure to run daily will generate one folder for every day along with all the scripts.