SQL Server import, export, and backup data methods _mssql

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.
You can go to see-> admin-> Current active-> lock/Object-> Find the process number of the database under lock-> into Query Analyzer kill the lock with the process number,
And then do the restore)
Note: If you restore the database from the backup file (*.bak) on the original destination SQL Server database
Replaces all database objects that already exist, such as tables and stored procedures, with the contents of the most recently imported backup database.
If you must restore some of the data in the backup file (*.bak), you need to build a new database.
Its logical name and quantity correspond to 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
In SQL Server Enterprise Manager, select the destination database, right-click, select All Tasks-> Import data->
Eject the Data Transformation Services Import/Export wizard window-> next->
Select a data source-> data source (microfost OLE DB Provider for SQL Server)->
Server (select all SQL Server servers that can be accessed from the LAN, or enter the IP address directly)->
Choose whether to use Windows Authentication or SQL Serve authentication (enter the user name and password for the database)->
Database (Optionally, select the database on all permissions on the SQL Server server)-> next->
Select the destination-> purpose (microfost OLE DB Provider for SQL Server)->
Server (default is the export server selected in the previous step, or any SQL Server server that can be accessed from other local area networks, or directly enter an IP address)->
Destination database (Optionally, select a database with all permissions on the SQL Server server)-> next->
Make table copy or query-> choose to copy tables and views from the source database (optionally specify the data to be transferred with a query)-> next->
Select source tables and views-> the same table name (which can be modified manually to another table name) in front of the table and view you want to import select the Source-> object->
Convert-> column mappings and transformations you can modify the corresponding relationship between the source and destination tables, modify the type and length of the destination table field, and so on,
You can choose to create the destination table, add rows to the destination table, drop and recreate the destination table, enable the option-> for flag insertion-> next->
Save, schedule, and copy packages-> time-> run immediately (if you want to implement automatic export of import data over time, select the schedule DTS package for later execution)->
Save (optionally)->[the DTS package (if you want to transfer the same batch of data later, you can save the contents and steps of this export import.
Save to SQL Server, save time 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:
In SQL Server Enterprise Manager, select the destination database, press the right mouse button, select All Tasks->
Import data-> Eject Data Transformation Service Import/Export wizard window-> next->
Select a data source-> data source (microfost OLE DB Provider for SQL Server)->
Server (select all SQL Server servers that can be accessed from the LAN)->
Choose whether to use Windows Authentication or SQL Serve authentication (enter the user name and password for the database)->
Database (Optionally, select the database on all permissions on the SQL Server server)-> next->
Select destination-> (text file)-> file name (generate a custom text file on your computer's hard disk)-> next->
Make table copy or query-> choose to copy tables and views from the source database (optionally specify the data to be transferred with a query)-> next->
Select destination file format-> source (select the table to export)-> with the default delimited-> Select the first row package contains column name Options-> next->
Save, schedule, and copy packages-> time-> run immediately (if you want to implement automatic export to text files at intervals, select the schedule DTS package for later execution)->
Save (can not select)-> [Save DTS Package (when saving to enter DTS package name and detailed description)-> next->]-> complete
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.
So we're going to do this:
On the source SQL Server database, Mr. SQL statement to create a table
In SQL Server Query Analyzer-> Select the table name in the source database-> Right-click-> Write Object Script in new window-> create->
Copy the SQL statement that creates the table name in the new window
To the target SQL Server database execute the SQL statement that creates the table name in Query Analyzer, generating an empty table structure.
(If the table name already exists, modify the SQL statement for the table, plus the date of the import time after the table name, such as table_0113)
Invoke Import/Export tool-> Eject Data Transformation Service Import/Export wizard window-> next->
Select a data source-> data source (text file)->
FileName (text file to be imported under the destination SQL Server database, suffix may not be *.txt,
But the general text Editor can open the file, file type Select All)-> next->
Select File format-> with the default delimited-> the first row package contains the column name option-> next->
Make a column delimiter-> comma-> next->
Select the destination-> purpose (microfost OLE DB Provider for SQL Server)->
Server (select all SQL Server servers that can be accessed from the destination LAN)->
Choose whether to use Windows Authentication or SQL Serve authentication (enter the user name and password for the database)->
Database (Optionally, select the database on all permissions on the SQL Server server)-> next->
Select source table and view-> modify the destination table name-> the transformation (append rows in the destination table) to the table name just created-> next->
Save, schedule, and copy packages->
Time-> run immediately (if you want to implement automatic import of text files over time, select the schedule DTS package for later execution)->
Save (can not select)-> [Save DTS Package (when saving to enter DTS package name and detailed description)-> next->]-> complete
Performing package-> graphical interface display text file to table steps and status-> complete
If you want to change the table name of the month information for the import time, such as table_0113 to the original table name,
In Enterprise Manager to change the original table name to table_old_0113,table_0113 renamed into table.
This can cause a certain interruption in the frequently accessed tables in the application.
Note: The indexes and primary key constraints on the source table cannot be transferred using the 1 and 2 methods described above, and you need to manually build indexes and primary keys.
Constraints on flag seed and not NULL can be inherited.
Importing a view imports all the real data from the source view into a new table, not a view.
Three, SQL Server stored procedures or user-defined function export Import
1, export stored procedures or user-defined functions into *.sql files
In SQL Server Enterprise Manager, select the source database,
Stored procedures-> or multiple select stored procedures to be transferred->
User-defined functions-> or multiple Select functions to be transferred->
Right-click, select All Tasks-> Generate SQL script-> determine that-> generates a custom *.sql file on its own computer's hard disk->
Save-> is generating SQL script-> success
2, if the destination database through the firewall, not in the same LAN,
The *.sql file generated in step 1th will be uploaded to the destination SQL Server database server via FTP or Remote Desktop or pcanywhere.
3, with Query Analyzer into the SQL Server destination database,
Select File from menu-> Open-> Open query file-> Select the *.sql file-> point generated in step 1th to execute the query's green Inverted triangle shortcut key->
Post-execution messages appear in the query window (sometimes because there is a dependency between the stored procedure and the user-defined function, some errors are reported.)
It is best to execute the *.sql file of the user-defined function before executing the *.sql file of the stored procedure.
Iv. tables in Oracle database import SQL Server database
1. Install Oracle client Software or Oracle ODBC Driver on the destination SQL Server database server.
Configure an ORACLE database alias (service name) in $oracle_home\network\admin\tnsnames.ora.
Specific configuration methods can refer to this site article: Client Even server considerations
2, in WIN2000 or Win2003 Server-> management tool-> Data Source (ODBC)->
System DSN (available to NT Domain users on this machine)-> add->oracle OD

Personal experience:
The "Generate Script" feature in SQL Server2005 to generate all of the content. SP Also, the advantage is that all the SPS can be generated at the same time in a file, so as long as the implementation of all the SPS in the new database. Similarly, about tables, views, and so on.