SQL Server Designers, Failed Saves, and Generated Scripts

Question

I am a big fan of the simple diagramming tool that comes with SSMS, and use it frequently. When I save changes to the model, I have it configured to automatically generate the change scripts that go along with the save. I then save (and source control) the resulting change script. This works great and an important piece of the process my team(s) uses.

What occasionally happens is that a save fails, and I still get the option to save my change script. I then fix the problem and save again (which results in another change script).

I'm never clear what I need to do at this point to maintain a consistent set of change scripts. There seems to be overlap between the two scripts (the failed and the successful), but they are not identical.

If I want to continue to use this feature, what should I be doing with the resulting script as soon as I get a failed save of the model?

Answer 1

I would not rely on the scripts generated by SSMS as they are not 100% accurate. SSMS is not the right tool to generate changes for the database, though it has functionality to do it.

Same functionality can be achieved using native or open source or third party tools .

Native tools :

• tablediff Utility available from SQL 2005 and up. TableDiff was intended for replication but can easily apply to any scenario where you need to compare data and schema. And it is command line, so can be easily used for automation of generating scripts. It is one of the top 10 hidden gems in sql 2005 and up.
• Using Powershell and SMO.

Open Source :

• Open DBDiff from codeplex

Third Party :

• SQL Compare from Redgate - Highly recommended as I have used it and beats all of the available tools and can be integrated and automated using Powershell as described here and here.

• others tools are best described at The cost of reinventing the wheel

IMHO, rather than just relying on SSMS to generate change scripts for databases, you should look into some sort of deployment framework that can be tweaked as per your need and can be automated. Refer to : Deploying Database Changes with PowerShell

Read up on :

• Beware the change scripts created by SQL Server Management Studio

• Connect - SSMS : Table designer fails to correctly script filtered index

Answer 2

I do not use the designer for making changes, so I have no particular experience with script problems. I did some years ago find that the designers were prone to errors, so I am not surprised that you have some problems. Nonetheless, you find the tools valuable.

From your description, you wind up with two scripts, but they are not identical. So the problem is how to treat the scripts. Or how to avoid any need to deal with the issue.

Database backups or database snapshots are a couple of tools that can help.

If you are using SQL Server Enterprise or Developer editions, you have the ability to create database snapshots. So this solution depends on your SQL Server edition and you rights on that server and how many people are sharing that database.

Scenario using a snapshot database and assuming that you have full rights to do whatever you want to with the design database.

First, create a SNAPSHOT database (examples from the SQL Server documenation):

CREATE DATABASE AdventureWorks_dbss1800 ON
( NAME = AdventureWorks_Data, FILENAME = 
'D:\Data\AdventureWorks_data_1800.ss' )
AS SNAPSHOT OF AdventureWorks;

Once you have the snapshot created, make your modifications in the designer and then save your scripts. If all is well, you get rid of the snapshot:

DROP DATABASE AdventureWorks_dbss1800;

However, if the designer glitched you would then use the snapshot to revert the database back to the point in time of the CREATE...SNAPSHOT.

USE master;
-- Reverting AdventureWorks to AdventureWorks_dbss1800
RESTORE DATABASE AdventureWorks from 
DATABASE_SNAPSHOT = 'AdventureWorks_dbss1800';

Because you reverted, you can repeat the designer steps with the database in exactly the same state as the first effort.

If you are not using a version that supports SNAPSHOT databases, you can do the same thing (perhaps more slowly) using BACKUP DATABASE and RESTORE DATABASE.

Answer 3

Personally, if the change fails and you need to fix it, then the initial script should be discarded. The only use i can see for it is as reference whilst performing the required fix.

There are several reasons why I always double check any change scripts, some times you may need to batch it, other times you may need to add checks to it. And you should alway be looking for dependencies.

The best tool i used for change scripts have been from redgate. both sql compare and sql data compare. they do include transactions and will rollback on failure.

Whenever creating change scripts we always add a rollback script that can be run in the event of a failed deployment.

To summarise, always review any tool generated script and make sure to test them before deplying to live.

Some sort of version control is/can be very useful.

Answer 4

I suggest you just save the change script but don't apply the changes using the GUI. Instead, apply the changes through SSMS using your script. That way there will only ever be 1 script.

Your script will be idempotent anyway since if something has already been changed (e.g. you changed a column name) then trying to change it again will either just cause an error or will result in the same effect.

I would also highly recommend you get yourself a copy of Red Gate SQL Compare - it's genius when it comes to creating change scripts - it can create DDL change scripts like the ones you need (e.g. between your dev and staging databases) and can even create change scripts that you can check into source control to handle modifying the data - e.g. for control tables or whatever you use.