How to do version control for SQL Server database

Question

I'd want to keep track of the versions of my databases.

I'll always want to have some information in there. For performance measurements, I'll frequently want a significant quantity of created test data.

How would I go about implementing version control in my database?

Answer 1

Not only can you compare objects at the object level and produce change scripts using Red Gate's SQL Compare, but you can also export your database objects into a folder hierarchy structured by object type, with one [objectname].sql creation script per object in these folders. The hierarchy of object types looks like this:

\Functions
\Security
\Security\Roles
\Security\Schemas
\Security\Users
\Stored Procedures
\Tables

You can use this to update your SVN repo and retain a running history of each item individually if you dump your scripts to the same root directory whenever you make changes.