Introduction
Many times, new database applications are written and
developed on test machines. Once completed, these new databases must somehow
be moved or transferred to a production server. Moreover, while there are
currently no Microsoft tools to handle SQL database source control
automatically, there are a number of different methods available for us to
transfer SQL objects from one server to another. This article will examine
several of the methods available in SQL 2000.
Generating a SQL Script
SQL Server can automatically create scripts of all your
database objects. This script can then be run inside Query Analyzer to
recreate the objects on to a different server. No data will be transferred,
only the structure of your database. In this example, the pubs database will be
copied off a test server onto a production server. To begin, open Enterprise
Manager and navigate to the pubs database. Right click pubs and select "All
Tasks," "Generate SQL Script."
When the General tab first appears, all of the options will
be grayed out. Clicking the Show All button will populate the object list. To
select every pubs database object, check the "Script all objects"
box.
On the Formatting tab, "Generate the CREATE" and "Generate
the DROP" are automatically selected. As their title implies, our script
will first drop the object being transferred if it already exists on the target
server, then create anew. If the "Include scripts for all dependent
objects" box is selected, then any user defined data types or other
dependent objects will also be scripted. The "Include descriptive headers
in the script file" box will automatically create script documentation.
To create a script including extended stored procedures (Extended Stored
Procedures are usually dlls’ written in C. In the next rev of SQL, we will see
extended stored procedures written directly to the CLR), select the "Include
extended properties" box. The last box, "Only script 7.0 compatible
features", when selected, will remove any references to objects that are
supported in SQL 2000 but were not supported in SQL 7, such as user defined
functions and INSTEAD OF triggers. For the complete list of items that will be
dropped from the script, see BOL "Generate SQL Scripts (Formatting Tab)."
In this example, all options were checked except for "Only script 7.0
compatible features," which was left unchecked.
The "Options" tab will give us control over
security and indexing choices. The first box, "Script database," if
checked, will create the database itself, the pubs database in our case. If
left unchecked and the database did not exist on the target, none of the
objects would be created. The database must exist or be created in the script
by checking the "Script database" box. The next three options script
security, users, logins and permissions. In the next group of options,
indexing and key choices can be made. Notice we have the ability to script
full text indexes, very handy if you are doing search development. For the "File
Format" options, "MS-DOS" would be selected if your script would
be run from a command line like isql. Select "ANSI" for Query Analyzer
and "Unicode" if special international characters are involved.
The last group gives you the option to create one file
total, or one file per object. In this example, all options were selected with
ANSI text and "Create one file."
Click OK and give the file a name. On the target server,
start Query Analyzer and open the script just created. Executing it will
create a complete pubs database on the target. No data, but the complete
structure. If instead of creating pubs, we wanted a copy, we could search and
replace each word "pubs" with "pubs2." This would create a
new pubs2 database as a complete copy of the original.
Detach / Attach
The Detach Attach method is probably the quickest and
easiest way to transfer a database structure with its data. Using Detach
releases the mdf and ldf from SQL server. Once released, we can copy or move
the actual database files to the target server. Once there, Attach will
register them on the new server. This method can be done from the command line
or Enterprise Manager. From the command line, use sp_detach_db and sp_attach_db.
From Enterprise Manager on the source server, right click the database, All
Tasks, Detach Database. Once detached, copy the mdf and ldf to the new
server. When the files are moved, open Enterprise Manager on the target
server, right click "Databases," All Tasks, Attach, and browse to
your newly moved files.
Data Transformation Services
DTS, Data Transformation Services, is a heavy weight tool
set designed to transfer, merge and manipulate data. Not only can both our
structure and data be moved, but also data can be altered as it is
transferred. In addition, Data Transformation Services allows your transfer
options to be saved, as a "package," and reused over and over. For
our example, we are only concerned about using DTS to move our database off the
test server and on to the production server. From the test server, right click
the database, select All Tasks, Export Data. This will bring up the opening
DTS screen. Select next, and then select our pubs database.
On the next screen, "Choose a Destination," select
the target server. To move all of our database objects, (tables, views, stored
procs, UDFs, etc), select "Copy objects and data between SQL Server
databases" from the "Specify Table Copy" or Query screen, then
click next. Use the defaults on the "Select Objects to Copy" screen
and click next. Run immediately and our database will be transferred. For DTS
transfer, processing, and data storage dot net scripting examples see "In
the Trenches, Advanced Techniques and Real world Solutions" published by
Beyond Books.
Backup / Restore
Both structure and data can be moved to a target server by
using the Backup and Restore methods. Like Attach / Detach, the method can be
done from both the command line and Enterprise Manager. From Enterprise
Manager on the source server, right click pubs, select All Tasks, Backup Database
and OK. Next, from the target machines Enterprise Manger, right click
Databases, All Tasks, then Restore Database. Select "From Device"
from the restore options, then navigate to your backup file made on the
previous step. The default location would be c:\program files\microsoft sql
server\ mssql\backup. Click OK until back to the original Restore screen. Make
sure the Restore Name is correct and click OK. The database has now been
transferred.
Conclusion
SQL 2000 has several methods for transferring databases from
server to server. The correct method will depend on if data needs to be
transferred and what type of access to the remote server you have. If version
control is needed, a third party product will be required as none of the tools
mentioned support it. However, for server-to-server moves, there are many
choices.
