How to check who restored database in sql server: audit RESTORE events, default trace, extended events, and msdb logs

Use SQL Server Audit or Extended Events to capture RESTORE events to identify who performed the restore. In this guide, you’ll learn multiple ways to track and verify restore activity—from historical sources like the default trace and error log to proactive monitoring with Extended Events and SQL Server Audit. We’ll also walk through practical, copy-paste-ready queries and clear steps you can apply today, plus best practices to future-proof your database restore auditing.

Useful URLs and Resources:

Microsoft Docs – SQL Server Audit
Microsoft Docs – Extended Events
Microsoft Docs – Default Trace
Microsoft Docs – xp_readerrorlog
Microsoft Docs – msdb.dbo.restorehistory
Microsoft Docs – sqlserver.restore_database event Extended Events
Stack Overflow and DBA Stack Exchange threads on auditing restores
SQL Server Demo Labs and Community Scripts for restore auditing

Introduction overview
In short, you can determine who restored a database by checking historical logs and enabling ongoing auditing for restore events. Here’s what you’ll typically use:

Historical sources: the default trace, the SQL Server error log, and msdb restore history
Proactive monitoring: SQL Server Audit and Extended Events to capture future restore activity
Practical steps: a mix of queries and setup scripts you can run right away

Now let’s dive into the details and give you a complete, battle-tested approach to answer the question “Who restored this database?” at any point in time.

Body

Table of Contents

Why auditing restore actions matters

Restoring a database is a high-impact operation. Knowing who performed the action helps with incident response, compliance, and change management. In many environments, a quick glance at a restore request can reveal whether the action was authorized, whether it used the expected backup, and whether it followed disaster recovery procedures. The good news: SQL Server provides multiple data sources and tooling to make this traceable, both retroactively and going forward.

Key considerations:

If restore activity happened months or years ago, the default trace and restore history may be your primary sources.
If you need real-time or near-real-time visibility, Extended Events or SQL Server Audit are your best bets.
Some environments rely on a mix: checking the error log for context, then using restorehistory for backup lineage, and finally enabling audits for ongoing compliance.

Sources that log RESTORE actions

There are several built-in places to look for restore activity:

Default Trace: captures a variety of events, including RESTORE statements issued by users
SQL Server Error Log: includes messages about RESTORE operations, especially when they complete
msdb.dbo.restorehistory and msdb.dbo.backupset: provide historical context about restores and their backup sources
Extended Events and SQL Server Audit: can be configured to capture RESTORE events in real time and retain them
xp_readerrorlog: a convenient way to peek into error logs from T-SQL

Each source has its strengths and limitations. Historical data is helpful for post-incident investigations, while audit and XE are powerful for ongoing monitoring and prevention.

Quick check: use the Default Trace to find RESTORE statements

The Default Trace is a lightweight, always-on trace that ships with SQL Server. It’s perfect for quick investigations when you don’t have an audit in place yet. RESTORE statements typically appear in the TextData field of the trace events. Configure split dns in windows server 2008 r2 step by step guide and best practices for internal vs external DNS

Step-by-step:

Identify the default trace path

Run this to grab the current default trace file path:
declare @path nvarchar260.
select @path = path
from sys.traces
where is_default = 1.

Query the trace for RESTORE statements

Use dynamic SQL to read the current trace file:
declare @sql nvarcharmax.
declare @tracePath nvarchar260 = select top 1 @path from sys.traces where is_default = 1.
set @sql = N’SELECT LoginName, StartTime, HostName, ApplicationName, TextData
FROM sys.fn_trace_gettable”’ + @tracePath + ”’, DEFAULT
WHERE TextData LIKE ”%RESTORE DATABASE%”’
.
exec @sql.

What you’ll typically see:

LoginName: who issued the RESTORE
StartTime: when the restore began
HostName and ApplicationName: where it came from
TextData: the actual RESTORE statement or batch that was executed

Tips:

If you see multiple RESTORE DATABASE lines, you can filter by database name with AND TextData LIKE ‘%YourDatabase%’.
The default trace has a rolling window historical data may be limited. For long-term investigations, rely on other sources or capture going forward with Audit or XE.

Limitations:

Default Trace is capped and rotates. it won’t cover every restore across long time spans.
If the RESTORE was performed via a job, the TextData might show the job’s T-SQL command, which is usually enough to identify the operator.

Check msdb.restorehistory and backupset for restore provenance

msdb stores historical data about backups and restores. The restorehistory table records each restore operation with a reference to the backup set used and the user who performed the restore when available. How to drop tde certificate in sql server a step by step guide: remove tde certificate safely in sql server, step by step

Useful queries:

Get recent restores for a specific database
SELECT rh.restore_date,
rh.destination_database_name,
bs.backup_set_id,
bs.backup_start_date,
bs.user_name AS backup_user,
rh.user_name AS restore_user,
rh.restore_type,
rh.restore_source_file
FROM msdb.dbo.restorehistory rh
LEFT JOIN msdb.dbo.backupset bs ON rh.backup_set_id = bs.backup_set_id
WHERE rh.destination_database_name = ‘YourDatabaseName’
ORDER BY rh.restore_date DESC.

What you get:

restore_date and destination_database_name tell you when and what was restored
backup_user comes from the backup set the person who created the backup
restore_user is the user who performed the restore when populated
restore_type can indicate a full or differential restore, etc.

Cross-check with backupset:

If you’re trying to verify the backup source, join with backupmediafamily to see the physical media used for the backup
You can extend the query to fetch additional details like the server name, backup start and finish times, and media family information

Note:

restorehistory doesn’t capture every restore scenario for example, restores from a mirrored copy or certain third-party tools. It’s best used in combination with the other sources discussed here.

Read the SQL Server Error Log for restore activity

The SQL Server error log logs significant database operations, including RESTORE events, especially when completed. You can search it programmatically with xp_readerrorlog or via SSMS by filtering the error log viewer. Learn How to Connect SQL Server With Localhost in 3 Easy Steps: A Practical Guide for Local Development, LocalDB & Docker

Simple approach with T-SQL:

Look for RESTORE statements and completion messages:
EXEC xp_readerrorlog 0, 1, ‘RESTORE DATABASE’, NULL, NULL, NULL, ‘DESC’.

What to expect:

You’ll typically see a log entry showing the database name, the time of the operation, and sometimes the login identity depending on how your SQL Server is configured
For more precise matching, include additional keywords like the database name and backup information in your search

Considerations:

The error log is finite in size and cyclical. You’ll want a longer-term approach for ongoing auditing Audit or XE if you have frequent restores.
Parsing the error log can require some manual correlation with time windows and usernames.

Proactive monitoring: Extended Events to track RESTORE_activity

Extended Events XE is the modern, low-overhead tracing system in SQL Server. It can capture RESTORE operations in real time and store them in an XE target like a file or ring buffer. This makes it ideal for ongoing governance and incident response.

What to capture: How To Connect To Linux VNC Server From Windows Dont Panic Its Easier Than Naming Your Firstborn

Event: sqlserver.restore_database_starting
Event: sqlserver.restore_database_completed
Optional actions: sqlserver.client_hostname, sqlserver.server_principal_name, sqlserver.database_name, sqlserver.database_id, sqlserver.sql_text if you want the actual T-SQL

A simple XE session example:

Create the session run as a privileged user:
CREATE EVENT SESSION
ON SERVER
ADD EVENT sqlserver.restore_database_starting
ADD EVENT sqlserver.restore_database_completed
ADD TARGET package0.asynchronous_file_target
SET filename = ‘C:\XE\RestoreTracking.xel’, max_file_size = 100, max_rollover_files = 10
WITH STARTUP_STATE = ON.
Start the session:
ALTER EVENT SESSION ON SERVER STATE = START.

Reading data:

