Home / server / Questions / 49438
Accepted
Earlz
Asked: 2009-08-04 10:34:55 +0800 CST

SQL Server and SourceSafe

  • 772

I have a setup of two SQL server(these are independent, only for development and testing) running computers and I would like to be able to use source safe so that I can keep these two SQL servers in sync(they are on a LAN). I have SQL server 2008, though it works in 2000 compatibility mode; and Source Safe 2005.

I have source safe installed on both computers, and I have a source safe database on one of the computers. On the other computer(the one that doesn't control SS) I have a SQL database that I need to put into source safe. How do I do this? The toolbar buttons in SQL Server Management Studio are there, but they are greyed out, the only thing it will let me do that has to do with source safe is "launch source safe" and inside sourcesafe, there is nothing about SQL databases.

windows sql-server version-control

5 Answers

  1. In SSMS, VSS integration means being able to check a "Sql Server Scripts", "Analysis Services Scripts" or "SQL Server CE Scripts" project into VSS. These projects are little more than a collection of text files-- unlike say a C# application project, which is a collection of files + a build script. SSMS projects lack the "build" part.

    3rd Party products like SQL Redgate will get you closer to what you are describing.

    • 2

  2. I believe Visual Studio for Database Developers has this feature. There are also other 3rd party products that can sync your schemas.

    A database is not like 'normal' code you store in source control - in order to sync the databases you don't just replace lines of code, you must generate ALTER(and other) statements.

    If you are creating a database from scratch each time, you can just use create statements, but keeping two live databases in sync requires some logic to sync.

    You could also write SMO (an API to manage SQL) to script the database to a file and then add that to source control.

    Here is an example of such a tool.

    We use redgate sql compare for such tasks.

    Also see: https://stackoverflow.com/questions/115369/do-you-source-control-your-databases

    • 1

  3. If you just have the regular VS, you can use the Database Project. Write scripts to update the database, then just run them against each database server. This is what I have used to move changes (sync databases) between dev, test, and prod because we do not have database edition either.

    The database project can then be checked into source control.

    • 0

  4. There is no full Integration of SQL Server with source control. Microsoft tried doing it years ago, and no one used it because it wasn't all that reliable.

    Most everyone simply keeps each object scripted out into a separate file and keep these files in source control. Then edit the file and deploy the change to the database at the same time. Then tag all the files you changed for the release.

    • 0
  5. Best Answer

    It sounds like you not only want to version control the schema but the data inside that schema as well? Why not script your database for the schema and then use SSIS to extract your data into CSV files. You can compress these files and then store them in your SCM of choice.

    I would not recommend Visual Source Safe as it can corrupt your version control database very easily. Have a look at Subversion via TortoiseSVN for Win32

    Sorry if I misinterpret your requirements. But I dont think you will find a tool the version controls your entire db - schema & db.

    • 0