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
Sql-server – Best way to copy a SQL Server database from a remote server to local computer
sql server
Related Question
- SQL Server 2012 – How to Generate Scripts from Command Line
- SQL Server – Troubleshooting Transactional Replication Issues
- Sql-server – Copy whole database from SQL Server to SQL Server using linked server
- How to Replicate Schema Changes from Local Database to Remote – SQL Server
- Sql-server – How to write and read files from client machine SQL Server 2019
Best Answer
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.