SQLServerCentral Article

How to Schedule a SQL Server Database Creation Script



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. This article describes how to do this using Microsoft's SCPTXFR utility, in response to a recent query in the SQL Server Central forums.


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. It's original purpose was as part of the upgrade process which 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 which is to be scripted resides.
/d <name><name> specifies the name of the database to script.
/IUse trusted NT security for the login.
/PThe 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 into 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.
/qGenerate identifiers with quotes around the name.
/rIncludes drop statements for each of the scripted objects.
/OGenerates OEM script files. Cannot be used with /A or /T. (Default behavior)
/TGenerates UNICODE script files. Cannot be used with /A or /O.
/AGenerates ANSI script files. Cannot be used with /T or /O.
/?Returns this help information on the command line. Also works with -?.
/EStops scripting when an error is encountered. (Default : log errors, and continue)
/CSpecify the number of a codepage which will override the default server codepage.
/XSpecifies that stored procedures and extended stored procedures should be scripted to separate files.
/HGenerates scripts without a header. (Default : Headers turned on)
/GSpecifies that the name of the server is used as a prefix for the output files.
/YGenerate 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 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 datestamped 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'


exec master..xp_cmdshell @command

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

createproc 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'


exec master..xp_cmdshell @command

execsp_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 ScriptTransfer

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:

DEFCreate script for defaults

DP1..nDrop script for stored procedures

DR1Drop script for non-stored procedures

FKYCreate script for foreign key constraints

ID1..nCreate script for indexes

LOGError log

PRCCreate script for stored procedures

RULCreate script for rules

TABCreate script for tables

TRGCreate script for triggers

UDFCreate script for user defined functions

UDTCreate script for user defined types

USRCreate script for user logins

VIWCreate 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.


4 (5)

You rated this post out of 5. Change rating




4 (5)

You rated this post out of 5. Change rating