Database Versioning Using Visual Studio

Question

In the SO podcast episode 54 Jeff talked about using Visual Studio to save all the database objects to individual files. This sounded like just what my team needed to better implement database schema changes into TFS and I told my lead about it. He thinks it's a great idea to.

Unfortunately, so far I've had no luck getting this to work for me. One of my problems is that I don't have SQL Server installed on my local box (dept policy). I'm obviously doing something wrong.

Can someone give me a rundown of the steps or provide a decent link?

Thanks!

Answer 1

1) Create a connection to the database in Server Explorer.
2) Right-click on the connection and select Publish to provider...
3) Next, Script to file, Next, Types of data to publish should be Schema (unless you need some initial data), finish.
4) Add script to the project and check into source control.

Answer 2

He was referring to a project type in VS that supports managing databases, including (if you set it up correctly), versioning your database publishes.

Check out this article on Database Projects in VS

Answer 3

If you're importing an existing database schema, its important to get the right database project. You should likely be using "Database Projects\SQL Server 2005 Wizard".

Visual Studio requires a database connection it can use to create temporary copies of the databases it is working with. It sounds like this is the issue. Do you have SQLExpress on your local box? Use that as the deployment target (server name would be .\sqlexpress if . does not work).

As an alternative, grab update GDR 2 for Visual Studio. Allegedly, it allows one to work with database projects without using a local instance of sql server to deploy temporary working copies of the database.

Answer 4

You want to use the GDR 2. (Sometimes called Data Dude)

This allows for a completely offline solution. You don't need to have SQL Server installed on your machine for this to work. (In fact the GDR is the first version that does not care if you have SQL Server installed.)

I use the GDR for my db (a team of 3 devs and 2 testers) and it works GREAT!

Here is a link for the GDR 2 release: http://blogs.msdn.com/vstsdb/archive/2009/04/21/microsoft-visual-studio-team-system-2008-database-edition-gdr-r2.aspx

And this is a link to the actual bits: http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=bb3ad767-5f69-4db9-b1c9-8f55759846ed

The basic use is to import from an existing DB and server. (You will probably want a server project and 1 or more DB Projects)

You can then make your changes off line. When you are ready to send you changes back to the DB you can delploy (make sure you set up your deploy options first as I think the default is to drop the db and re-deploy). You can also do a Data->Schema Compare in Visual Studio and comapre your project to your database then get a script of changes from the diff output.

It takes a bit of work, but it really allows great source control and is easy once you get the hang of it. (I have my db auto deploy in my night time build twice a week.)