SQL Server Club
Home Articles Essential Guides Blogs Links Twitter Facebook Join Contact About
How to Schedule a SQL Server Database Creation Script
Web Monkey About the Author

With security clearance and well over a decade of experience with SQL Server, Web Monkey is the resident DBA at Norb Technologies and a real database guru.

A regular contributor to SQL Server Club, his background includes software development, hardware, a smattering of essential networking knowledge and an unhealthy interest in web security. He has an obsession with automating repetitive tasks, but has a tendency to forget where he left his last open bag of fruit and nut...

Typepad Profile RSS Feed Web Monkey on Twitter
Send to a Friend Printer Friendly Version
Whilst an entire database script can be manually created from Enterprise Manager quite easily, or an entire database's structure transferred on a scheduled basis to create a skeleton database on another server using DTS, scheduling a script creation is not particularly obvious or well documented.

One of the more unusual problems facing a SQL Server DBA is how to create and maintain object creation scripts. Objection creation scripts can easily be generated in Enterprise Manager by right-clicking on a database, choosing All Tasks, then selecting Generate SQL Script. This script writes to a .sql file and contains the T-SQL to create all, or a selection of the objects within a database.

This is useful for quickly getting the code to create a stored procedure or a table, to have a backup script, or to re-create a database structure or just some of its objects on another server. This is particularly important in a development environment where it may be a requirement to take a copy of the drop/create scripts for all objects in the database at regular intervals and place it in source control.

Alternatively it may be an audit requirement in certain organizations to do this on a regular basis, especially with the emergence of Sarbanes-Oxley compliance requirements.

Where to Find SCPTXFR
The SCPTXFR.EXE utility is part of the standard SQL Server 7.0 and 2000 installation (although it appears to be missing in SQL Server 2005 beta), and if you've created a default installation, can be found in:
C:\Program Files\Microsoft SQL Server\MSSQL\Upgrade

Its original purpose was as part of the upgrade process that allowed SQL Server 6.5 databases to be migrated to SQL Server 7.0 and 2000. However it is also very useful in a day-to-day environment, as it allows you to programmatically generate scheduled scripts of database objects. SQL-DMO also allows you to do this.

However it involves coding up an application to do what SCPTXFR already does very well. SCPTXFR is exceptionally useful, as it allows a DBA to automatically generate a complete database script every night at a fixed time without being in attendance, as described below.

How to Use SCPTXFR
To get command line help on the SCPTXFR command, type either SCPTXFR /? or SCPTXFR -?. Note that you will probably have to either change to the directory in which SCPTXPR resides, or specify the full path of the utility when executing it, as it is usually not picked up as a default path in Windows or DOS:
C:\Program Files\Microsoft SQL Server\MSSQL\Upgrade>scptxfr -?SCPTXFR /s {server} /d {database} {[/I] | [/P {password}]} {[/F {script files directory}] | [/f {single script file}]} /q /r /O /T /A /E /C /N /X /H /G /Y /?
/s name name specifies the source server on which the database that is to be scripted resides.
/d name name specifies the name of the database to script.
/I Use trusted NT security for the login.
/P The password for the 'sa' account, if used. Note that if /I is not used the login ID is always set to 'sa' to avoid scripting permission problems. If /P is not used or if a password is not specified after the /P, a null password is used. /P Cannot be used with /I, as /I implies that the trusted NT account of the currently connected user is to be used, rather than the sa account.
/F name Creates a separate file for each category of objects. 'name' is the path plus folder name of the directory into which the script files should be generated.
/f name Creates a single file in to which all objects are scripted. 'name' is the path plus filename of the file into which the script is to be saved. Not compatible with /F.
/q Generate identifiers with quotes around the name.
/r Includes drop statements for each of the scripted objects.
/O Generates OEM script files. Cannot be used with /A or /T (default behavior).
/T Generates UNICODE script files. Cannot be used with /A or /O.
/A Generates ANSI script files. Cannot be used with /T or /O.
/? Returns this help information on the command line. Also works with -?.
/E Stops scripting when an error is encountered. (Default: log errors, and continue)
/C Specify the number of a codepage that will override the default server codepage.
/X Specifies that stored procedures and extended stored procedures should be scripted to separate files.
/H Generates scripts without a header. (Default: Headers turned on)
/G Specifies that the name of the server be used as a prefix for the output files.
/Y Generate Extended Properties script (only valid for SQL Server 2000 and above). This option allows extended properties such as column descriptions to be scripted.

NB: Note that if you omit the space between any of the options and a parameter, you will get the error:

"Invalid command arguments"


SCPTXFR /sMYSERVER ... -- returns an error
SCPTXFR /s MYSERVER ... -- works correctly

The two main options available are NT or SQL Server security, and the choice of writing the generated scripts into either a single file or into multiple files. If the latter option is chosen, individual files are given separate extensions, as detailed at the end of this article. In the latter case, the files generated will be named in the format server name.database name.extension.

The remaining options are mainly concerned with output formatting.

Real World Examples
To automatically script a database, a scheduled job can be created to run a T-SQL command, such as in the following examples. NB: All examples use a database server called DEVSVR.

