SQL Server: Capture Object changes with SMO Capture Mode

  • Article

In a previous article I have showed how a SMO object can be serialized into a Transact-SQL script, which allows you to recreate the object. What if you want to have the script that is emitted when you changed one or more properties of an object? In that case Capture Mode comes in handy. This switches the ServerConnection object in a state where it starts capturing all statements that SMO emits instead of sending these to the server. The following sample illustrates how it is used.

 

 Server svr = new Server();

  

 Database db = svr.Databases["MySmoTestDatabase"];

 // First create new test database, if it does not exist
if (db == null)
{
    db = new Database(svr, "MySmoTestDatabase");
    db.Create();
}

  

 // Set an option (we flip it to on or off depending orginal state)
db.DatabaseOptions.AutoClose = !db.DatabaseOptions.AutoClose;

  

 // Switch SMO in capture mode
svr.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql;

  

 // Now perform the operation
db.Alter();

  

 // Don't forget to switch back
svr.ConnectionContext.SqlExecutionModes = SqlExecutionModes.ExecuteSql;

  

 // Put database back in original state
db.Refresh();

  

 // Print script
foreach (string s in svr.ConnectionContext.CapturedSql.Text)

 {
    Console.WriteLine(s);
}

 

Note the Refresh() near the end. This is needed to bring back the Database object in sync with the server again. It’s not enough to execute the script (if you would do that) to bring it in sync with the server.