Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

How to Schedule a SQL Server Database Creation Script

By Jon Reade, (first published: 2005/04/14)

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

/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.
/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 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.
/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 which will override the default server codepage.
/N Generate ANSI PADDING.
/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 is 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"

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.

create	proc 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

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 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:

DEF Create script for defaults
DP1..n Drop script for stored procedures
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.

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.

Total article views: 37929 | Views in the last 30 days: 45
 
Related Articles
BLOG

A Script A Day - Day 6 - Drop and Create Database Snapshots

Today's Script will drop all database snapshots and create a database snapshot for all online read w...

FORUM

Create Login Script

Error with Create Login Script

FORUM

script to create indexes existing in a database

script to create indexes existing in a database

FORUM

Create Database

Create database taking forever

ARTICLE

How to Create a Corrupt SQL Server Database

This article details how to create a corrupt SQL Server database for testing purposes

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones