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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
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


















Thanks for your registration, follow us on our social networks to keep up-to-date