Automating "Generate SQL Script"

Wednesday Sep 1st 2004 by Muthusamy Anantha Kumar aka The MAK
Share:

MAK examines how to automate the "Generate SQL Script" by using a stored procedure to take advantage of "scptxfr.exe" provided by Microsoft.

MAK examines how to automate the "Generate SQL Script" by using a stored procedure to take advantage of "scptxfr.exe" provided by Microsoft.

Step 1

Create the folder "MSSQLScripts" on the SQL Server box's hard disk drive.

Eg:

MKDIR d:\MSSQLScripts

Step 2

Execute the source code below in query analyzer. [refer Fig:1.1] This will create the procedure "sp__GenerateScript."

Source Code

set quoted_identifier off
go
use master
if exists (select * from dbo.sysobjects where id = 
  object_id(N'[dbo].[sp__GenerateScript]')
  and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp__GenerateScript]
GO
create proc sp__GenerateScript
@dbname varchar(256) = '',
@Applicationpath varchar(700) =
  'C:\program files\Microsoft SQL Server\mssql\upgrade\',
@destinationpath varchar(700) ='D:\MSSQLScripts\',
@switches varchar(200) = ' /X /Y /A /q /r /G /I '
as
set quoted_identifier off
set nocount on
set concat_null_yields_null off
declare @count int
declare @folderexist int
declare @maxcount int
declare @query varchar(1000)
declare @date varchar(10)
set @date = convert(varchar(10),getdate(),112)
set @count =1
Print 'Generate Script - Started'
print getdate()
set @Applicationpath = @Applicationpath +'scptxfr.exe'
create table #dbtable (id int identity(1,1), dbname varchar(256))
if @dbname = ''
begin
      insert into #dbtable (dbname) select name from
master..sysdatabases
      where   name != 'tempdb'  and status & 32 != 32
      and status & 256 != 256   and status & 512 != 512
      and status & 1024 != 1024   and status & 4096 != 4096
      and status & 32768 !=32768   and status & 1073741824 !=1073741824
end
else
begin
      insert into #dbtable (dbname) select name from
master..sysdatabases
      where   name != 'tempdb'  and status & 32 != 32
      and status & 64 != 64   and status & 128 != 128
      and status & 256 != 256   and status & 512 != 512
      and status & 1024 != 1024   and status & 4096 != 4096
      and status & 32768 !=32768   and status & 1073741824 !=1073741824
      and name = @dbname
end

if (select count(*) from #dbtable) = 0
begin
      set @date = convert(varchar(100), getdate(),109)
      Print 'Error: No valid database found for Generating Script'
end
else
begin
      set @destinationpath = @destinationpath +@date
      create table #files (Files int, Folder int, parent int)
      insert #files exec master.dbo.xp_fileexist @destinationpath
      select @folderexist = Folder from #files
      if @folderexist <>1
      begin
      set @query = 'MKDIR "'+@destinationpath+'"'
      print @query
      exec master..xp_cmdshell @query
      set @destinationpath = @destinationpath
      end
	else
	begin
	print 'Information:'+ @destinationpath + ' already exist. Skipping Folder Creation'
	end
      set @maxcount =  (select max(id) from #dbtable)

While @count <= @maxcount
begin
      set @dbname = (select dbname from #dbtable where id = @count)
      set @query = '"'+@applicationpath +'"'+ ' /s '+@@servername+ '
	    /d '+@dbname+ +' /F '+@destinationpath+ @switches
      set @query = @query
      print @query
      exec master..xp_cmdshell @query

if @@error <> 0
begin
Print 'Error'
end

set @count = @count+1
end

end
print getdate()
Print 'Generate Script - Completed'


--Usage:
--exec sp__GenerateScript '','D:\program files\Microsoft SQL Server\mssql\upgrade\','D:\mssqlscripts\'
--exec sp__GenerateScript
--exec sp__GenerateScript 'MSDB','C:\program files\Microsoft SQL Server\mssql\upgrade\','D:\mssqlscripts\'
--exec sp__GenerateScript 'TOM','C:\program files\Microsoft SQL Server\mssql\upgrade\','D:\mssqlscripts\'

--xp_cmdshell '"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'

Download the above stored procedure here.


Fig 1.1

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

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved