Uploading your databaseUploading data to a SQLServer2005 database is not as straightforward as with previous versions of SQLServer, but happily Microsoft recently released a wizard tool that makes it simple.
Deploying a SQL Database to a Remote Hosting Environment
(thanks to Scott Guthrie for this informaton, on his blog) I have taken this content substantially from Scott's blog, and added ti to out site rather than relying on Scott keeping his information on line)
You finish building a great T application, have everything tested and working right on your local system, and are ready to publish it to a remote hosting environment and share it with the world.
Copying the files and compiled assemblies to the remote system is pretty easy (just FTP or copy them up). The challenge that confronts a lot of developers, though, is how to setup and recreate the database contents - both schema and data - on the remote hosted site. Unfortunately there hasn't historically been a super-easy way to accomplish this.
The good news is that the SQL Server team published the release candidate of a new SQL Server Hosting Toolkit that will make it much, much easier to deploy your SQL solutions remotely to a hosted environment. The toolkit allows you to work with SQL Express, SQL Server 2000, and SQL Server 2005 databases locally, and then easily transfer your schema and data and install them into a shared hosting remote SQL Server account.
The below post describes how you can start using this today.
SQL Server Hosting Toolkit
The SQL Server Hosting toolkit is available for free, and ships with a Database Publishing Wizard that supports two database hosting deployment scenarios:
- The Database Publishing Wizard enables you to point at a database you are working with on your local system, and then automatically create a .SQL script file that contains the setup logic needed to re-create an exact replica of the database on any remote system. This .SQL script includes everything needed to create the database schema (tables, views, sprocs, triggers, full-text catalogs, roles, rules, etc - full details here), as well as populate the new database with the same table row contents as your local database (this is analogous to the MySQL dump utility). The benefit of having this setup logic encapsulated in a single .SQL file is that most hosters already support the ability for you to upload .SQL files to their hosted environments and run these scripts via their hosting admin control panels. Assuming you have a web hoster that supports this today, you can immediately start using the Database Publishing Wizard to easily deploy your sites without requiring anything to be installed or configured by the hoster.
- The Database Publishing Wizard also enables you to point at a database you are working with on your local system, and then use web-services to transfer and recreate the database in your remote hoster environment (without you having to create the .SQL file or use the hoster admin control panel to run it). This publishing option does require that a SQL Publishing web-service be exposed in the hosting environment, and the SQL Server Hosting Toolkit includes a free implementation of this SQL Publishing web-service that we'll be working with hosters to aggressively deploy.
The Database Publishing Wizard enables you to use either SQL Express or SQL Server 2000/2005 locally, and then use either SQL 2000 or SQL 2005 in the remote hoster environment. It does not require that the versions of SQL match - so you can use SQL Express 2005 locally and then upload to a SQL 2000 server in the hosting environment without having to change any of your code.
The Database Publishing Wizard does not require DBO permissions, and should enable you to deploy the ASPNETDB tables + sprocs just as easily as any other database using the Database Publishing Wizard.