Replay Requirements
In addition to any other event classes you want to monitor, the following event classes must be captured in a trace to allow the trace to be replayed (note that these events are enabled by default if the TSQL_Replay trace template is used to configure a trace that is later used for replay):
- **CursorClose (**only required when replaying server-side cursors)
- CursorExecute (only required when replaying server-side cursors)
- CursorOpen (only required when replaying server-side cursors)
- CursorPrepare (only required when replaying server-side cursors)
- CursorUnprepare (only required when replaying server-side cursors)
- Audit Login
- Audit Logout
- ExistingConnection
- RPC Output Parameter
- RPC:Completed
- RPC:Starting
- Exec Prepared SQL (only required when replaying server-side prepared SQL statements)
- Prepare SQL (only required when replaying server-side prepared SQL statements)
- SQL:BatchCompleted
- SQL:BatchStarting
In addition to any other data columns you want to capture, the following data columns must be captured in a trace to allow the trace to be replayed:
- Event Class
- EventSequence
- TextData
- Application Name
- LoginName
- DatabaseName
- Database ID
- ClientProcessID
- HostName
- ServerName
- Binary Data
- SPID
- Start Time
- EndTime
- IsSystem
- NTDomainName
- NTUserName
- Error
Note
Use the trace template TSQL_Replay for traces that capture data for replay.
In Microsoft SQL Server 2005, replay checks for the presence of required events and columns. This change helps improve the accuracy of replay and takes the guesswork out of troubleshooting replay when required data is missing. Replay returns an error and stops replaying a file when required data is missing from a trace.
To replay a trace against a server (the target) on which SQL Server is running other than the server originally traced (the source), make sure the following has been done:
- All logins and users contained in the trace must be created already on the target and in the same database as the source.
- All logins and users in the target must have the same permissions they had in the source.
- All login passwords must be the same as those of the user that executes the replay.
- The database IDs on the target ideally should be the same as those on the source. However, if they are not the same, matching can be performed based on DatabaseName if it is present in the trace.
- The default database for each login contained in the trace must be set (on the target) to the respective target database of the login. For example, the trace to be replayed contains activity for the login, Fred, in the database Fred_Db on the source. Therefore, on the target, the default database for the login, Fred, must be set to the database that matches Fred_Db (even if the database name is different). To set the default database of the login, use the sp_defaultdb system stored procedure.
Replaying events associated with missing or incorrect logins results in replay errors, but the replay operation continues.
For information about what permissions are required to replay a trace, see Permissions Required to Run SQL Server Profiler.
See Also
Tasks
How to: Replay a Trace Table (SQL Server Profiler)
How to: Replay a Trace File (SQL Server Profiler)
Reference
SQL Server Event Class Reference