Exporting your SQL Server Schema and Data to a Single Script File
I host my website, and manage several other websites using shared hosting, these hosting packages all contain some sort of SQL Server database allowance. Using shared hosting, problems can and do arise when you’re ready to deploy from development/test into the live/production environment, e.g. you have your database fully constructed and in working order on your development/test environment with all your static tables populated, with a bit of sample data thrown in on other tables.
SQL Server has a few built-in tools to enable you to copy data to and from servers and databases, “Copy Database…”, “Export/Import Data…” and of course you can always perform a “Backup and Restore” of the database and copy the backup file to another server. These all work great on your local network (most of the time!) where you hold a lot of security rights compared to when you are using a shared hosting environment where you hold a lot less security rights, therefore you cannot perform these built-in tasks (well not always). Problem!
Well, actually, not so much of a problem anymore since there is a tool, Microsoft SQL Server Database Publishing Wizard, which will enable you to script the entire database schema AND data to a single script (.sql) file. If you want, you can script individual parts of the database, and you can also set a target SQL Server, so if you are developing using SQL Server 2008 Express Edition but your host is SQL Server 2000 or 2005 you need not worry about compatibility.
SQL Database Publishing Wizard is built-in to Visual Studio 2008 and can be accessed under the “Server Explorer” window as seen to the left.
Otherwise, if you don’t have Visual Studio 2008, you can download and install the SQL Server Database Publishing Wizard 1.1.
When you’re ready, either going through Visual Studio or sqlpubwiz.exe, first things first, you’ll have to set up your connection string and if you are connecting to a remote server then you may come into difficulties if the database is large and your connection is slow, so bare that in mind and perhaps tweak your connection string, “Connect Timeout”, “Packet Size” and “Max Pool Size”.
I really don’t need to explain anything else since these screenshots speak for themselves:
It really is as simple as that… Once you click finish, the wizard will whiz away generating the T-SQL you need to deploy your database schema and data to any SQL Server, providing you set “Script for target database” appropriately.
Some notes: When executing scripts onto the remote shared host SQL Server, this can take a long time, depending on the size of the database script, and while the script is executing your database will be unavailable.
Also, you can use sqlpubwiz.exe via the command line (CMD), so obviously with that in mind, you could run batch files to generate a script backup.
Hope that helps, it’s certainly made my life a lot easier!