Orchard Hungary is an archive

This site is now an archive. We'll keep it so the content is accessible, but we won't update it anymore. However, we frequently publish Orchard-related news on our other site Orchard Dojo, so be sure to check that out instead. And if you're interested in news about our Orchard development company, Lombiq, check out our website.

Backing up and restoring SQL Server databases on a remote server

Tags: SQL, SQL Server, backup, restore, GearHost, Orchard, database

Thursday, June 21, 2012 11:05:00 AM

So you want to use a decent SQL Server database for your ASP.NET application (e.g. an Orchard site :-)), both when testing locally and in the hosting environment. Now how to backup or restore these databases, so you'll more or less see the same in either environment? I'm glad you asked :-).

My favourite hosting company so far, Gearhost, gives you MS SQL Server 2008R2 databases (besides MySQL ones), which you can connect to with SQL Server Management Studio. With this same tool you can manage your local SQL Server (Express) instance too, so backing up remotely then restoring locally or vice versa shouldn't be an issue, right? It slightly is, because the simple procedure of backup/restore through bak files doesn't work if you don't have access to a file system that in turn can be accessed by the SQL Server. Since one doesn't have this option with shared hosting, the way to go is scripting the database.

This is quite simple, too. Backing up the remote database:

  1. Connect to your remote DB.
  2. Right click on the DB itself, select Tasks/Generate Scripts.
  3. Go through the wizard. For Orchard databases selecting only the tables for scripting is enough. Don't forget to set "Types of data to script" to "Schema and data" under Advanced in the Set Scripting Options step.
  4. Save the script file.

Restoring locally:

  1. Open the script file from File/Open/File.
  2. Hit Execute.

If the remote and local databases' names are different, don't forget to change the leading USE statement of the script accordingly.

Happy backing up and restoring!

No Comments

  • Bertrand Le Roy said

    I like my backups to be versionable, diff-able, readable and neatly factored into tables, and since most of what Orchard stores is intelligible in text formats, I built my own tool: https://bitbucket.org/bleroy/hgdbackup http://weblogs.asp.net/bleroy/archive/2011/12/04/source-controlled-database-backups.aspx

  • Bertrand Le Roy said

    The Export feature from SQL Server works well too if that's all you have access to. But since you're using Gearhost, you should know that they have a web interface to their backup tool. Creating and downloading a proper bak file takes a few seconds. Restoring is just as easy.

  • Piedone said

    Thanks for the tips and the module!
    Since I couldn't find anything I asked Gearhost where they have access to backing up but they told me to use myLittleBackup... Anyway, this scripting out is fine for me for the moment.