Example 1 - Return the command help to check that you have SCPTXFR in the right place. If not you'll need to search for it on your server's hard disk and replace the directory where it resides:

master..xp_cmdshell '"C:\Program Files\Microsoft SQL Server\MSSQL\Upgrade\scptxfr.exe" /?'
Example 2 - To script the contents of the master database out to a single file :
master..xp_cmdshell '"C:\Program Files\Microsoft SQL Server\MSSQL\Upgrade\scptxfr.exe" /s DEVSVR /I /d master /f c:\masterscriptfile.txt'
Example 3 - To script out each object category (stored proc, table, trigger, etc...) to a separate file :
master..xp_cmdshell '"C:\Program Files\Microsoft SQL Server\MSSQL\Upgrade\scptxfr.exe" /s DEVSVR /I /d msdb /F c:\scriptfolder'
Note the use of the capital 'F' here to specify a Folder instead of a file - mixing the two up often causes problems when you use SCPTXFR the first few times and usually results in puzzling 'access denied' permission errors.
Example 4 - Generate a script to contain the contents of the Accounts database and generate a separate file containing all of the DROP scripts:
master..xp_cmdshell '"C:\Program Files\Microsoft SQL Server\MSSQL\Upgrade\scptxfr.exe" /s DEVSVR /I /d Accounts /F c:\temp2 /r'
Example 5 - Generate a script to contain the contents of the master database, and generate a separate folder containing the scripts date stamped with the date on which it was run:

declare @command varchar(1000)
declare @texttime varchar(10)
set @texttime = convert(varchar, getdate(), 102)
set @command = '"C:\Program Files\Microsoft SQL Server\MSSQL\Upgrade\scptxfr.exe" /s svdb3 /I /d master'
set @command = @command + ' /F c:\temp\' + @texttime + ' /r'
print @command
exec master..xp_cmdshell @command

Example 6 - Finally, write the script to re-create every database on the server, one file per database:

create proc sp_ScriptDatabase @dbname sysname
declare @command varchar(1000)
declare @texttime varchar(10)
set @texttime = convert(varchar, getdate(), 102)
set @command = '"C:\Program Files\Microsoft SQL Server\MSSQL\Upgrade\scptxfr.exe" /s svdb3 /I /d ' + @dbname + ' /f c:\temp\' + @dbname + '_' + @texttime + '.txt /r'
print @command
exec master..xp_cmdshell @command
exec sp_MSForeachDB "exec sp_ScriptDatabase ?"

Once you have experimented with the examples above, insert a call to the stored procedure into a scheduled job to run at the hour you require, and you should be able to schedule a script of any database quickly and at any time, and produce a full historic archive of database creation scripts.

SQL-DMO Dependencies
If you run SCPTXFR.EXE specifying a target folder that does not exist, you may receive the following error:
exec xp_cmdshell '"C:\Program Files\Microsoft SQL Server\MSSQL\Upgrade\scptxfr.exe" /s SVDB3 /I /d msdb /F c:\notafolder\'

Scripting failed.ERROR: Executing Script transfer
Error Code: 7b Error
Category: 0
Source: Microsoft SQL-DMO Description: [SQL-DMO]CreateFile error on 'c:\notafolder\'. The filename, directory name, or volume label syntax is incorrect.

Of interest here is the fact that the error seems to originate in SQL-DMO, implying that SCPTXFR uses SQL-DMO to create the script. Therefore whether or not this utility will work with SQL Server 2005 is open to speculation. It may be re-written to use the SQL-SMO library instead, or may be replaced with an alternative, graphical tool.

At the time of writing, SCPTXFR was not included in the SQL Server 2005 beta release, but may re-appear in the RTM release or be replaced by a GUI based alternative that is even simpler to use.

File Extensions Generated by SCPTXFR
If the /F option flag is included to generate a file per object category, the files will be named with the following extensions:

DEF Create script for defaults DP1..nDropscriptforstoredprocedures
DR1 Drop script for non-stored procedures
FKY Create script for foreign key constraints
ID1..n Create script for indexes
LOG Error log
PRC Create script for stored procedures
RUL Create script for rules
TAB Create script for tables
TRG Create script for triggers
UDF Create script for user defined functions
UDT Create script for user defined types
USR Create script for user logins
VIW Create script for views

This is not an exhaustive list but details the more common file extensions.

Whilst the command line interface to SCPTXFR does not offer the same comprehensive range of script output options as SQL-DMO or Enterprise Manager, for purposes such as generating an entire database script each night for source control or auditing purposes it is ideal.

As a way of simply automating the daily load and allowing the DBA to concentrate on less mundane technical issues, it is perfectly suited to provide your business with the audit information it needs.

If you found this article useful, please visit our sponsors who keep us going...

SQL Defrag Studio 2009
Fragmented indexes can cause your SQL Server to gradually slow down until it grinds
to a complete stop. Performance may be slow but you'll need to solve it fast.

SQL Defrag Studio finds fragmented indexes and automatically defrags them for you.
Send to a Friend Printer Friendly Version
Top of Page
© Norb Technologies 2007-2010 Privacy Policy Norb Technologies Devdex Feedback Home
Feedback Form