Introduction

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.

Why Use SCPTXFR?

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 /?

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

"Invalid command arguments"

eg:

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

createproc sp_ScriptDatabase @dbname sysname
as
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
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.

Summary

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.