How to import and export MSSQLServer to a remote server is required by a friend who backs up the mssql database.
How to import/export MSSQL Server to a remote Server is required by friends who have backed up the mssql database.
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:
:
2. Enter the content in the pop-up window. "You are always prompted to enter the login name and password" (optional, optional, 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.
:
4. Select your database, such as testdb. Right-click on the top, and choose "all tasks"> "import data", 4.
:
5. Go to the DTS import/export wizard and click "Next" to continue
:
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 ".
:
7. Select "copy objects and data between SQL Server databases" and click "Next" to continue
:
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 0.
Figure 9:
10. Set the scheduling mode. Select "Run now" and click "Next" to continue.
0:
11. Click "finish" to start execution.
1:
12. Importing data...
2:
13. If everything is normal and a message indicating successful replication is displayed, success will be achieved.
3:
For SQL Server Import/Export error troubleshooting, if the error message "Import failed" appears, do not rush to "finish" to close the window. Double-click the "error" button in the middle to display the detailed failure cause. 4:
Cause 1: If the SP3 Patch Is Not Installed, if the error is 15, it is likely that your local SQL Server has not been patched with SP3. 5:
How can I check whether the SP3 patch has been installed? Right-click the local SQL Server properties, and a row of "product version" appears in the 6 window.
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. 6:
Cause 2: If object attribute conflicts occur in 7 cases, the owner of the table, view, and stored procedure in your local database is inconsistent with the database user 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.
7:
8:
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 the operation is successful, the table owner changes to 19: 9:
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 (you must create the sp_MSforeachObject stored procedure 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 and then re-import it.
