Im currently running a website that needs integrating into multi channel sales software. In order to do this the website database needs upsizing/migrating from Access to MSSQl.
I have initially tried using ‘SSMA for Access’ to do this but from what I understand I need remote access to my SQL server which my hosting company won’t grant. They have indicated that the database needs to be edited locally on my machine and a .sql dump saved and importing through the hosting package control panel interface. However, I do not know how to migrate/upsize the database locally?
If anybody has any ideas on how or what I should use do this, your information would be most appreciated.
Thanks for the replies folks. I have literally just got back from holiday so I am no further forward.
From the comments I take it the best way forward is to check to see what type of file the control panel will let me import and then hopefully I will have a clearer idea of what I need to do!
Install SQL Server 2008 Express edition (with tools) on your local machine. You will now have a local SQL Server to migrate the Access database to.
You should then be able to use SQL Server Management Studio Express to help you transfer the database to the new remote SQL Server.
Thanks for the info. I have downloaded ‘SQL Server 2008 Express edition’ and created a SQl server on my local machine and then migrated a copy of the database to it. However, Im now trying to get a .sql dump file of the database? Do you know how this would be achieved?
You have a few options. you can view some of them here:
Move a database from one server to another server in SQL Server 2008 - Nice things inside SQL Server - Site Home - MSDN Blogs
Hmmm…HTH make a full back up of your database from your local machine then restore it in your live database server.
Not sure if that’s an option. The OP said the new host requires an “SQL dump” that is imported through the control panel.
Backups are the “sql dumps” of the MSSQL world . . .
Well, the phrase is used and interpreted by people in different ways. The SQL Dump application on Sourceforge generates sql text files. Since it has to be imported by a control panel, there is a good chance a backup file wont work. Only the OP will know that for sure based on the control panel of the server. Its my impression that they will not be able to use SSMS to import the database. If the control panel can handle database imports from backups then the OP is in a good spot.
Was this issue resolved?