How to replay a SQL Profiler trace on different database?

Question

I've captured a trace (template: tsql_replay) on our production server for a specific database (filtered by database name). I'd like to replay on it on our test server, but of course the database id is different on the test server.

So far, I've tried loading the trace file into a table and modifying the databaseid with an update query (see below), but Profiler gives me an error when trying to replay it (missing events, etc). If I open the trace file directly, it allows me to replay it. So somehow, either loading into a table or changing the database id messes something up.

What's the correct procedure to capture a trace for replay, and changing the database it should be replayed on?

select * into trace_table
from fn_trace_gettable('f:\trace\trace100222.trc', default)
go
update trace_table
set databaseid = 47
where databaseid = 16

Answer 1

You have to create the trace and save it as Trace XML File for Replay afterwards, the open the XML and Alter the Database ID on the Trace XML.

Beware that if you trace from a sql 2008 to sql 2000, you can't set the end date and hour, because there will be a ODBC DateTime Error.

But other than that it works, normally.