Sql-server – Sql Server 2008 Replication “Generate Scripts” option not available

Security Account Delegation

And for SQL Server 2000 but still valid too

Basically

• Configure the server in AD
• Set up the SPN
• Use sp_addlinkedsrvlogin so useself = true

In theory you could do something as like wrap the script execution in a transaction. You can execute the .sql batch files from an application library like dbutilssqlcmd or SMO's ServerConnection.ExecuteNonQuery which handles the GO batch separator or sqlcmd extensions in the script.

However in practice this is nearly impossible to do. Wrapping a script in a transaction may yield unexpected results because the locking changes. Certain DDL statements are not allowed inside a transaction. The script may contain sqlcmd extension directives like :connect which would mess up the entire transaction handling.

Another approach is to use migrations, which are coded in the application and handle both the roll forward and the roll back of the change. But to be effective the steps must be idempotent (yield the same result even if invoked multiple times) and this is very hard to achieve in practice.

The safest and also easiest approach is to rely on a backup taken prior to migration, which would give the ability to rollback to the backup if anything goes wrong during the deployment of V. next. This is very easy to implement and very efficient if database snapshots are used as backups.

Of course if there is a 24/7 availability requirement things change dramatically, but rolling out upgrades with no downtime is a subject much bigger that this post and solutions roll in the $millions, unlikely something you would fancy.