I had the need to completely script a database at work today. I couldn’t just attach a backup because I couldn’t run the backup on the database since I didn’t have physical access. At my last job we did this pretty often, but we had the wonderful SQL Compare and SQL Data Compare tool by Red Gate. (You could also use SQL Packager) At my current job I didn’t have access to these tools, so I set about to do it without them. I figured that I would just script out the database by using the built in SQL Server 2005 scripting tools. The process is pretty simple, you just right click on the database and go to “Tasks” and then “Generate Scripts…”
The wizard is pretty simple so I’m not going to go over it. Then I was going to use this stored proc that I have actually used many times in the past(click the image to download):
The problem I had was that when I tried to script the database using the built-in “Generate Scripts…” function I got an error stating that “An entry with the same key already exists”. It looks like a bug surrounding synonyms in the database conflicting with table names. So, I didn’t feel like trying to figure out my way around it, since the schema was valid it should be able to be scripted out. So I started looking around for an alternate method of scripting my databases.
I found just the tool, it is Microsoft Database Publishing Wizard and it is part of the SQL Server Hosting Toolkit. It is a tool for scripting databases to a file so that you can install it on a remote host. In fact, it will script the entire schema and the data! I ran it on the database that I needed to script and it worked like a charm.
When I went to install it after I got home, I noticed that it was already on my machine! And then I did a bit of digging and found out that it is included in Visual Studio 2008. All you have to do is open the Server Explorer and right click on one of the databases:
A wizard will pop up that looks like this:
The wizard is pretty self explanatory, so I’m not going to walk through it. Suffice to say that you can choose to publish the database to a file with schema, data, or both.
I hope that this helps out someone!
Loved the article? Hated it? Didn’t even read it?
We’d love to hear from you.
Nice tip about vs2008 i have used the publishing wizard stand alone before its got me out of a few scrapes!
Regards
Ismail
This tool i excellent when You are moving a database e.g. to a hosting provider, but lacks at least two important features (or maybe I have just not found them):
1. Command line support. The Microsoft SQL Server Database Publishing Wizard had support for this before Visual Studio 2008 (version 1.1, I think) by calling the sqlpubwiz.exe executable with proper switches.
2. Better Version Control Support. I would like it to generate 1. one script for each object/relation , 2 on create script that run each script is step 1, and 3. a script that insert data into the tables (with a possiblility to select which table to insert data into). I currently use ScriptDB (http://www.codeplex.com/ScriptDB) which does step 1 and 2 with Subversion and it works reasonably well.
I know that there are commercial tools that do this (e.g. the high-priced Red Gate Tools), but it would be nice that his tool that ALMOST does the job also could do this.