Posted by Erland Sommarskog on 11/05/07 11:55

D. (d@d.com) writes:
>> But please do check, double-check, and triple-check the script before
>> executing it on a live server. There have historically been some serious
>> flaws in the scripted code used, that might result in losing all your
>> data if you are unlucky. Maybe things have changed since the last time I
>> looked, but I'd never run a script generated by Enterprise Manager or
>> SQL Server Management Studio without prior minute inspection!
>>
>
> Ok,
> could you please me tell me which part do I have to check?
>
> I though it is the same script that Enterprise Manager launch when I save
> the modification in the data structure...

EM and Mgmt Studio generate the same crap.

There are many things to watch out for:

o Transsaction scope. The script has many small transactions, but there
should be one big transaction. That or just restore a backup if there
is any error.

o Constraints are restored with NOCHECK, that should be WITH CHECK.
That takes longer time, but the flip side is that the optimiser then
can trust the constraints. This can matter a lot in some cases.

o Remove all "go" in the script, and wrap most statements in EXEC.
The way script lookas as generated, if there is a batch-aborting
error, the transaction is rolled back, and the rest of the statements
will be committed. An alternative is to wrap all batches in
IF @@transcount > 0 BEGIN END.

o Rewiew that the script only includes the changes you intend. There
are situations where EM/SSMS may include a change that you have
abandoned.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]