Primary Key and constraints during mssql Import and Export

Source: When the http://hi.baidu.com/amazon83/blog/item/f3d58ef481ff0969dcc47464.html imports data, use the default option, will lose the primary key, constraints, default value and other attributes, follow the steps below: --> Export wizard --> Select a data source --> Select the target --> Specify table replication or query: Do not use the default option. Select "copy objects and data between SQL Server databases" --> Select the object to be copied: select the corresponding toggle as needed on this page. For column constraints, You must select "extended attributes". If you remove "Copy all objects ", you can select the table or object you want to export --> Follow the default steps for the rest. The detailed process is as follows: 1. Open the local enterprise manager and create an SQL Server registration to remotely connect to the SQL server port. The steps are as follows: Figure 1: 2. Enter the content in the pop-up window. "You are always prompted to enter the login name and password" (optional, optional, 2. Figure 2: 3. After registering the server, click open. If you select "Always prompt to enter the login name and password", after clicking "OK", you will be prompted to enter the user password, 3. Figure 3: 4. Select your database, such as testdb. Right-click on the top, and choose "all tasks"> "import data", 4. Figure 4: 5. Go to the DTs import/export wizard and click "Next" to continue Figure 5: 6. Select a data source, enter the name, user name, password, and source database of the database where the data source is located, and click "Next ". Figure 6: 7. Select "copy objects and data between SQL Server databases" and click "Next" to continue Figure 7: 8. In this step, you can select "extended attributes" and "sorting rules. Remove "use default options" in the lower left corner and click "options" in the lower right corner to configure the settings. Figure 8: 9. In Figure 8, if you click "option", figure 9 is displayed, and the "Copy database user and database role" and "Copy object-level permission" options are removed, Click confirm to return to figure 8 and then click "Next" to go to figure 10. Figure 9: 10. Set the scheduling mode. Select "Run now" and click "Next" to continue. Figure 10: 11. Click "finish" to start execution. Figure 11: 12. Importing data... Figure 12: 13. If everything is normal and a message indicating successful replication is displayed, success will be achieved. Figure 13:   SQL Server Import/Export error troubleshooting If you are prompted that the Import fails, in the case of figure 14, do not rush to "finish" to close the window. Double-click the "error" button in the middle to display the detailed failure cause. Figure 14: Cause 1: SP3 patch Not Installed If the error message is 15, it is likely that your local SQL Server has not been patched with SP3. Figure 15: How can I check whether the SP3 patch has been installed? Right-click the local SQL Server properties, and a line of "product version" is displayed in Figure 16. As shown in 8.00.760 (SP3), the patch has been installed. If the version displayed by your Enterprise Manager is smaller than this version, no SP3 patch is installed. Install the SQL Server SP3 patch and try again. Figure 16: Error cause 2: object property conflict In Figure 17, the owner of the table, view, and stored procedure of your local database is different from that of the database on the server by default. The user on the server is generally the database name + '_ F'. If my database name is testdb, the database user name I use on the server is testdb_f. 18. My local table owner is testuser, which is inconsistent with the server database username. Therefore, an error occurred while importing the table. Figure 17: Figure 18: Solution: Change the owner of all local tables, views, and stored procedures to DBO or testdb_f (the latter must create a user locally. We recommend that you create one. Otherwise, an error occurs when you want to export data from the server) and then import or export the data again. Open the SQL query analyzer and run the following command to modify the table owner in batches as DBO: Exec sp_msforeachtable 'SP _ changeobjectowner "? "," DBO "' After running successfully, the table owner changes to 19: Figure 19: If you need to modify the view/stored procedure, it is troublesome. The following method can be implemented: 1. Create a sp_msforeachobject stored procedure on the master. The command is as follows: Use master Go Create proc sp_msforeachobject @ Objecttype Int = 1, @ Command1 nvarchar (2000 ), @ Replacechar nchar (1) = n '? ', @ Command2 nvarchar (2000) = NULL, @ Command3 nvarchar (2000) = NULL, @ Whereand nvarchar (2000) = NULL, @ Precommand nvarchar (2000) = NULL, @ Postcommand nvarchar (2000) = NULL As /* This proc returns one or more rows for each table (optionally, matching @ where), with each table defaulting to its Own result set */ /* @ Precommand and @ postcommand may be used to force a single result set via a temp table .*/ /* Preprocessor won't replace within quotes so have to use STR ().*/ Declare @ mscat nvarchar (12) Select @ mscat = ltrim (STR (convert (INT, 0x0002 ))) If (@ precommand is not null) Exec (@ precommand) /* Defined @ isobject for save object type */ Declare @ isobject varchar (256) Select @ isobject = case @ objecttype when 1 then 'isusertable' When 2 then 'isview' When 3 then 'istrigger' When 4 then 'isprocedure' When 5 then 'isdefault' When 6 then 'isforeignkey' When 7 then 'isscalarfunction' When 8 then 'isinlinefunction' When 9 then 'isprimarykey' When 10 then 'isextendedproc' When 11 then 'isreplproc' When 12 then 'isrule' End /* Create the select */ /* Use @ isobject variable isstead of isusertable string */ Exec (n' declare hcforeach cursor global for select' [''+ Replace (user_name (UID), n'']'', n''] '') + '']'' + ''. ''+'' [''+ Replace (object_name (ID), n''] '', n'']'') + '']'' from DBO. sysobjects o' + N' where objectproperty (O. ID, n''' + @ isobject + ''') = 1' + N' and O. CATEGORY & '+ @ mscat + N' = 0' + @ Whereand) Declare @ retval int Select @ retval =error If (@ retval = 0) Exec @ retval = sp_msforeach_worker @ command1, @ replacechar, @ command2, @ command3 If (@ retval = 0 and @ postcommand is not null) Exec (@ postcommand) Return @ retval Go 2. Run the following command to modify the owner of tables, triggers, views, and stored procedures in batches (sp_msforeachobject stored procedure must be created on the master) Exec sp_msforeachobject @ command1 = "sp_changeobjectowner '? ', 'Dbo' ", @ objecttype = 1 Exec sp_msforeachobject @ command1 = "sp_changeobjectowner '? ', 'Dbo' ", @ objecttype = 2 Exec sp_msforeachobject @ command1 = "sp_changeobjectowner '? ', 'Dbo' ", @ objecttype = 3 Exec sp_msforeachobject @ command1 = "sp_changeobjectowner '? ', 'Dbo' ", @ objecttype = 4 At this point, re-import should be able to go smoothly.