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!