Query the XE file target:
SELECT
CASTevent_data AS XML.value’event/@name’, ‘nvarchar50′ AS event_name,
CASTevent_data AS XML.value’event/data/value’, ‘nvarchar128′ AS database_name,
CASTevent_data AS XML.value’event/action/value’, ‘nvarchar256′ AS login_name,
CASTevent_data AS XML.value’event/@timestamp’, ‘datetime2’ AS event_time

FROM sys.fn_xe_file_target_read_file’C:\XE\RestoreTracking*.xel’, NULL, NULL, NULL. How to invite someone on discord server a step by step guide: Invite Links, Direct Invites, Roles, and Settings

Why XE helps:

It captures both start and completion events, including who initiated the restore
You can add additional actions to capture more context, like HostName or ApplicationName
It’s lightweight and adjustable, so you won’t impact performance in most environments

Common XE query pattern:

If you want everything in one go:
CASTevent_data AS XML.value’event/action/value’, ‘nvarchar128’ AS login_name,

FROM sys.fn_xe_file_target_read_file’C:\XE\RestoreTracking*.xel’, NULL, NULL, NULL
ORDER BY event_time DESC.

XE is perfect for audit trails, but you’ll typically pair it with a durable storage location to ensure you retain data as needed for compliance
Be mindful of retention policy and perform routine maintenance to keep trace data manageable

SQL Server Audit: the gold standard for future-proof restore auditing

SQL Server Audit lets you define precise policies and capture events to a secure log, with options to store data in a file or the Windows Security Log. For restores, you’d typically enable an audit at the SERVER level and include the action group that covers backup and restore activities often called DATABASE_BACKUP_RESTORE_GROUP or similar, depending on version.

Setup steps high-level: Learn how to get your dns server working in minutes: Quick DNS Setup Guide for Fast, Reliable DNS Server Configuration

Create a SERVER AUDIT that writes to a file or the Windows Event Log
CREATE SERVER AUDIT
TO FILE FILEPATH = ‘C:\Audit\Restore’
WITH ON_SUCCESS = CONTINUE, QUEUE_DELAY = 1000.
Create a SERVER AUDIT SPECIFICATION to capture restore events
CREATE SERVER_principals
ADD DATABASE_BACKUP_RESTORE_GROUP
FOR SERVER AUDIT
WITH STATE = ON.
Start the audit
ALTER SERVER AUDIT WITH STATE = ON.

Querying audit output:

If you’re writing to a file, use fn_get_audit_file to read logs
SELECT event_time, server_principal_name, database_name, action_id, succeeded, object_name
FROM sys.fn_get_audit_file ‘C:\Audit\Restore\Restore*.sqlaudit’, NULL, NULL

What you’ll capture: How to change your server name on discord step by step guide

Who performed the restore
When it happened
What database was involved
Whether the operation succeeded
The T-SQL statement used if you include the statement in the audit

Best practices:

Narrow the scope to DATABASE_BACKUP_RESTORE_GROUP to minimize noise
Keep audit logs on a durable, tamper-evident store
Periodically archive and prune old audit data to meet retention requirements
Combine with Extended Events for real-time alerting and quick triage

Limitations and tips:

SQL Server Audit requires appropriate permissions and can impact performance if misconfigured. start with a test environment
For retroactive investigations, audits won’t help. use default trace, restorehistory, and error logs as primary sources

Practical, ready-to-run queries and templates

Copy-pasteable blocks you can adapt immediately.

Default Trace RESTORE search
DECLARE @path nvarchar260.
SELECT @path = path FROM sys.traces WHERE is_default = 1.
DECLARE @sql nvarcharmax =
N’SELECT LoginName, StartTime, HostName, ApplicationName, TextData
FROM sys.fn_trace_gettable”’ + @path + ”’, DEFAULT
WHERE TextData LIKE ”%RESTORE DATABASE%”’.
EXEC @sql.
Restore provenance from msdb.restorehistory
SELECT rh.restore_date,
rh.destination_database_name,
bs.backup_set_id,
bs.backup_start_date,
bs.user_name AS backup_user,
rh.user_name AS restore_user,
rh.restore_type How to connect to a pocket edition server on computer: A complete guide to hosting and joining

