dcsimg
Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Sep 1, 2004

Automating "Generate SQL Script" - Page 2

By Muthusamy Anantha Kumar aka The MAK

Step 3

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


FIG 1.2

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.

» See All Articles by Columnist MAK



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM