How to Replay a SQL Server Trace on a Different Server


By:   |   Updated: 2016-11-14   |   Comments   |   Related: More > 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.

SQL Replay Server Selection

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


Last Updated: 2016-11-14


get scripts

next tip button



About the author
MSSQLTips author Ben Snaidero Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.

View all my tips
Related Resources





Comments For This Article





download





Recommended Reading

Scheduling a SQL Server Profiler Trace

Use SQL Server Profiler to trace database calls from third party applications

Using the SQL Server Default Trace to Audit Events

How to Grant Permissions to run SQL Server Profiler for a non System Admin User

Find Long Running SQL Server Queries Using Profiler














get free sql tips
agree to terms