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