Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "the method of importing, exporting and backing up data from SQL Server". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

First, export and import a database in SQL Server 1. Select the database to be transferred in SQL Server Enterprise Manager, press the right mouse button, and select all tasks-> back up the database. two。 Backup selected database-full, purpose backup to press the add button file name enter a custom backup database file name under the SQL Server server hard disk (suffix is usually bak) rewrite select rewrite existing media and finally press the OK button. If the backup database file generated is larger than 1m, it should be compressed with a compression tool and then transferred to the Internet. 3. Transfer the backup database file generated in step 2 or its compressed file to the destination SQL Server database through FTP or remote desktop or pcanywhere, and decompress it if there is any compression. 4. Destination SQL Server database if you do not already have this database, create a new database first Then select the newly created database and press the right mouse button. Select all tasks-> restore Database restore-> Slave device-> Select device-> disk-> add (find the backup database file name to import)-> OK restore backup set-> Database-completely press the OK button. The full database import was successful. (if restoring a database on an existing SQL Server database may encounter failure because someone else is using it, you can see-> manage-> current activity-> Lock / object-> find the process number of the lock under the database-> kill these locks with the kill process number in the query Analyzer. (and then restore) Note: if you restore the database from the backup file (* .bak) on the original destination SQL Server database, it will replace all existing tables, stored procedures, and other database objects with the contents of the recently imported backup database. If you must restore part of the data in the backup file (* .bak), you need to create a new database whose logical name and quantity are the same as those in the backup file (* .bak). The physical file name of the new database must be different from that of the database in the backup file (* .bak). Second, export and import some table 1 in SQL Server. There is no firewall, same LAN or not in the same LAN, but you can access each other through Internet. Select the destination database in SQL Server Enterprise Manager and press the right mouse button. Select all tasks-> Import data-> pop-up data conversion Service Import / Export Wizard window-> next-> Select data Source-> data Source (Microfost OLE DB provider for SQL Server)-> Server (you can select all SQL Server servers that can be accessed in the LAN Or enter the IP address directly)-> choose to use windows authentication or SQL Serve authentication (enter the user name and password of the database)-> Database (you can select databases within all permissions on the SQL Server server above)-> next-> Select purpose-> purpose (Microfost OLE DB provider for SQL Server)-> Server (default) Think that the export server selected in the previous step You can also select all SQL Server servers that can be accessed in other local area networks. Or enter the IP address directly)-> destination database (you can select databases within all permissions on the SQL Server server above)-> next-> develop table replication or query-> Select to copy tables and views from the source database (you can also choose to specify the data to be transferred with a query)-> next-> Select the source table and view-> in the table to be imported The same table name appears as the source-> purpose selected before the view (can be manually changed to another table name)-> transform-> column mapping and transformation can modify the corresponding relationship between the fields between the source table and the destination table Modify the type and length of destination table fields, and you can choose to create the destination table, add rows in the destination table, remove and recreate the destination table, enable options such as flag insertion-> OK-> next-> Save, schedule and copy packages-> time-> run immediately (if you want to automatically export imported data at regular intervals Select schedule DTS package for later execution)-> Save (optional)-> [Save DTS package (if you want to transfer the same batch of data later, you can save the contents and steps of this export and import to SQL Server). When saving, enter the package name and detailed description of DTS)-> next->] > complete the executing package-> graphical interface shows the steps and status of creating tables and inserting records-> finish 2. After the firewall, do not ① in the same local area network, export the data in the table to the text file: select the destination database in SQL Server Enterprise Manager and press the right mouse button. Select all tasks-> Import data-> pop-up data conversion Service Import / Export Wizard window-> next-> Select data Source-> data Source (Microfost OLE DB provider for SQL Server)-> Server (you can select all SQL Server servers accessible on the LAN)-> choose whether to use windows authentication or SQL Serve authentication (enter data User name and password of the library)-> Database (you can select databases within all permissions on the SQL Server server above)-> next-> Select purpose-> purpose (text file)-> File name (generate a custom text file on your computer hard disk)-> next-> make table copy or query-> Select copy from source database Tabulation and view (you can also choose to specify the data to be transferred with a query)-> next-> Select the destination file format-> Source (select the table to be exported)-> use the default delimited character-> Select the first row with column name options-> next-> Save, Schedule and copy package-> time-> run immediately (if you want to automatically export to a text file at regular intervals Select schedule DTS package for later execution)-> Save (optional)-> [Save DTS package (enter the package name and detailed description of DTS when saving)-> next step->]-> complete the executing package-> graphical interface display table to text file steps and status-> complete if the generated text file is larger than 1m, use a compression tool to compress and then transfer to the Internet. ②, transfer the text file generated in step ① or the compressed file to the destination SQL Server database by means of FTP or remote desktop or pcanywhere, and decompress it if there is any compression. ③, import the text file into the destination SQL Server database directly import the text file into the new table name with the same name as the text file in the destination SQL Server database, all field types will be changed into strings by default. So we have to do this: create the sql statement of the table on the source SQL Server database in the SQL Server query Analyzer-> select the table name in the source database-> right-click-> script the object in the new window-> create-> copy the sql statement that creates the table name in the new window to the query parser on the target SQL Server database to execute the sql statement to create the table name to generate an empty table structure. (if such a table name already exists, modify the sql statement to create the table, and add the year and month information of the import time after the table name, for example, table_0113) invoke the import / export tool-> pop-up data conversion service import / export wizard window-> next-> Select data source-> data source (text file)-> file name (passed to the text file to be imported under the destination SQL Server database The suffix may not be * .txt, but a file that can be opened by a regular text editor Select all file types-> next-> Select file format-> use default delimited character-> Select first line to include column name options-> next-> define column separator-> comma-> next-> Select purpose-> purpose (Microfost OLE DB provider for SQL Server)-> Server (you can select what is accessible on the target LAN There are SQL Server servers)-> choose to use windows authentication or SQL Serve authentication (enter the user name and password of the database)-> Database (you can select databases within all permissions on the SQL Server server above)-> next-> Select source table and view-> change the destination table name to the table name you just created-> transform (trace in the destination table) Add Line)-> next-> Save, Schedule and copy package-> time-> run immediately (if you want to import text files automatically at regular intervals Select schedule DTS package for later execution)-> Save (optional)-> [Save DTS package (enter the package name and detailed description of DTS when saving)-> next->]-> complete the executing package-> graphical interface to display the steps and status of the text file to the table-> complete the table name of the year and month information of the import time if you want to change it, such as table_0113 to the original table name. Change the original table name to table_old_0113,table_0113 and rename it to table in Enterprise Manager. This will interrupt the frequently accessed tables in the application. Note: the index and primary key constraints created on the source table cannot be transferred using the 1 and 2 methods described above. Indexes and primary keys need to be built manually. The constraints of flag seeds and not null can be inherited. Importing a view imports all the real data in the source view into a new table instead of the view. 3. SQL Server stored procedures or user-defined functions export and import 1, export stored procedures or user-defined functions into * .sql files select the source database in SQL Server Enterprise Manager, stored procedures-> single or multiple stored procedures to be transferred-> user-defined functions-> single or multiple functions to be transferred-> right mouse button Select all tasks-> generate SQL script-> OK-> generate a custom * .sql file on your computer hard disk-> Save-> generate SQL script-> success 2. If the destination database passes through the firewall and is not in the same local area network, you need to transfer the * .sql file generated in step 1 to the destination SQL Server database server through FTP or remote desktop or pcanywhere. 3, use the query analyzer to enter the SQL Server destination database, select the file from the menu-> Open-> Open the query file-> select the * .sql file generated in step 1-> the green inverted triangle shortcut key that executes the query-> the query window will appear after the execution of the message (sometimes because there is a certain dependency between the stored procedure and the user-defined function, some errors will be reported. It is best to execute the * .sql file of the user-defined function first, and then execute the * .sql file of the stored procedure) fourth, import the table in the ORACLE database into the SQL Server database 1, install the ORACLE Client software or ORACLE ODBC Driver on the destination SQL Server database server. Configure the alias (service name) of the ORACLE database in $ORACLE_HOME\ network\ admin\ tnsnames.ora. Specific configuration methods can refer to this site article: client connection server precautions 2, in WIN2000 or win2003 server-> management tools-> data source (ODBC)-> system DSN (NT domain users can use this machine)-> add-> ORACLE OD personal experience: SQL Server2005 "generate script" this function to generate all the content. SP is also OK, the advantage is that all the SP can be generated in one file at the same time, so just execute it and put all the SP in the new database. In the same way, the same is true for tables, views, and so on. "methods for importing, exporting and backing up data in SQL Server" ends here. Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.