More specific my db is hosted at appharbor and I want to copy that db to my local SQL Server Express 2012. Up until now I have used Management Studio to export the data and later used the schema compare feature in Visual Studio 2012, but I guess there must be an easier way
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
migrated from stackoverflow.com Jul 11 '12 at 19:33
This question came from our site for professional and enthusiast programmers.
You should talk to your support team at appharbor. Presumably there is a way to take a backup of your database, store it on the file system somewhere, and then download the .BAK file (through http or, more likely, ftp). This should be a lot less work than your current process, but it depends on your host providing the facility to do so. This also assumes that your backup file would be < 10 GB (since this is the maximum supported by Express) and that it doesn't use any features not supported in Express Edition (such as Data Compression, SQL Server Agent, etc.).
You can do this using just Management Studio, e.g. you can right-click your database, Tasks > Generate Scripts and select all objects and on the Scripting Options tab make sure to go into advanced and select the option to script both schema and data. This will generate a humongous script that you can run on your local machine after you've created an empty database. Personally I find a backup / restore much more reliable and a lot less hassle.
But asking here isn't going to be much help - unless you happen to come across someone who has done this specific thing at that specific host using the specific hosting plan you have, there is little chance any of us knows what options you have - appharbor is going to be able to help you answer that question a lot more accurately than any of us.