How to migrate a local sql express database?

Discussion in 'Database Support' started by sanjaysk, Dec 11, 2008.

  1. sanjaysk

    sanjaysk Perch

    How do I duplicate a local ms sql server express database in your regular ms sql server database? I can create a .sql file locally containing both data and schema. But in the database control panel, I see only a small execute sql script control. Can it execute large scripts?

    What other ways are there to put up these databases from local machine to production after testing?

  2. sanjaysk

    sanjaysk Perch

    I just discovered that there is a database publishing wizard from Microsoft that can do the job. But it requires to connect to a web service at Jodohost. Does Jodohost have a web service to logon to the sql 2005 database server? If so, it would become much easier to use this tool.
  3. sanjaysk

    sanjaysk Perch

    Another discovery--I'm enjoying this now:) I can connect to the database server using SQL Server Management Studio Express. But there is something confusing:

    As soon as I login, I see the databases belonging to "all" the users. It took quite a while for me to find my own database in this big list. Of course, I can't see the details within other users' databases which is a good thing. I just tested this for security purposes.

    But why doesn't it just list my own databases? Is it something you missed to configure in the server?

    BTW, I had earlier logged in through your only database tool in the control panel. It was quite slow. It also showed databases for other users but they were not so many. I'm confused now.
  4. sanjaysk

    sanjaysk Perch

    While waiting for a reply here, I tried something on a web site at another windows hosting company. I created a .BAK file of my local database. Then, I used a nice tool called MyLittleAdmin for SQL Server 2005 at this other company to restore this .BAK file from my local disk to a database on the server. It worked like a charm. So I now have a good idea on how it is done with a .BAK file.

    Question: is MyLittleAdmin available on JodoHost? If not, you should get it. It is excellent.
  5. Penhall

    Penhall Perch

    have you tried going to: http://mssql##.<yourservicedomain>.com/mssql/ and doing a restore of the .bak from there?
  6. sanjaysk

    sanjaysk Perch

    Hi George,

    Thank you for pointing this out. Yes, I can see two links there, Download backup and Restore backup. But there is no login prompt. I tried clicking it but it didn't do anything for a while so I stopped. I don't want to try it more without full knowledge. But that seems to be the answer I am looking for.

    But why is that link not there in the hsphere control panel? All I can see in there is some MS SQL manager application which is very slow.

  7. Penhall

    Penhall Perch

    Its not speedy but it should work. You will eventually be asked for the db name/login/password

    As far as I know, the app was developed by Jodohost internally to allow customers to do their own backup and restore - but honestly, has not been publicized very well.

    That being said, I have had no problems using the tool whenever I had to.

  8. Stephen

    Stephen US Operations Staff Member

    It isn't speedy because it has to be compiled almost every visit since it doesn't get a lot of traffic :)

    It is also a rather large codebase.
  9. Penhall

    Penhall Perch

    slow and steady wins the race ;-)

    ... and its not unbearably slow anyway.
  10. sanjaysk

    sanjaysk Perch

    The backup worked very well, thanks. Though, I haven't tried a restore yet.

    One question: What is the policy of jodohost on SQL server databases. Can they be used from any web site, not even hosted on jodohost? Is that what a remote connection means?

  11. Stephen

    Stephen US Operations Staff Member

    SQL servers can be used by our web servers, and for remote management.

    If found to be used by external servers those servers will be BLOCKED.

    SQL can easily use 100mb/s bandwidth back and forth and it has been done by others before, such is not even able to be isolated per user to be billed and it therefore not allowed.

    If someone wants to do it on a dedicated server it is fine as all traffic to the server(or VPS even) is theirs.
  12. sanjaysk

    sanjaysk Perch

    >>and for remote management.

    That's good. It would helps in development and debugging too.
  13. Stephen

    Stephen US Operations Staff Member

    emote management is already allowed via EM SQL Window and EMS SQL Manager Lite 2008 :)

    BTW you can run a direct .SQL file in the query window just preface your query in EM/SSMS with use DB_NAME
  14. sanjaysk

    sanjaysk Perch

    I'd prefer to use Microsoft tools like SQL Server Management Studio express (SSMS). In fact, it seems to work quite well. However, I just created a table in my database and can query it but can't see it in SSMS. I posted this question in a wrong form "Microsoft Development." I hope jodohost people attend to that forum too.

  15. sanjaysk

    sanjaysk Perch

    I just tried the EMS free tool to login to mssql9. It just locks up getting the name of the databases on the second screen. I tried that twice. Too bad.

    The Microsoft tool SSMS worked much faster and better.
  16. Penhall

    Penhall Perch

    No problems myself with the EMS SQL Manager but honestly, just enter the DB name manually to avoid the long query (grabs all the DB's on the server for the dropdown list)
  17. sanjaysk

    sanjaysk Perch

    Thanks. That worked very well.

    BTW, I discovered that MS SSME tool's generated SQL is incompatible with that on the actual MS SQL server at jodohost. It was giving syntax error. But then I generated a script with this EMS tool and the table created painlessly.

Share This Page

JodoHost - 26,000 hosting end-users in 100 countries
Plesk Web Hosting
VPS Hosting
H-Sphere Web Hosting
Other Services