Replay of backup operations

[ghotz]

Workloads may contain backup commands that overwrite backup files on network shares when replayed on other instances assuming these have the necessary permissions and the backup commands include INIT/FORMAT options.

This is not a bug per se, just an issue because people may not think that it could happen and accidentally overwrite production backups, so it may be enough to put a big warning in the documentation and/or issue a warnings in log files.

Backup commands may be filtered as any other command by providing a custom XE session and we may add one as an example.

[ghotz]

Couple of things to keep in mind:

1. there's no "not like" predicate, you should use NOT like_i_sql_unicode_string
2. if you filter just by the backup keyword you risk to filter too much (e.g. when part of column names, procedure names, comments etc.)
3. backup commands may still be executed inside stored procedures and not be filtered so remember to include a specific filter for such procedures (e.g. Databasebackup if using Ola's maintenance procedures)

Following is a predicate example on sql_batch_completed events:

WHERE (
    NOT [sqlserver].[like_i_sql_unicode_string]([batch_text],N'%backup database%')
    AND NOT [sqlserver].[like_i_sql_unicode_string]([batch_text],N'%backup log%')
    AND NOT [sqlserver].[like_i_sql_unicode_string]([batch_text],N'%DatabaseBackup%')
)

[spaghettidba]

That's an interesting suggestion, thanks for filing it. The problem with such kind of predicates is that they work only with extended events, and WorkloadTools is designed to work with trace and sqlite recorderd workloads (and potentially other sources). I could introduce biolerplate filters in the session / trace setup, but I don't feel this is the right approach.

The problem I see is that it is not only backups are involved: imagine having updates on linked servers or calls to anything else impacting systems outside the sqlserver database itself (xp_cmdshell, extended stored procedures, execute at...): all of those would modify something outside the scope of the database, potentially hitting production systems.

My take on this is to make sure that the system you are replaying to is isolated, so it can't overwrite legit backups, run updates on linked server, touch production systems in any way. Firewalls are great for this.

[ghotz]

I agree, it's not worth over engineering filtering when a solution is already available via custom sessions. Documenting such use-case and providing a generic warning (including, as you pointed out, also other things like xp_cmdshell etc.) is more than enough.