Whew! Yesterday was a long day. Backup and recovery is one of the most important things you can do with SQL Server. As you saw, performing a backup is not difficult. The hard part, however, is knowing when to back up, whether you should perform transaction log backups, and when and how to recover databases. Today you examine how to get data into SQL Server from a file, or out of SQL Server to a file. You also learn how to transfer data and other objects from one database to another (including across SQL Servers).
Using backup and recovery is one way to move data. You could back up your database and recover it on another server. There are a couple of problems with this approach, however. The first is permissions, and the second involves the requirements of the recovery process, as documented yesterday. You examine using backup and recovery to move data in more detail before the end of the day, but there are better options available.
Another way to move data is to use SQL Enterprise Manager's Transfer Management Interface. This is a fairly flexible and simple way to move data, as well as other objects, from one SQL Server system to another. The Transfer Management Interface has a restriction: You can only transfer data into SQL Server 6.5 (although you can get objects and their data from previous versions of SQL Server). The most flexible way (and you guessed it--the hardest way) is to use the bulk copy program, bcp.exe. Because BCP just works with files, you can transfer data to any other application or server, or receive data from almost any other program that can produce an ASCII text file. You should begin by exploring SQL Enterprise Manager's Transfer Management Interface.
The Transfer Management Interface allows you to move SQL Server objects (tables, indexes, stored procedures, and so on), as well as data, between databases. The databases can be on the same copy of SQL Server 6.5, or they can be completely separate servers.
You can copy all objects, or a particular class of objects (for instance, only stored procedures). You can also transfer just the data, without replacing the tables. You can either replace the data in the destination database with the data you are transferring, or append the data you are transferring to any existing data already in the table.
You can also schedule the transfer to occur at some future time, or execute the transfer immediately. This also means that you can use this interface to schedule a recurring transfer. Assume you want to copy some tables from one server to another on a periodic basis. You can accomplish this by setting up the transfer and adjusting its schedule so that it runs at the intervals you require. Discussion of how to adjust a task's schedule is addressed on Day 18, "SQL Server Automation." You find that scheduling tasks, including the transfer task, is very similar to the scheduling backups method discussed yesterday.
As you read through the rest of this chapter's section, you transfer data between two databases on a single copy of SQL Server 6.5. Create a database named transfer, with a 2MB device for data, and a 2MB device for the log. Here's an example script used to create a database:
disk init name = `transfer_data', physname = `d:\mssql\data\transfer_data.dat', vdevno = 20, size = 1024 go disk init name = `transfer_log', physname = `d:\mssql\data\transfer_log.dat', vdevno = 21, size = 512 go Create database transfer on transfer_data = 2 Log on transfer_log = 1 go
You transfer your pubs database to this new database. If you have a second installation of SQL Server 6.5 on your network, you can use a different server.
You should be aware of some requirements before you begin. You can get data from most any previous releases of SQL Server, or even Sybase SQL Server, but the destination server must be version 6.5 of Microsoft SQL Server. You must, however, register every server you'd like to transfer data from or to in Enterprise Manager. You can register them when setting up the transfer, but registering the server is required. You must also run a script on the source server if it is not version 6.5 of Microsoft SQL Server. You can find all of these scripts in the platform-appropriate directory on your SQL Server 6.5 CD-ROM.
Version | Script to Run |
Microsoft or Sybase SQL Server 4.2x | SQLOLE42.SQL |
Microsoft SQL Server 6.0 | SQLOLE65.SQL |
Sybase SQL Server System 10 | SYBSYS.SQL |
You also need the appropriate permissions. On the source server (from where you're getting data or objects), you need select permissions on all the data you'd like to transfer, as well as select permissions on the system tables. You need to be DBO of the database you select on the destination server's database you're transferring data into. Of course, there needs to be enough space on the destination server for the objects/data you want transferred.
Start Enterprise Manager and connect to your server to get to the Transfer Management Interface. Expand your databases folder, right-click your pubs database, and select the Transfer option from the pop-up menu. You are presented with the screen shown in Figure 9.1. The Transfer Management Interface is referred to as the Database/Object Transfer dialog in Enterprise Manager.
Start at the top and work your way down through this dialog. In the Source frame's Server drop-down dialog, you have to specify the server where the data or objects you want to transfer reside, as well as the source database.
If you click the Source Server drop-down box, you see a list of servers that are currently registered in Enterprise Manager. If you do not see the server you'd like to transfer from, you can register a new server here by clicking on the New Source button. You are presented with the Register Server dialog you encountered the first time you started Enterprise Manager. When you've completed the dialog, click Register. You are returned to the Database/Object Transfer dialog with the new server selected in the Source Server drop-down box. If you get an error and you are registering a previous version of SQL Server, make sure you've run the appropriate script (mentioned previously). You could also click the Foreign Source button to connect to a Sybase Server. You cannot use this option to connect to any other type of server.
Figure 9.1. The Database/Object Transfer dialog.
Enterprise Manager connects to that server and fetches a list of all databases that exist on that server after you specify a source server. You can then pick the database from which you'd like to get objects or data. For this example, you should select the pubs database.
Next, take a look at the Destination frame. You select a server just as you did a moment ago. This time, however, you must pick a version 6.5 server. If you click the New Destination button, you are presented with the Register Server dialog. Again, you are logged in to the server you select, and a list of databases are available in the Destination Database drop-down box. In this example, you select the transfer database.
Every check box is selected by default in the Transfer Options frame.
The Copy Schema option specifies that if you request objects to be transferred, the definitions are transferred. One key thing you can do with the Transfer Management Interface is copy data into an existing table (just appending the data into the destination database's table(s)). If you want to do this, you must first uncheck the Copy Schema option. If this option is not selected, data is transferred, but no schemas, or object definitions, go. For example, if this option is not checked, no stored procedures actually transfer, even if you request them. There's no data associated with stored procedures, and by unchecking the Copy Schema option, you've specifically requested not to transfer any object definitions. You should leave the Copy Schema option selected for most transfers.
WARNING: You can select objects to transfer, and not receive any errors--even though those objects won't actually be transferred (such as stored procedures, views, defaults, rules, and user-defined datatypes).
This option does what its name implies. If selected, all objects you request to transfer are dropped in the destination database and are re-created before any data is transferred. If you deselect this option, any objects that already exist in the source database and in the destination database cause an error during the transfer.
This option requests that when you select an object to be transferred, any objects that it depends upon are also transferred. For instance, if you requested a view to be transferred, any tables that the view selects from are also transferred; the view "depends" upon the tables to function properly. This option has produced mixed results (sometimes not all related objects are correctly identified).
This option requests that the data be transferred when you request tables to be transferred. If not selected, data is not transferred. Uncheck this option if you wanted the schema to be transferred.
When this option is checked and you have common tables in the source and destination databases, the data in those tables is replaced with the data from the source. When not checked, the data is appended to the existing data.
You should leave all of these options enabled in the sample transfer you're running.
Advanced Options is the next frame. Both options here, Transfer All Objects and Use Default Scripting Options, are selected by default. Uncheck the Transfer All Objects option. The Choose Objects button becomes enabled. Select this option, and you see a dialog like that shown in Figure 9.2.
Figure 9.2. The Choose Objects to be Transferred dialog.
Select the objects here that you'd like transferred. If you'd like all of a particular object, check the All xxx check box at the top of the dialog. Otherwise, select the objects you'd like to transfer from the Add/Remove Objects window and click the Add button. The objects are moved to the window to the right of this dialog. After you've selected all the objects you want transferred, click the OK button. Leave the Transfer All Objects option enabled for your transfer.
Uncheck the Use Default Scripting Options check box and click the Scripting Options button (see Figure 9.3).
Figure 9.3. The Transfer Scripting Options dialog.
Start with the Object Scripting frame.
Allows you to transfer triggers for each table you select to transfer. No triggers are transferred if not enabled. It is selected by default.
This option enables transferring Declarative Referential Integrity (DRI). This includes Primary Keys, Foreign Keys, and other ANSI constraints such as check, default, and unique constraints. It also includes the IDENTITY property on a column.
This option specifies whether bindings are completed upon a transfer. If you uncheck this box, no sp_bindrule or sp_bindefault stored procedures are run on the destination database. Day 14, "Data Integrity," holds further discussion of these system stored procedures.
As you learned on Day 5, "Creating Tables," each object in a database can have a separate owner. Remember that the default owner for each object is DBO but may be different if someone creates an object while not DBO or aliased to DBO. If you want each object to retain its owner in the destination database, leave this option selected. Otherwise, all objects transfer with an owner of DBO.
If you've created user-defined datatypes and used them when creating tables, this option specifies whether to use them in the transferred tables. If you enable this option, only system datatypes are used. Your user-defined datatypes are transferred if you request as much; however, they won't be used by the create table statements. This has no effect on the actual data stored, but future use of rules and defaults could be affected.
If you've enabled this option, all identifiers are surrounded with double quotation marks (""), and quotation marks in objects such as rules and check constraints are enclosed in single quotation marks (`').
The next frame is for Security Scripting.
This option (which is checked by default) transfers all users and groups in the database you select as the source database. See Day 6, "SQL Server Login and User Security," for more information about users and groups.
This option warrants some attention. It is enabled by default and means that all logins on your source server are transferred to the destination server. You should think carefully before using this option. For security reasons, all the logins are added to the destination server with a NULL password. This may be a real security concern. An error is generated if a login already exists. See Day 6 for more information about logins.
If you bring over database users and groups, you probably want to have permissions assigned properly in the destination server. This option has the appropriate grant and revoke statements scripted for your transfer. See Day 7, "User Permissions," for more information about permissions.
This option brings over statement permissions (the ability to create objects, and the like). For additional information about statement permissions, refer to Day 7.
Index Scripting is the next frame.
This option creates all of the non-clustered indexes for the tables you choose to transfer, in the destination database.
This option creates clustered indexes just as with the non-clustered indexes.
This option creates the clustered indexes after the data is transferred, rather than before. It uses the sorted_data option with the create index statement. See Day 13, "Indexing," for more information about indexes and these options.
This option is only available if you select the previous option. It creates the clustered index with the sorted_data_reorg option, which preserves any fillfactor settings from the source database.
It's recommended that you select the Create Clustered Keys/Indexes After Data Transfer with Sorted Data option, especially for large data transfers. It speeds the data transfers.
For your test, you should uncheck all the options in the Security Scripting frame, and then click OK to return to the Database/Object Transfer dialog.
There's one last option on this screen: Save Transfer Files in Directory. This option specifies the name of the directory that stores the scripts generated by the transfer process. It defaults to the server installation's log directory. If you modify this entry (which is highly recommended) to a new directory, such as adding a directory level, the transfer process creates a new directory for you.
There is one additional concern in specifying the directory here. Transferring data runs the bulk copy program (BCP) to move the data. That means that it temporarily copies all of your data out to operating system files. If you were to transfer a 100MB database, you need to have at least that much free space (and probably more) on the disk drive that you specify in this text box. The directory refers to the destination server by default. If you use a third server to initiate the transfer, the scripts are on that server. You can also use a UNC path here, with the same conditions specified when backup devices were discussed on Day 8, "Backing up and Restoring."
After you've configured all of these options, you can start the transfer by clicking on the Start Transfer button. The Scripting In Progress window opens and displays each stage of the transfer process (see Figure 9.4).
Schedule is the next button. This opens the Schedule Transfer dialog, which is shown in Figure 9.5.
Here you can specify that the transfer occurs immediately, at some point in the future, or on a regular basis. This dialog is exactly like the dialog in yesterday's discussion on scheduled backups. Being able to continue working while the transfer takes place is one reason to use the Schedule option and run the transfer immediately. Your SQLExecutive service must be running for this option to work properly.
Figure 9.4. The Scripting In Progress window.
Figure 9.5. The Schedule Transfer dialog.
View Logs is the last option button on the Database/Object Transfer dialog (beyond the Close and Help buttons). If you run a transfer interactively, this button becomes available after the transfer is complete and any errors or warnings occur. There may be two distinct logs--one from the source server and one from the destination server. If there are any problems encountered while scripting objects from the source server, they are reflected in the log with the source server/database as part of the log's name. Any errors encountered on the destination server are reflected in a separate error log.
This brings you to an important point. As implied, a transfer involves scripting out all the appropriate object drops/creations, stored procedures for logins/users, and so on, and grant/revoke statements for permissions. Each type of script goes in a specific type of file. In general, the files are of the format Servername.DatabaseName.scripttype. A list of the file extensions and a description of the contents of the file follows.
Script Extension | Script Contains |
BND | Bindings for rules and defaults |
DEF | Defaults (create default statements) |
DP1 | Drops Foreign Keys that refer to tables you transfer |
DP2 | Drops all transferred objects |
DR1 | DRI objects that should be applied before loading data |
DR2 | DRI objects that should be applied after loading data |
FKY | Foreign Key Declarative Referential Integrity |
GRP | Groups- and statement-level group permissions |
ID1 | Indexes that should be created before loading data |
ID2 | Indexes that should be created after loading data |
LGN | Logins, including the NULL passwords |
PRC | Stored procedures and their permissions |
PRV | Assigns table permissions |
RUL | Rules (create Rule statements) |
TAB | Creates tables |
TRG | Creates triggers on your transferred tables |
UDT | Creates user-defined datatypes |
USR | Creates users and their statement permissions |
VIW | Create views and sets their permissions |
Each of these scripts is run in a particular order (if they exist). If you don't request a particular type of object to be transferred, the script file for that type of object may not exist. They are run in the following sequence:
DP2, LGN, GRP, USR, DEF, RUL, UDT, TAB, DR1, ID1.
The data is then copied using the BCP program, and then these scripts are run: DR2, ID2, BND, PRV, FKY, TRG, VIW, PRC.
You also see one .BCP file for each table you transfer. During the transfer, each table is stored in these .BCP files but are zeroed out at the end of the transfer.
The bulk copy program can be used to load data either into SQL Server from a file or from SQL Server to a file. You can use this utility when you need to move data from SQL Server 6.5 to a previous version of SQL Server, or to another database such as Oracle. You can also import data from another program, another database, or a legacy system. Still another useful option is loading auditing data from SQL Trace into a table for analysis.
You can append data to an existing table, just as you can with any other program you can write that inserts data into a table. However, BCP usually runs faster than a program you can write to perform this kind of data load.
You can also export the data to a file. A new file is created if the file didn't previously exist. Otherwise, the file's contents are replaced with the newly downloaded data.
There are some common naming conventions for files used with the BCP program. These conventions are generally followed:
To copy data from SQL Server to a file, you need to select permissions on the table or view from which you get data, as well as select permissions on the system catalog. Everyone can view the system tables by default if they have a database username, so this shouldn't be much of a concern. You also need operating system permissions to create or modify the file to which you want to write. This typically means the Change or Full Control permissions if the file is on an NTFS partition. You need Add, Add and Read, Change, or Full Control permissions on the directory, as well as file permissions if you are creating a new file. FAT partitions are not secured, so no special permissions are needed at the file or directory level.
To import data into SQL Server from a file, you need read permissions on the file (if on an NTFS partition). You also need insert permissions on the table you want to load in SQL Server.
Take a look at the BCP program's syntax.
bcp [[databasename.]owner.]tablename {in | out} datafile [/m maxerrors] [/f formatfile] [/e errfile] [/F firstrow] [/L lastrow] [/b batchsize] [/n] [/c] [/E] [/t field_term] [/r row_term] [/i inputfile] [/o outputfile] /U login_id [/P password] [/S servername] [/v] [/a packet_size]
Begin with the required parameters (those not in square brackets).
These are the required parameters. The optional parameters include:
As you saw, there are two ways to transfer data--native mode and character mode. Character mode specifies that data will be transferred in human-readable format. For instance, numbers from an integer column would be human-readable. The number 12500 would be readable just like that. In native mode, the data is kept in its internal format. 12500 would be stored in four bytes: 0x000030D4 (in hex).
Some people prefer the character mode format, because it's much more flexible. You can use native mode only when transferring data from SQL Server to SQL Server, and preferably if going from servers on the same platform using the same character set. Character mode allows the data to be transferred from or to just about any program. In addition, if an error occurs in the file, character mode files are much easier to fix.
You are not prompted for additional information when running BCP if you specify /c. It is assumed that your data will be formatted as plain ASCII text. If you specify /n, you cannot be prompted for additional information. Otherwise, you are required to provide information about how you want your file to be formatted.
BCP was run to export a table with a single char(5) column in Figure 9.6.
Figure 9.6. A basic BCP export.
If you add the /c parameter, you notice that it no longer prompts you to format the output rows and build a format file (see Figure 9.7).
Figure 9.7. A simple BCP export.
This leads you to the next item to examine--format files.
Format files are used to specify the layout of data in your files or to document the layout for import. When you export data with BCP, you can build a format file if you choose not to use the /n or /c parameters. For import, you could build a format file yourself.
For example, you can examine each option you are presented with when you specify that you want to create a format file during a BCP export. You receive an additional request for a field length for character data, which is represented as follows. Otherwise, the requested information is consistent between datatypes.
Enter the file storage type of field COL1 [char]: Enter prefix length of field COL1 [0]: Enter length of field COL1 [5]: Enter field terminator [none]:
The file storage type refers to how the data will be stored in the file. For character data, or to use character-only data type, use the datatype char. Otherwise, accept the default value from SQL Server. Any valid SQL Server datatype can be used here.
The prefix length determines how much space is reserved to indicate how long this particular field is. For many types, this can be 0 bytes. For char or varchar data, this can be 1 byte long. For text or image datatypes, the default length is 4 bytes.
The field length specifies how long the data fields are when being exported. You should probably accept the default values requested by SQL Server.
The last option is the field terminator. Just about any single character is a valid field terminator; however, it's best to specify a character that does not appear in your data. You can specify any value, including special characters. These include: \t for a tab.
\n for a new line.
\r for a carriage return.
\\ for a backslash. \0 for a null terminator (no visible terminator). You can use these terminators for both field terminators (the /t option) or row terminators (/r).
The example produces a format file such as this:
6.0 1 1 SQLCHAR 0 5 "" 1 COL1
The 6.0 on line 1 refers to the version that this format file is associated with; the next line references the number of columns in this format file; lastly, there is one line for each column of data represented in the file.
The first number is the relative column number in the data file. The second entry on the line is the data type. BCP format files don't contain SQL Server datatypes--they contain special datatypes that are specific to these BCP files. Valid BCP format file data types include:
BCP Datatype | Used for SQL Server Datatypes: |
SQLBINARY | BINARY, IMAGE, TIMESTAMP, VARBINARY |
SQLBIT | BIT |
SQLCHAR | CHAR, SYSNAME, TEXT, VARCHAR |
SQLDATETIME | DATETIME |
SQLDECIMAL | DECIMAL |
SQLFLT8 | FLOAT |
SQLMONEY | MONEY |
SQLNUMERIC | NUMERIC |
SQLFLT4 | REAL |
SQLDATETIM4 | SMALLDATETIME |
SQLMONEY4 | SMALLMONEY |
SQLTINYINT | TINYINT |
The next field is the length of the prefix as described in the list. The length of the actual data length is listed. The next entry is the field terminator. The order of the column in the table DDL is needed (also called the server column number), and it ends with the column name in the server.
When you load data into SQL Server with BCP, all data is copied in a single batch and a single transaction by default. That means that the entire load either completely succeeds or completely fails. Even if you are loading a million rows of data, the entire set of data is copied in a single transaction. This could easily fill your transaction logs on a database. Therefore, it's probably appropriate to issue a Commit periodically and begin a new batch. This improves concurrency and allows you to do things such as truncate the log on checkpoint. For more information on transactions see Day 16, "Programming SQL Server."
BCP can operate in two modes; those modes are known as fast and slow BCP. At its lowest level, BCP is fundamentally running inserts. That means that each row is processed as an insert operation. It is written to the transaction log, then the data is modified, and then any indexes are added. This is not the fastest way to add data to SQL Server.
If you drop all indexes on a table, and set the Select into/Bulkcopy option to true for a database, then a "fast" BCP will execute. In this mode, the inserts ARE not written to the transaction log. Because there are no indexes to be maintained and no transaction logging occurs, this operation runs very quickly. Datatypes and defaults are still enforced, even during a fast data load. Rules, constraints, and triggers are not enforced.
WARNING: You should be very cautious performing nonlogged operations on a production server. Because the inserts are not logged, the database is unrecoverable until a backup occurs. See Day 8 for more information about the Select into/Bulkcopy option and database recovery.
Do the following steps to get a fast data transfer to occur:
Now perform a couple of BCPs to see how you might use them in the real world. If you want to follow along, you need to copy the data presented here into files on your system.
You are going to work with the following table in both examples:
CREATE TABLE mytable (cust_id integer not null, cust_name char(30) null, city char(20) null , state char(2) not null DEFAULT `WA', zip char(10) null)
This table represents customer data you receive from another system. cust_id and state are the only required fields. Additional data, such as the city, cust_name, and zip fields, are optional (and hence, allow nulls). If a value is not supplied for state, it defaults to Washington (WA).
In the first example, you are going to load a table from a data file that doesn't have as many columns of data as the table. Your data file looks like this (comma delimited):
cust_id,city,state,zip_code
For instance, two sample rows might look like the following:
1,Seattle,WA,98102 2,Bellevue,WA,98004
You need to create a format file to properly load this. In this case, look at the following format file (which you should copy if you want to run this example):
6.0 5 1 SQLCHAR 0 9 "," 1 cust_id 2 SQLCHAR 0 0 "" 0 cust_name 3 SQLCHAR 0 20 "," 3 city 4 SQLCHAR 0 2 "," 4 state 5 SQLCHAR 0 10 "\r\n" 5 zip
You have to change the server column number for cust_name to 0. This indicates to BCP that the data is not being passed in this file. You also have to reset the field length of this column to 0 and avoid specifying a row terminator.
When loading with BCP and fewer columns are in the data file than in the table, you see something similar to Figure 9.8.
Figure 9.8. Loading with BCP.
Now perform the second example. This time, your file has more data than you want to load. Your file should be in the following format:
cust_id,cust_name,cust_addr,city,state,zip_code
The address is 30 characters long. Again, examine the two rows of sample data.
1,Joe Smith,1200 First Ave,Seattle,WA,98102 2,Marilyn Jones,123-116 Ave NE,Bellevue,WA,98004
This time, a format file reflects the column that exists in the data file but doesn't exist in SQL Server.
6.0 6 1 SQLCHAR 0 9 "," 1 cust_id 2 SQLCHAR 0 30 "," 2 cust_name 3 SQLCHAR 0 0 "," 0 cust_addr 4 SQLCHAR 0 20 "," 3 city 5 SQLCHAR 0 2 "," 4 state 6 SQLCHAR 0 10 "\r\n" 5 zip
You added a field for cust_addr in this example, but indicated that it doesn't exist in the table by specifying its server column number as 0, and its data length is 0. You still need the field terminator, so that BCP knows how much to skip in your file.
As you can see, BCP is quite flexible--but working with format files can require some time to perfect.
You can use backup and restore to move your databases between servers, but it's probably not the optimal solution. When you back up the database, you also (as discussed yesterday) back up all of the system tables, including the sysusers table in the database. This sysusers table maps security using the login ID (or SUID) field, instead of the login name. Therefore, when you recover the database on a new server, unless all the same logins exist in the same order as occurred on the server that you backed up from, the mappings for security are probably incorrect. It is unlikely that the mappings will be correct.
Starting with SQL Server 6.5, Microsoft shipped a stored procedure called sp_change_users_logins. This stored procedure fixes the biggest problem in using the backup and restore technique: the invalid security mappings. After you've completed the restore, run this stored procedure. However, you need to verify that the logins are appropriately matched to users.
sp_change_users_login {Auto_Fix | Report | Update_One} [, `UserNamePattern' [, `LoginName']]
Auto_Fix | Report | Update_One indicates which action should be taken.
Auto_Fix tries to map usernames to login names. However, it assumes things that may not be true. You should be wary of using this option.
Report lists all of the broken named links. If you don't specify a parameter, a report is generated.
Update_One fixes only one specific broken link as specified by the next parameters.
The UserNamePattern is a "like" comparison operator (such as % for all users, if you specify Auto_Fix). The default is NULL for Report (report on all users). For Update_One, it reports on the username you specify here.
The LoginName parameter is the name of a login from the syslogins table in master. If using Update_One, you specify the mapping you'd like corrected. Otherwise, it should be NULL.
An example: After a recovery, if you have a login of Ted on the new server with an SUID of 14 but query sysusers and find that the table has a user of Ted assigned to SUID 19, you could remedy this situation by running the following code:
Sp_change_users_login Update_One,'Ted','Ted'
This changes the SUID field in the sysusers table in your recovered database to match SUID 14, the (presumably) correct SUID for Ted on this server.
None of this changes the fact that you must perform a recovery with compatible devices and database size assignments.
Moving data into and out of SQL Server 6.5 can be done with either SQL Enterprise Manager's Transfer Management Interface (TMI), or with the Bulk Copy Program. The TMI is most appropriate when either transferring data between multiple copies of SQL Server or on the same SQL Server between two databases. However, BCP allows you the most flexibility in terms of loading and unloading operating system files into SQL Server tables. Using backup and restore may work, but you need to fix any security that is breached when recovering a database to a new server.
A If they're network connected, then the Transfer Management Interface is probably your best choice.
Q If I want to load a file from my mainframe into SQL Server, can I do that with BCP?
A Yes, as long as your mainframe application provides a way to store the information in one of the file formats BCP recognizes.
Q Can I restore a database from one server to another to copy data from that server?
A Yes, but security may be problematic.
The Workshop provides quiz questions to help you solidify your understanding of the material covered, as well as exercises to provide you with experience using what you've learned. Try to understand the quiz and exercise answers before continuing onto the next day's lesson. Answers are provided in Appendix B, "Answers."
Truncate table stores Go
© Copyright, Macmillan Computer Publishing. All rights reserved.