Suppose you’ve been working locally with a SQL Server 2005 database that contains the data for a website, and it’s time to move your database and data up to the web hoster. Maybe you’ve got a few tables, some stored procedures, and even a little data in that database which is all hanging out in an .mdf file your App_Data directory.
Up until this point, Visual Studio 2005 (even the Web Developer Express Edition) has probably given you all the functionality you need to create and work with your data.
Now, for scripting your database, SQL Server Management Studio Express becomes your new best friend. (Like all the Express Edition tools, it’s also free to download.)
In SQL Server Management Studio Express, attach to your .mdf database, right-click on the database in the Object Explorer, and under “Tasks”, choose “Generate Scripts…” (Don’t be fooled by the decoy menu item “Script Database As…”, as it doesn’t script the tables, just the creation of the database.)
The Wizard that appears will help you export your database, and even includes options for SQL Server 2000 compatibility, which is relevant for hosting scenarios where your hoster is not yet running SQL Server 2005.
The Script Wizard will generate a T-SQL script that can be executed against the hoster’s SQL Server to re-create your database’s structure.
The last step that you may require is to reproduce the data from your local tables on the remote server. If there’s a tool in SSMSE to do this automatically, I don’t know about it. But there are T-SQL scripts freely available that can help. I recently had success using this procedure to script your data (to generate INSERT statements from the existing data) to get the job done.
And now my database and its data are online!