FROM msdb.dbo.restorehistory rh
LEFT JOIN msdb.dbo.backupset bs ON rh.backup_set_id = bs.backup_set_id
ORDER BY rh.restore_date DESC.

Error log search for RESTORE
EXEC xp_readerrorlog 0, 1, ‘RESTORE DATABASE’, NULL, NULL, NULL, ‘DESC’.
Basic Extended Events capture readable view
— Start with a simple XE session to capture restore events
CREATE EVENT SESSION ON SERVER
ADD EVENT sqlserver.restore_database_starting
ADD EVENT sqlserver.restore_database_completed
ADD TARGET package0.asynchronous_file_target
SET filename = N’C:\XE\RestoreTracking.xel’, max_file_size = 50, max_rollover_files = 5
WITH STARTUP_STATE = ON.
ALTER EVENT SESSION ON SERVER STATE = START.
— Read back the events
SELECT
CASTevent_data AS XML.value’event/@name’, ‘nvarchar50′ AS event_name,
CASTevent_data AS XML.value’event/data/value’, ‘nvarchar128′ AS database_name,
CASTevent_data AS XML.value’event/action/value’, ‘nvarchar256′ AS login_name,
CASTevent_data AS XML.value’event/@timestamp’, ‘datetime2′ AS event_time
FROM sys.fn_xe_file_target_read_file’C:\XE\RestoreTracking*.xel’, NULL, NULL, NULL
ORDER BY event_time DESC.
Audit-based capture basic template
— Create and enable a server audit for restore events
CREATE SERVER AUDIT
TO FILE FILEPATH = ‘C:\Audit\Restore’.

CREATE SERVER AUDIT SPECIFICATION
FOR SERVER AUDIT
ADD DATABASE_BACKUP_RESTORE_GROUP.
ALTER SERVER AUDIT WITH STATE = ON. Host a free ts server today a step by step guide: Quick setup, free options, and best practices

— Read audit results
SELECT event_time, server_principal_name, database_name, action_id, succeeded
FROM sys.fn_get_audit_file ‘C:\Audit\Restore\Restore*.sqlaudit’, NULL, NULL

Best practices for auditing restores

Plan retention: Decide how long you need to keep restore activity data. Default Trace is short-term. audits and XE can be long-term but require storage planning.
Separate duties: Limit who can alter audit or XE configurations. This reduces risk of audit tampering.
Use a central log store: If you’re in a distributed environment, centralize audit logs to a SIEM or a data lake for correlation with other events.
Combine sources: Use a multi-source approach—default trace for quick historical checks, msdb.restorehistory for backup lineage, error log for operational context, XE for real-time activity, and Audit for durable, compliant records.
Automate alerts: Build automated alerts for RESTORE events outside approved change windows or for restores on critical databases.
Test your setup: Validate that your chosen approach captures restores in a non-production environment before migrating to production.

Common pitfalls and troubleshooting

Pitfall: Default Trace is truncated and overwritten. relying on it alone can miss events
Remedy: Use Extended Events or SQL Server Audit for permanent auditing, and periodically archive the default trace if you rely on it for quick checks.
Pitfall: Restore history does not always align with login details
Remedy: Cross-check with the backupset’s user_name and the restoration user from the error log or audit.
Pitfall: Audits generate a lot of data
Remedy: Use targeted action groups DATABASE_BACKUP_RESTORE_GROUP and implement rotation and archival routines to manage growth.
Pitfall: Restores via third-party tools may bypass some logging
Remedy: Ensure that your audit or XE configuration captures external utilities by including the login name and host details and by auditing through the server. How to Delete a Discord Server in 3 Simple Steps: A Quick Guide to Remove, Transfer Ownership, and Safer Alternatives
Pitfall: Parsing dates across systems and formats
Remedy: Normalize timestamps when correlating data from different sources. consider converting to UTC where possible and aligning time zones.

Practical strategies for organizations

