sql database connection to server

MSSQL connection to remote database


Problem description:

I have finished testing the website locally with MSSQL database

Just opened a space

How to upload local database to the server? upload to the server

The more detailed and foolproof the better thank you


Lol, I just learned it, teach you a trick.

Step 1: Generate the table structure by SQL script,

It can ensure the integrity of the views, stored procedures, and the default values, identifiers, primary keys and other attributes of the table in the local database, avoiding the problem of lost attributes due to the DTS transfer, and at the same time, use a query analyzer to execute the SQL script to create the table structure on the server side

1) Please run your local Enterprise Manager

2) Please click on your local database and click on the right hand side -> Select Generate SQL Script

3) Select General -> Click on Show All and select the script object you want to export. the script you want to export

4), click Options – “Table Script Options,” the script you used to select and click OK

5) for their own imported .sql script file name, and save it locally

6), to find the . local .sql script file, use notepad to open

7), select Edit->Replace, change all the [dbo] characters in the program to the database login name that you have been notified of by WANET, and save and close notepad after the replacement is completed

8), through the WANET notification of the database login address, database login name, and database password, use Enterprise Manager to connect to Wannet’s host server, and then select Query Analyzer

9), click Open to select the .sql script that you just edited, and then click Run

Step 2: Create an SQL database with the same privileges as Wannet’s host locally

Step 2: Create a local SQL database

1), after completing the above operation, please select the database and click right button to choose new database, because you applied for virtual host in WANET, the permissions assigned by WANET are user’s instead of dbo’s permissions, therefore, you need to create one locally with the same configuration as the server, so as to complete the import operation normally

< /p>

2), in the General – “Name” in the name of the Wannet opening notice to inform you of the database library name, and then click OK. For example: cw01001_db

3), select Security – “New Login”

4), in the General selection, enter the name, authentication selection SQLServer authentication, the default settings to select the database that you have just created

. 5), in the database access to select the database you have just created, and then click OK

6), now we go back to the database we have just created, and then click right – “Properties”

7), select the permissions, and then the appropriate permissions to the newly created user name, and now we have a new database has been created. The next step is how to import the files from the old database to our new database

Step 3: Backup the original database and restore it to the newly created database

1) Select the old database and right click on it – “All Tasks” – “Backup Database

1) Select the old database and right click on it – “All Tasks” – “Backup Database”

2) Select the old database and right click on it. p>

2), in the General Options – “Purpose” click Add to select the location of the file you need to back up

3), and so the backup is complete, we re-selected the new database click on the right button – “All Tasks” – “Restore Database

4), select the slave device in General and click Select Device in Parameter Devices

5), click Add to select the files that we just backed up from the old database, and then click OK

6), now we are in the restore database and then click on Options to put on the existing database Forced restore selected, click OK to restore the operation, and so restore the completion of our new database has been the same as the contents of the old database, the last step of the operation is to import data from the new database using the DTS export protocol to the WAN server

The fourth step: through the Enterprise Manager’s DTS transfer method of exporting the local data to the server.

1), find your new database

2), click Right – “All Tasks – “Export Data

3), enter the DTS import/export interface, and click Next


4) Select your local database from the Select Data Source window

5) In the Select Destination window, you are required to fill in the server address, enter the database user name and password that you were told in the database opening notice of Wannet, and then select the database that Wannet opens to you after the connection is successful, and then click Next

6). Specify the copy or query, you can choose the type you need to import, generally the default

7), in the selection of the source table and view, click Select All, click Next, so that your local database files imported to the server, and now we have all the work of importing databases has been completed.

There are pictures, if you want, then contact me. whm8377@163