SQL Server 2005 – scripting your database for moving to your hoster


SQL Server 2005 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.)


Generate scripts in Sql Server Management Studio Express


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. 


Choose script options in Sql Server Management Studio Express


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!

Comments (17)

  1. Martin says:

    It does a really lousy job at scripting.  In particular it does not seem to script the identity columns as identities and who know what else.

  2. robburke says:

    Hi Martin, I just double-checked to be sure, and it definitely created identity columns for me.  I am using SSMSE Service Pack 1 (9.00.2047.00), which is available for free at the link above.

  3. robburke says:

    (Do you have a SQL Server 2005 scripting tool you prefer?)

  4. There is only one drawback, if you want to generate drop statements with those scripts, you have to input them by hand (unless the express management tool has fixed the bug with the 2005 Server product). SO just be careful of that when you make changes to the database. The default scripts do not remove existing items if they already exist in the database.

  5. robburke says:

    Richard, thanks for the tip.  I just went back in to see that there’s an option in the Script Wizard called "Script Behavior" that toggles between "Generate CREATE statements only" and "Generate DROP statements only."  The comment on that option suggests there should be a third option to generate both, but the drop-down list doesn’t provide that option.  I guess the workaround is to generate the DROP and then generate the CREATE, and execute them in turn?

  6. Gabe says:

    Now how about a tool for sending the changed made to a DB after it’s already been sent to the hoster?

    I.e. I’ve made a DB and sent it to the hoster. Then I make changes to my local DB and need just those changes sent to the hoster. How do I do that?

  7. robburke says:

    Gabe, if that tool existed, it would make me very happy.  I don’t know of one — it would be a challenge because many hosters have SQL Servers behind firewalls.  Perhaps such a tool would consist, in part, of a code-based interface that you’d deploy onto the site.  Hmm… maybe someone out there knows of something like this?

  8. Cathal says:

    Rob, The asp.net team were working on a tool that allowed sql express to be upsized to sql server (see

    http://weblogs.asp.net/scottgu/archive/2005/10/15/427581.aspx ), I don’t know what ever happened to it.

  9.  

    I showed the Mosaic Art site by Siofra site for the second time yesterday.  It’s a simple…

  10. Marciano says:

    Rob, very thanks to your tip. i’ve been stuck in this hosting problem then i found it’s solution in your page. thanks =)

    note: i’m from Indonesia, and until now still no sql server 2005 hosting in here. To Microsoft: why make ur customer frustating about this compatibility?

  11. I’ve written before about how to ease the challenge of scripting your data to move it from a local SQL

  12. Over the holidays, I built a data-driven website for a family member. It gave me a chance to explore