SQL Server export Import data method

One, export import to a database in SQL Server
1. In SQL Server Enterprise Manager, select the database you want to transfer, press the right mouse button, select All Tasks-> back up the database.
2. Backup to select Database-Complete,
Purpose Backup to press add button
FileName Enter a custom backup database file name on the SQL Server server hard disk (suffix is generally bak)
Overriding an existing media with a select override
Finally press the OK button.
If the generated backup database file is larger than 1M, compress the compression tool and then transfer to the Internet.
3. Through FTP or Remote Desktop or pcanywhere and other methods
Upload the second step of the backup database file or its compressed file to the destination SQL Server database, if there is compression to extract.
4. Purpose SQL Server database if you do not have this database, create a new database first;
Then select the newly created database, press the right mouse button, select All Tasks-> Restore the database
Restore-> from Device-> Select device-> disk-> Add (find backup database file name to import)-> determine
Restore backup Set-> database-complete
Finally press the OK button. The full database import succeeded.
(If you restore a database on an existing SQL Server database, you may experience a failure to restore it if someone else is using it.
can go to see-> management-> Current Active-> lock/object-> Locate the process number of the database under lock-> into Query Analyzer kill the lock with the process number and then do the restore.
Note: Restoring a database from a backup file (*.bak) on the original destination SQL Server database replaces all the existing tables, stored procedures, and other database objects with the contents of the most recent imported backup database.
If you must restore some of the data in the backup file (*.bak), you need to build a new database with the logical name and number consistent with the logical name and number of the database in the backup file (*.bak);
The physical file name of the new database must be different from the physical file of the database in the backup file (*.bak).

Two, export import a table in SQL Server
1. No firewall, the same LAN or not in the same LAN, but through the Internet can access each other
Select the destination database in SQL Server Enterprise Manager, right-click, select All Tasks-> import data-> Eject Data Transformation Service Import/Export wizard window-> next-> Select data source-> data source (for SQL Server's Microfost OLE DB provider-> servers (you can select all SQL Server servers that are accessible to the LAN, or enter IP addresses directly)-> Choose to use Windows Authentication or SQL Serve authentication ( Enter the username and password for the database-> database (you can select to select a database on all permissions on the SQL Server server above)-> next-> Select the destination-> purpose (microfost OLE for SQL Server DB provider-> Server (default is the export server selected in the previous step, or all SQL Server servers that can be accessed from other local area networks, or directly enter an IP address)-> destination database (optionally, select SQL All permission-scoped databases on server servers-> next-> Make table copy or query-> Select Copy tables and views from the source database (optionally specify the data to be transferred with one query)-> next-> Choose Source Tables and views-> In front of the tables and views you want to import select the same table name (which can be manually modified to another table name)-> Transformation-> Column mappings and transformations you can modify the corresponding relationship between the source table and the-> in the destination table. Modify the type and length of the destination table field, and optionally create the destination tables. Add rows to the destination table, drop and recreate the destination table, enable the option-> for flag insertion determine-> next-> save, schedule, and copy package-> time-> run immediately (if you want to implement automatic export of import data over time, select Dispatch DTS package for later execution)- > Save (can not choose)->[Save DTS Package (if you want to transfer this batch of same data later, you can save the content and steps of this export import to SQL Server, save it to enter DTS package name and detailed description)-> next->]- > Complete
Performing package-> graphical interface displays the steps and status of creating a table and inserting records-> complete
2. Through the firewall, not in the same LAN
①, exporting data to a text file:
Select the destination database in SQL Server Enterprise Manager, right-click, select All Tasks-> import data-> Eject Data Transformation Service Import/Export wizard window-> next-> Select data source-> data source (for SQL Server (microfost OLE DB Provider)-> servers (you can select all SQL Server servers that are reachable from the LAN)-> choose to use Windows Authentication or SQL Serve authentication ( Enter the user name and password for the database-> database (optionally select a database with all permissions on the SQL Server server)-> next-> Select the destination-> purpose (text file)-> file name ( Generate a custom text file on your computer's hard drive-> next-> make a table copy or query-> choose to copy tables and views from the source database (or you can choose to specify the data to be transferred with a single query)-> next-> Select the destination file format-> source ( Select the table to export-> with the default delimited-> the first row contains the column name option-> next-> Save, schedule, and copy package-> time-> run immediately (if you want to implement automatic export to a text file at intervals, Select Schedule DTS package for later execution-> Save (optionally)-> [Save DTS Package (to enter DTS package name and detailed description when saving)-> next->]-> finish
Performing package-> graphical interface display table to text file steps and status-> complete
If the generated text file is larger than 1M, compress the compression tool and then transfer to the Internet.
②, via FTP or Remote Desktop or pcanywhere
The text file generated by step ① or its compressed file is uploaded to the destination SQL Server database, if there is compression to extract.
③, importing text files into the destination SQL Server database
When you import a text file directly into a new table name with the same name as a text file in the destination SQL Server database, the default is to turn all field types into strings.

Total 2 page: previous 1 [2] Next page