MSSQL数据库的上传方法

skar9363

我在网上找了几个方法，大致能看懂，但翻译不好，所以请老大翻译下以作备用或教程

This method fory copying a SQL database from my computer to Lunarpages works great for me.

1. Visual Web Developer 2005 Express (it is free)

This program is a streamlined version of Microsoft Visual Studio and it is free for everybody. It is perfect for creating websites.

Download and install Visual Web Developer 2005 Express. To install, double-click the vwdsetup.exe file that you downloaded and follow the setup wizard. When running the installation, be sure also install SQL Server Express simultaneously.

Download here: http://msdn.microsoft.com/vstudio/express/downloads/

2. SQL Server Hosting Toolkit (it is free)

This is a handy tool to copy your database from your computer to Lunarpages. It creates a script file that will convert the SQL Express database to SQL 2000 format for use on Lunarpages.

Download and install the SQL Server Hosting Toolkit from CodePlex (and is backed by Microsoft). To install, double-click the DatabasePublishingWizard.msi file that you downloaded and follow the setup wizard.

Download here:  http://www.codeplex.com/sqlhost/Wiki/View.aspx?title=Database%20Publishing%20Wizard

To export your local database to Lunarpages, locate the database in Visual Web Developer, Database Explorer. To use the SQL Server Hosting Toolkit within Visual Web Developer, right-click on the anyDB.mdf database and choose ‘Publish to Provider,’ then follow the wizard. Now you have a new .sql file.

Additional instructions here: http://weblogs.asp.net/scottgu/archive/2006/12/22/recipe-deploying-a-sql-database-to-a-remote-hosting-environment-part-1.aspx

3. My Little Admin Tool (inside Plesk)

In this example, we will import the .sql file into an empty database on Lunarpages. This way, once you publish your Personal Website, it will use the new database you created on Lunarpages.

Within Plesk, click on your domain name, then the Databases icon. Click the ‘Add New Database’ icon and follow the wizard. Click the ‘Add New DatabaseUser’ icon and follow the wizard. Now you have an empty database named myDatabase with username and login as myUsername and myPassword.

Now you are ready to import the .sql file to your new myDatabase, which will recreate your Personal Website database structure and contents exactly how it was from Visual Web Developer on your local computer.

Within Plesk, click on your domain name, then the My Little Admin Tool icon. Login with your new database username and password, myUsername and myPassword. Also, be sure to click ‘Options’ and select ‘TCP/IP’ network protocol connection in order to log in.

Within My Little Admin Tool, click Tools and then New Query. Open the .sql file in Notepad and copy and paste it into the New Query. Be sure to select myDatabase from the dropdown box. Click Submit and your script should run successfully. Now your myDatabase is fully populated.

Hope this helps

skar9363

Regor_User:

hw15,

Your method also seems to allow the transfer of a database to LunarPages, but I can't see the benefit over the more straightforward Backup and Restore method through MyLittleAdmin.  For instance, in my situation, the result still had the same wrong Schema issue.  Also, our database as a Backup file was over 20 MB, but as a script file generated using the method you suggested resulted in 90+ MB of text.  This caused problems in the Copy and Pasting step (the Windows copy and paste functionality even died a few times, and I had to find a little program that would revive it).

I did finally solve my problem by removing all databases and users.  I then had to request that someone at LunarPages remove the troublesome Username/Schema, since MyLittleAdmin and Plesk gave me no control over that Username/Schema, and it remained in the MS SQL server even after I had deleted all databases and usernames that I could administer.  Once LunarPages support removed the MS SQL database user in question, I manually created an empty database in Plesk and added what used to be the Username that was a roadblock.  Once I did this, re-Restoring the database using the .BAK file resulted in all tables being imported properly and showing ownership by "dbo" instead of ownership that used to be listed as the troublesome username/schema.  Now everything seems to work just fine.

Still, I appreciate your detailed instructions, and I'll file that method away in my toolkit to try in the future as I'm sure it may solve any number of other problems.


Thanks again,

skar9363

Thanks for letting us know the issues that you ran into with the alternate method. It sounds like your .bak method is more straight forward. My database was very small, so I did not have trouble with copy/pasting a large .sql file. Maybe I should do it your way, especially if it gets bigger. Did you create the .bak file from within Visual Web Developer or SQL Server Express on your local computer? My database is in SQL Server Express on my local computer, but it must be converted to SQL 2000 for Lunarpages. Does the .bak file solve this?

I'm glad Lunarpages was able to solve your original issue. How can we avoid running into the problem you faced with invalid username/schemas?

skar9363

Regor_User:

@hw15,

To be honest, I don't know how our .BAK file was created, as it was generated by the technical support at another hosting company.  I'm fairly sure it came out of an MS SQL 2000 database, and I'd guess they used Enterprise Manager or an equivalent.  Uploading this MS SQL 2000 database directly to LunarPages' MyLittleAdmin through the Tools > Restore Wizard (Web Hosting) functionality is straightforward.

The only issue that we ran into is the username/schema problem.  That seemed to arise because the username which created and administered records in the .BAK database (let's call this username "joeadmin" as an example) was not first created through Plesk at LunarPages.  Therefore, when I restored the .BAK database on my first attempt, MyLittleAdmin automatically created "joeadmin" in the LunarPages MS SQL server but gave me no control or access over it.  As a result, I could not access any of the tables that were restored.  We could find no quick way to correct this username/schema misassignment through MyLittleAdmin (we would've needed a way to change all the tables from "joeadmin" schema/ownership to "dbo" schema/ownership).  We did find a way to generate a script using Enterprise Manager that when run in MyLittleAdmin would fix the problem, but only one table at a time.  Complicating matters, MyLittleAdmin does not allow you to administer the "joeadmin" username/schema in any way.  Even after deleting all of the users and databases you created through Plesk, the "joeadmin" username/schema remains.  You discover this since the next time you try to create a database, then you try to manually create "joeadmin", MyLittleAdmin reports that the username already exists.  I had to open a support ticket at LunarPages and request that they delete the "joeadmin" database username for me.

To avoid this, make sure that before you install any MS SQL database using this MyLittleAdmin restore process, you create an empty database in Plesk, and you MUST add the username that is the main username in the database you are about to restore ("joeadmin" in this example).  I also used the same password as was used in the backed up database, although I'm not sure if this is necessary (probably not a bad idea, though).  Then, log in to MyLittleAdmin using "joeadmin", and perform the restore.  Once the restore is complete, remember that you'll probably have to delete "joeadmin" and recreate it in Plesk before you can access MyLittleAdmin again (some kind of quirk with the system).  Once you log back in to MyLittleAdmin, you should have full control over the restored data for that user, and any subsequent user you add using Plesk.  You should find all table schema/ownership assignments are listed to "dbo" instead of "joedmin".

Hope this helps.  Thanks for your detailed instructions earlier

大漠孤狼

好多，等一下我了解一下他们的MSSQL。对他们的windows主机还不是很熟悉

harold