Transcript Importing and Exporting Data

Importing and Exporting Data
Importing and Exporting Data
Exam 70-432
1 / 30
Importing and Exporting Data
Bulk Copy Program (BCP)
If you are exporting data using BCP, the account
that BCP is running under needs only
SELECT permissions on the table or view. If you
are importing data, the account that BCP is
running under needs SELECT, INSERT, and ALTER
TABLE permissions.
Importing and Exporting Data
BCP is a utility that you execute from the command
line and has the following syntax:
bcp {[[database_name.][owner].]{table_name |
view_name} | "query"}
{in | out | queryout | format} data_file
[-mmax_errors] [-fformat_file] [-x] [-eerr_file]
[-Ffirst_row] [-Llast_row] [-bbatch_size]
[-n] [-c] [-w] [-N] [-V (60 | 65 | 70 | 80)] [-6]
[-q] [-C { ACP | OEM | RAW | code_page } ] [tfield_term]
[-rrow_term] [-iinput_file] [-ooutput_file] [apacket_size]
[-Sserver_name[\instance_name]] [-Ulogin_id] [Ppassword]
[-T] [-v] [-R] [-k] [-E] [-h"hint [,...n]"]
3 / 30
Importing and Exporting Data
CAUTION CASE SENSITIVITY
All command-line switches for BCP are case-sensitive. For
example you use –e to specify an error fi le, yet –E tells
BCP to preserve identity values during an import.
Examples:
bcp AdventureWorks.HumanResources.Department
out c:\test\department.txt -n -SHOTEK –T
bcp AdventureWorks.HumanResources.Department in
c:\test\department.txt -c
-SHOTEK -U<login> -P<password>
bcp "SELECT Name, GroupName FROM
HumanResources.Department" queryout
c:\test\d
epartment.txt -n -SHOTEK –T
4 / 30
Importing and Exporting Data
EXAM TIP
The exam tests you on whether you know
which import/export option is most
appropriate
to a given situation.
NOTE ENFORCING CHECK CONSTRAINTS AND TRIGGERS
When you import data into a table using BCP, triggers and
check constraints are disabled
by default. If you want to enforce check constraints and fi
re triggers during the import,
you need to use the –h switch. If you do not disable
triggers and check constraints during
an import, you do not need ALTER TABLE permissions.
5 / 30
Importing and Exporting Data
The BULK INSERT command
- BULK INSERT cannot export data.
- BULK INSERT is a T-SQL command and does not
need to specify the instance name or
login credentials.
6 / 30
Importing and Exporting Data
BULK INSERT
[ database_name . [ schema_name ] . | schema_name . ] [ table_name
| view_name ]
FROM 'data_file'
[ WITH
( [ [ , ] BATCHSIZE = batch_size ] [ [ , ] CHECK_CONSTRAINTS ]
[ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
[ [ , ] DATAFILETYPE = { 'char' | 'native'| 'widechar' | 'widenative' } ]
[ [ , ] FIELDTERMINATOR = 'field_terminator' ] [ [ , ] FIRSTROW
=first_row ]
[ [ , ] FIRE_TRIGGERS ] [ [ , ] FORMATFILE = 'format_file_path' ]
[ [ , ] KEEPIDENTITY ] [ [ , ] KEEPNULLS ]
[ [ , ] KILOBYTES_PER_BATCH =kilobytes_per_batch ] [ [ , ] LASTROW =
last_row ]
[ [ , ] MAXERRORS = max_errors ] [ [ , ] ORDER ( { column [ ASC | DESC ]
} [ ,...n ] ) ]
[ [ , ] ROWS_PER_BATCH = rows_per_batch ] [ [ , ] ROWTERMINATOR =
'row_terminator' ]
[ [ , ] TABLOCK ] [ [ , ] ERRORFILE = 'file_name' ] )]
7 / 30
Database Configuration
and Maintenance
EXAM TIP
The exam tests you on whether you know
which import/export option is most
appropriate
to a given situation.
NOTE ENFORCING CHECK CONSTRAINTS AND TRIGGERS
When you import data into a table using BCP, triggers and
check constraints are disabled
by default. If you want to enforce check constraints and fi
re triggers during the import,
you need to use the –h switch. If you do not disable
triggers and check constraints during
an import, you do not need ALTER TABLE permissions.
8 / 30
Importing and Exporting Data
The SQL Server Import and Export Wizard
You access
the wizard by right-clicking a database within Object Explorer, selecting Tasks,
and then
selecting either Import Data or Export Data.
- Import and Export Wizard can use any data
source that is recognized by SSIS, such as Microsoft Offi ce Excel, Microsoft
Offi ce Access, or
Extensible Markup Language (XML) fi les.
- Import and Export Wizard supports
any data source or destination for which you have an Object Linking and
Embedding
Database (OLE DB) provider
- Import and Export Wizard does not require a SQL
Server instance to be either the source or destination
- Wizard
can move data from multiple tables or fi les in a single operation
9 / 30
Importing and Exporting Data
Chapter Summary
- BCP is a program that allows you to import
data from a fi le into a table as well as
export data from a table to a fi le.
- BULK INSERT is a T-SQL command that
allows you to import data from a fi le into
a table.
- The Import and Export Wizard uses a
subset of the SSIS feature set to move data
between a source and destination
10 / 30
Importing and Exporting Data
1. You want to import data into the Orders table.
The table has triggers and check
constraints that you want to be checked to
guarantee integrity. You choose to use
the BCP utility and specify the -h
“CHECK_CONSTRAINTS, FIRE_TRIGGERS” hint to
accomplish your task. Which of the following
permissions must be in place?
A. SELECT permission on the Orders table
B. ALTER TABLE on the Orders table
C. INSERT permission on the Orders table
D. A member of the bulkadmin role
11 / 30
Importing and Exporting Data
1. Correct Answers: A and C
A. Correct: If you are importing data, you need SELECT, INSERT, and ALTER TABLE
permissions
on the destination. However, if you do not disable triggers and check
constraints, you do
not need ALTER TABLE permission.
B. Incorrect: If you are importing data, you need SELECT, INSERT, and ALTER
TABLE permissions
on the destination. However, if you do not disable triggers and check
constraints, you do not
need ALTER TABLE permission.
C. Correct: If you are importing data, you need SELECT, INSERT, and ALTER TABLE
permissions
on the destination. However, if you do not disable triggers and check
constraints, you do
not need ALTER TABLE permission.
D. Incorrect: If you are importing data, you need SELECT, INSERT, and ALTER
TABLE permissions
on the destination. However, if you do not disable triggers and check
constraints, you do not
need ALTER TABLE permission.
12 / 30
Importing and Exporting Data
2. You are performing a migration on the Order
database at Contoso from Oracle to SQL
Server. The Order database contains several hundred
tables. The CustomerAddress
table has an XML column named AddressBook. What
is the most effi cient, least
intrusive way to move the data to the new SQL Server
database?
A. Move the Order database from Oracle to SQL Server
using replication.
B. Unload the data using Oracle utilities and load the
data into SQL Server using BCP.
C. Move the Order database using the Import and
Export Wizard.
D. Move the data from Oracle to SQL Server using the
OPENROWSET function.
13 / 30
Importing and Exporting Data
2. Correct Answer: C
A. Incorrect: Although it is possible to publish the
tables from Oracle to SQL Server, it is
more intrusive than using the Import and Export
Wizard to move the data.
B. Incorrect: You need to code a BCP command for
each table that you are importing data
into, which is less effi cient than using the Import and
Export Wizard.
C. Correct: The Import and Export Wizard allows you
to connect to Oracle, select multiple
tables, and move the data to SQL Server in a single
operation.
D. Incorrect: The OPENROWSET can move a single
table at a time, which is less effi cient
than using the Import and Export Wizard.
14 / 30
Designing Policy Based
Management
Designing Policy Based
Management
Exam 70-432
15 / 30
Designing Policy Based
Management
Policy Based Management introduces
the following new objects that are used
to design
and check for compliance:
- Facets
- Conditions
- Policies
- Policy targets
- Policy categories
16 / 30
Designing Policy Based
Management
Facets
Facets define the type of
object or option to be checked, such as database,
Surface Area, and login. SQL Server ships
with 74 facets
get a list of the facets available by querying the
dbo.syspolicy_management_facets table
17 / 30
Designing Policy Based
Management
Conditions
You defi ne the conditions that you
want to check or enforce for a policy
by defi ning
criteria for the properties of a facet.
=, <>, LIKE, NOT LIKE, IN, or NOT
condition must
be executed manually and cannot be scheduled.
18 / 30
Designing Policy Based
Management
Policy Targets
Conditions can also be used to specify the objects
to compare the condition
against, called policy targeting or target sets.
You can target a policy at the server level, such as
instances that are SQL Server 2005 or
SQL Server 2008. You can also target a policy at
the database level, such as all user databases
or all system databases
19 / 30
Designing Policy Based
Management
Policies
The
execution mode can be set as follows:
- On demand Evaluates the policy when directly
executed by a user
- On change, prevent Creates data defi nition language
(DDL) triggers to prevent a
change that violates the policy
- On change, log only Checks the policy automatically
when a change is made using
the event notifi cation infrastructure
- On schedule Creates a SQL Server Agent job to check
the policy on a defi ned schedule
20 / 30
Designing Policy Based
Management
Policy Categories
Policy categories can be used to group one or
more policies into a single compliance unit. If
not specifi ed, all policies belong to the DEFAULT
category.
Each policy category has a Mandate property that
applies to databases
21 / 30
Designing Policy Based
Management
Policy Compliance
Because you cannot set all policies to enforce
compliance, you need to check policies manually
that cannot be enforced on a regular basis.
You view policies that apply to an instance by
right-clicking the name of the instance within
Object Explorer and selecting Policies, View
You can check policies that apply to an instance by
right-clicking the name of the instance
within Object Explorer and selecting Policies,
Evaluate.
22 / 30
Designing Policy Based
Management
Central Management Server
Import and Export Policies
SQL Server
ships with 53 policies that are located in the
Microsoft SQL Server\100\Tools\Policies folder.
There are 50 policies for the database engine, 2
policies for Reporting Services, and 1 policy
for Analysis Services.
23 / 30
Designing Policy Based
Management
1. You have defi ned several policies that you want
applied to all databases within an
instance. How do you ensure that a database
owner is not allowed to avoid the policy
check with the least amount of administrative
effort?
A. Create a condition that checks all databases.
B. Add the policy to a user-defi ned policy
category and set the Mandate property.
C. Add the policy to the default policy category.
D. Check the policies manually against the
instance.
24 / 30
Designing Policy Based
Management
1. Correct Answer: C
A. Incorrect: Even if you create a condition that checks all
databases, a database owner can
choose not to subscribe to a policy unless you have mandated
compliance.
B. Incorrect: While you could create a policy category that has the
Mandate property
enabled and add the policy to the category, it requires more effort
than just adding the
policy to the default policy category.
C. Correct: The default category is confi gured with the Mandate
property enabled. In
addition, you cannot disable the Mandate property.
D. Incorrect: While you could check the policies manually, this
requires more effort that
adding the policy to the default category.
25 / 30
Dziękuję za uwagę
Tomasz Gacek
26 / 30