Start with a quick health check: Run a one-time sweep across the default trace, error logs, and restorehistory to establish a baseline for your most recent restores.
Add ongoing governance: If you don’t currently audit restores, implement XE and a dedicated SQL Server Audit policy to capture RESTORE events going forward.
Compose a single-source-of-truth view: Build a small dashboard or weekly report that aggregates data from default trace, restorehistory, error logs, and XE to provide a comprehensive view of restore activity.
Align with compliance and DR plans: Tie restore auditing to your DR runbooks so you can quickly verify who performed which restores during or after an incident.

Frequently asked questions

How do I know if RESTORE was run by a user versus a service account?

RESTORE statements and their TextData can reveal the initiating login, and audits can explicitly capture server_principal_name. Checking the LoginName in the default trace, the restore user in restorehistory, and the login_name/action in XE will usually identify the actor. If a service account runs automation, look for a corresponding Windows or domain account in the logs and correlate with job history or automation tooling.

Can I see who performed a restore that used a copy-only backup?

Yes, restorehistory will show the backup_set_id and the backup source. If you need to confirm the backup’s origin, cross-reference with backupset records backup_start_date, user_name and the T-SQL used in the RESTORE statement captured by the default trace or XE.

How long should I retain restore audit data?

A practical approach is:

Short-term: Default Trace for quick triage 30–90 days depending on load
Mid-term: Error Logs and Restore History for 6–12 months
Long-term: Extended Events and SQL Server Audit data for 1–3 years or per your regulatory requirements

What is the simplest way to start auditing RESTORE events today?

Enable Extended Events for RESTORE events first, as they’re lightweight and highly configurable. Then enable a SQL Server Audit policy focused on DATABASE_BACKUP_RESTORE_GROUP to capture ongoing activity with minimal overhead. Use restorehistory and error logs for immediate retroactive checks while the audits ramp up. How to update multiple rows in sql server a step by step guide

How can I verify a restore in a production environment without affecting performance?

Start with the default trace for a quick check, then add an XE session to capture ongoing activity with minimal overhead. For long-term compliance, add a SQL Server Audit with a narrow scope and rotation. Always test in a staging environment before enabling in production and monitor the system’s performance impact.

What if I only have a database name and a rough time window?

Filter the default trace for RESTORE statements by the database name and narrow the time window. Cross-check with restorehistory for the same window, then look into the error log and any XE captures. The combination will usually reveal the actor and the command.

How do I handle restores performed by automation tools?

Automation often uses a service account. Look for the login_name in logs, the host and application, and correlate with scheduled jobs or orchestration tools. If needed, expand auditing to the automation user account and include a step to attach a reason or ticket reference in the T-SQL text data.

Can I search across multiple servers for restore activity?

Yes. Centralize your auditing by configuring a shared Audit server or a SIEM integration that collects and normalizes logs from all SQL Server instances. Use consistent time zones and export formats to simplify cross-server correlation.

What’s the best practice for DR teams to track restores?

Document your DR restoration steps and ensure that you have a predefined audit policy that captures who performed each restoration, when it happened, and from which backup set. Regularly validate the logs during DR drills and pull reports to confirm that the chain of custody is intact. Why Do I Keep Getting Server Connection Lost In Tarkov: Fixes, Troubleshooting, and Latency Tips

How can I automate reporting for restore events?

Create a small ETL process or a scheduled query that aggregates data from:

Default Trace for historical quick checks
msdb.restorehistory for restore provenance
xp_readerrorlog for error-context
XE or SQL Server Audit for ongoing, auditable records
Then publish a daily or weekly report to stakeholders, including dashboards and alert-based notifications for unusual restore activity.

Sources:

The best free vpn for china in 2025 my honest take what actually works

Is hotspot vpn free and how to choose a reliable VPN for hotspot protection in 2025

Nordvpn basic vs plus 2026: Plans, Features, Pricing, and Which Is Right for You

如何下载和使用一亩三分地 ⭐ apk：新手指南 VPN 使用要点与下载安全 Why You Cant Join a Discord Server and How to Fix It

Cant sign into your nordvpn account heres exactly how to fix it