Graphics and text methods to import/export MSSQL server to a remote server tutorial sharing _mssql

1, open the Local Enterprise Manager, first create a SQL Server registration to remote connection server port SQL Server.
The steps are shown below:

Figure 1:


2, after the pop-up window input content. "Always prompt for login name and password" is optional, as shown in Figure 2.

Figure 2:


3, after registering a good server, click Open. If you select "Always prompt for login name and password" then click OK and then prompt for user password, as shown in Figure 3.

Figure 3:


4, after entering, select to your database, such as TestDB. Right-click the "All Tasks" >> "import Data", as shown in Figure 4.

Figure 4:

5, into the DTS Import/Export Wizard, click the "Next" button to continue

Figure 5:

6, select the data source, enter the data source of the database server name, user name, password and the source database to copy data, click the "Next" button

Figure 6:

7. Select "Copy objects and data between SQL Server Databases" and click "Next" to continue

Figure 7:

8, this step can be "including extended properties" and "collation" two choices. Then remove the "Use default option" on the bottom left, and click "Options" in the lower right corner to configure it.

Figure 8:

9, Figure 8 Midpoint "Options" will pop up figure 9, "Copy database user and database role" and "Copy object level permissions" two options to remove,
Click OK to go back to figure 8 and then "next" into Figure 10.

Figure 9:

10, set the scheduling mode, the general election "run immediately" can, and then click "Next" to continue

Figure 10:

11, point "Finish" to start execution.

Figure 11:

12, in the data import

Figure 12:

13, if all is normal, prompts the successful replication as Figure 13, that is done.

Figure 13:

SQL Server Import/Export Error troubleshooting

If you are prompted for an import failure, the situation is shown in Figure 14, so don't be anxious to "finish" close the window. "Error" between double hits can cause detailed failure.

Figure 14:

Error reason one, did not install SP3 patch

If this is the cause of the error shown in Figure 15, it is likely that your native SQL Server has not yet patched SP3.

Figure 15:

How do I see if a SP3 patch has been made? Right-click the local SQL Server properties, pop-up Figure 16 window, view the "product version" line.
As shown in the figure below, the 8.00.760 (SP3) description has been patched. If your Enterprise Manager displays a version that is smaller than this one, it's not a SP3 patch.
Please install the SQL Server SP3 patch and try again.

Figure 16:

Error reason two, object attribute conflict

If you have a picture of Figure 17, you should be the same as the database default user for your local database's tables/views/stored procedures.
The user of the server is generally: database name + ' _f ', if my database name is TestDB, then the database username I use on the server is Testdb_f.
As shown in Figure 18, my local table owner is testuser, inconsistent with the server database username, so the import process is wrong.

Figure 17:

Figure 18:

The solution is:
All local tables/views/stored procedure owners should be changed to dbo or Testdb_f first (the latter need to create the appropriate user locally.)
It is recommended that you create it, or you will export the data from the server later, and then import/export it again because of the error.

Open SQL Query Analyzer, run the following command to bulk modify the table owner is dbo:

exec sp_msforeachtable ' sp_changeobjectowner ', "dbo" '

After running successfully, the table owner will change to Figure 19:

Figure 19:

If you need to modify the view/stored procedure, it's a bit of a hassle.
Here's a way to achieve this:

First, create a sp_msforeachobject stored procedure in master, which commands the following:

Use MASTER
Go
CREATE proc Sp_msforeachobject
@objectType Int=1,
@command1 nvarchar (2000),
@replacechar nchar (1) = N '? ',
@command2 nvarchar = null,
@command3 nvarchar = null,
@whereand nvarchar = null,
@precommand nvarchar = null,
@postcommand nvarchar = null
As
/* This proc returns one or more rows for each table (optionally, matching @where), and each table defaulting to its
Own result set * *
/* @precommand and @postcommand May is used to force a single result set via 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 ten then ' Isextendedproc '
When one then ' Isreplproc '
When 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

Second, run the following command to bulk modify the owners of tables, triggers, views, stored procedures (need to create sp_msforeachobject stored procedures first in 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

You should be able to go through the import again at this point.