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:
- Connect to your remote DB.
- Right click on the DB itself, select Tasks/Generate Scripts.
- 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.
- Save the script file.
- Open the script file from File/Open/File.
- 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!