By: Ben Snaidero | Comments | Related: > Profiler and Trace
Problem
I have a SQL Server trace file from my production SQL Server database and I would like to be able to use the Replay feature in SQL Profiler to run this on a test database that is hosted on a different server with a different database name which is accessed by a different user. Can you list the steps required in order to have this run successfully?
Solution
In this tip I'll assume you've used the Replay feature in SQL Profiler before, so we won't go through all the steps of capturing the trace and listing the events and columns that are required. If you haven't used this feature before, the following tip will walk you through the steps required to create and capture a trace that is suitable for replay.
Updating SQL Server Trace Data
In order to Replay the trace on a different server that has a different database name and a different user, we'll need to update the trace data. Before we start making the updates we'll need to make sure your trace is stored in a table since it's much easier to perform large data updates if the trace is stored there rather than in a file. If you didn't capture your initial trace this way then you'll first need to load your trace file into SQL Profiler and then save it into a table.
Since there are three things that need to be changed in order for us to run this replay in SQL Profiler (Server, Database and User), let's take a look at each one of these updates individually.
Updating User Information
SQL Profiler stores the user data in the LoginName column so we can use the following UPDATE statement (substituting in the correct LoginNames of course), so your trace file will replay using the correct user account.
UPDATE TraceDataTable SET LoginName='##NEWUSER##' WHERE LoginName='##OLDUSER##';
Updating Database Information
Next let's tackle updating the database that we will run the replay against. For this to work correctly you'll need to update both the DatabaseName and DatabaseID columns in your trace. If you don't know the DatabaseID for either of your databases you can get it by querying the sys.databases catalog view. Once you have the DatabaseID you can use the following UPDATE statement so your trace file with replay against the correct database (substituting in the correct values of course).
UPDATE TraceDataTable SET DatabaseName='##TestDBName##', DatabaseID=##TestDBID## WHERE DatabaseName='##ProdDBName' AND DatabaseID=##ProdDBID##;
Updating Server Information
The last item we need to change is the server name and this one is the easiest of the three as it's not stored in the trace. Once you open SQL Profiler and select "Start" from the Replay menu item you are presented with the usual "Connect to Server" dialog as shown in the screenshot below. You can enter the server name here (circled in red) and this will connect you to your instance and bring up the Replay Configuration dialog box which allows you to configure the settings for your replay session. You can also change the server where you will run your replay in this configuration dialog by clicking the change button (circled in green) as shown in the second screenshot below.
There you have it, once you click OK the trace file you collected from your production database will be running against your test database.
Next Steps
- Read more on using Distributed Replay
- Read other tips on SQL Profiler
- Check out the SQL Server Profiler Tutorial
About the author
Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
