Creating a CHANGE script in sql server management studio?

Question

Hi there,

i was wondering if there is a way to automatically append to a script file all the changes i am making to my columns, tables, relationships etc...

The thing is i am doing a lot of different changes on a TEST db and the idea will be to apply this change script when i move the test db to production .. hence keeping production data but applying all CHANGES.

Is there an easy way to do this?? Does it also with with database diagram changes too?

I have seen how you can create a change script each time i do a change but this means i have to copy and paste into a master file.. Actually pretty easy!

I was just wondering if i was missing something??

Answer 1

Do not make changes to the test server using the UI. Write scripts and keep them under source control. You can test your scripts starting from backups of the live data and you can tune yoru scripts untill they achieve the desired result. Then you can check in the scripts for reference and later apply them on the live server. See this article Version Control and Your Database.

BTW, check out the SSMS toolpack, I think it may do what you want (I'm not sure). My advice stand none the less: version your schema, use explicitly created/saved scripts, use source control.

Answer 2

There's no way to directly generate a "delta" script in SSMS.

However, if every time you publish changes, you script out the entire database, including data, to SQL using the SQL Server Database Publishing Wizard you should be able to extract diffs between the versions and get your deltas that way.

If money is no object, you can purchase Visual Studio Team System Database Architect edition and use its fantastic database comparison tools to generate and version control exactly the diffs you want.

Answer 3

Try using TableDiff , that came with SQL Server 2005.

SQL Server 2005 TableDiff Utility

tablediff Utility

Answer 4

We have the process where when a developer gets done with a change, they then script it out and check it into Subversion. In Subversion we have a folder for Tables, Stored Procs, Data, etc. They script it out so it is repeatable (i.e. don’t insert the new data if it is already there.) This is important to do anyway so you keep the history of changes for a given object in the database.

In the past, we would just enter each of the files that we wanted scripted out into a text file (i.e. FileListV102.txt). When we were ready to make a release we would do “get latest” on all of the files (from VSS back then.) We then had a simple utility that would read the “file list” file and open each of those files in turn concatenating them into an output file. That is pretty easy to code.

We outgrew that and now we have a release management tools (which can be found here and will be on sale mid September), that takes all of the files and creates a big SQL script file out of it. It does it in the order that you would expect based on the folder names – so files found in the "Tables" folder are done before those in the "Data" folder, etc.

Either way, once you are done you have a big SQL script file that you can then apply to a fresh copy of production and that is what you test against.

Answer 5

I know I'm way late to the party, but I just wanted to add that there are tens of third party products out there. Some are very good, some are very cheap or free, and some are a mixture. I listed 22 here: http://is.gd/4uXJ1