This is how you can move your MSSQL database from dev environment to production. Or if you just need a backup before doing an upgrade.

  • Log in to SQL Management Studio 2014 (it's free)
  • Right click on the database you want to copy and choose Tasks -> Generate Script
  • Choose "Objects" and select "Tables"
  • "Next" under "save to file" choose advanced
    • Script: DROP and CREATE
    • Script for server version 2008
    • Types of data to script: Schema and Data
  • Choose a place to save the SQL file to
  • Next, Finish!

Then just run this script on your production server. If you are overriding an old Umbraco installation i usually get some errors droping all the tables. What i'll do is just manually delete all the tables. Some tables may have dependencies on others so deleting them can require some going back and forth before everything is gone.

Just for the sake of reference. This is how i did it in SQL Management Studio 2008:

  • Log in to SQL Management Studio 2008
  • Right click on the database you want to copy and choose Tasks -> Generate Script
  • Script for server version 2008
  • Script drop: true
  • Script create: true
  • Script Data: true : (Script Data Compression Options i 2014)
  • Script indexes: true
  • Script Primary Keys: true
  • In the next step: choose Tables
  • In the next step: choose "select all" button
  • In the next step: Script to file

Source

And before that i used "Publish to Provider" in Visual Studio but that feature got discontinued :S