Using publishing to transfer MS SQL data

Recently, the site moved from the domestic site to the Lunarpage, the program transfer is relatively simple, using cuteftp upload on it. But database transfers have always been a tricky problem. This article describes the method of database transfer.

The easiest way to transfer a database is by using DTS, but the Lunarpages database does not support remote database links, so DTS is not available, so you have to use publishing to transfer the data.

The specific steps are as follows:

Step1. Run SqlPubWiz.exe

Publishing a plug-in similar to MS SQL, you can go to the

Http://www.microsoft.com/downloads/details.aspx?FamilyId=56E5B1C5-BF17-42E0-A410-371A838E570A

Download, after the operation can be found under the tools

Step2 Run, the Run wizard appears and the local database is found

Step3. Options to generate the type, the system will automatically detect the available content, the general choice of "table" "Stored Procedures" and "view" for users do not allow the system to generate

Click Next to complete.

Change database owner

Here is the core, very important, otherwise it will not succeed.

When we use the website, we usually use the SP to our account, for example, my original database is called "bf4190_"

When the website provider gave me an account of bf419, the system generated data tables are as follows

As you can see, some tables have prefix bf419, some have prefix dbo (db Oh, database owner), which is very different. Because the script is slightly different when we set up the table.

Writing a:

CREATE TABLE [dbo].  [Ads] ( [ID] [int] IDENTITY (1,1) not NULL, [Name] [nvarchar] (m) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [img] [nvarchar] (m) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, }

Writing two:

CREATE TABLE [Ads] ( [ID] [int] IDENTITY (1,1) not NULL, [Name] [nvarchar] (m) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [img] [nvarchar] (m) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, }

For the first, the resulting table is dbo.ads, and the second table is bf419.ads, because your bf419 is actually dbo, so the system can run.

However, when you transfer the database to a new service provider, if your account is called XXXX, the above set up Bf419.ads error, and with Dbo.ads is completely no problem.

Usually the new and old service providers to the user's user name is not the same, so we need to change the owner of the database.