April 25, 2011 at 6:28 pm
When a database is copied is that captured in a table some where? I know you can query to see when the last time a database was restored. I didn't know if you could query the history of when a database was copied and by whom. Urgent :crazy:
April 25, 2011 at 7:11 pm
Well I played around with the local instance I have running on my desktop and by just running the copy database wizard, choosing detach/attach method I copied a database. Then queried the defaul trace file and it shows is the DBCC DETACHDB command but it is run by the service account. However immediately after that event is a 46 event class, which is object:created and that shows my username.
I don't know that it is necessarily gold but you could get a good guess by the activity following the detachdb who was doing what.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
April 25, 2011 at 10:20 pm
Thank you. The problem is we think it happened the first week of the month. Would it still be there? What is the query?
April 26, 2011 at 7:15 am
If you keep the trace files from your default trace then yes it should be. The downside is if you want to query it, you will have to load in each file individually to read them. I have not figured out a way to loop through with PowerShell or something to get them all into one table at once.
I did notice that when I used the wizard and did the copy database that a portion of it does show up in the SQL Server ERRORLOG. So you could use that as an indication of the timeline. I'm not at my home computer anymore so can't tell you what showed up exactly.
The command to query the file from T-SQL:
SELECT *
FROM fn_trace_gettable('<exact filename for trace file>', DEFAULT)
ORDER BY StartTime
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
April 26, 2011 at 7:52 am
Good! How do I get the exact trace file name?
April 26, 2011 at 10:00 am
Well the filename usually follows "Log_XX.trc". With "XX" being a sequential number as the trace file roles over to a new file.
If you are wanting to look through older trace files you can put the exact path the file is stored at with the filename, if SQL Server has permission to it.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
April 26, 2011 at 12:51 pm
Where are trace files stored so I can get the trace file path?
April 26, 2011 at 1:06 pm
You should find them in the LOG directory: X:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
Drive letter and full path may vary depending on how you installed your instance.
You can use this query to get the path of the current file being written to:
SELECT [path] FROM sys.traces
WHERE is_default = 1
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
April 26, 2011 at 2:53 pm
I ran:
SELECT * FROM sys.traces
WHERE is_default = 1
I couldn't browse to the path that was returned. I tried typing in the path in Windows Explorer but it's not there.
April 26, 2011 at 3:10 pm
SwaidSwaid (4/26/2011)
I ran:SELECT * FROM sys.traces
WHERE is_default = 1
I couldn't browse to the path that was returned. I tried typing in the path in Windows Explorer but it's not there.
The path returned is going to be local to the server. So if you are running SSMS from your desktop you will need to logon to the server to access that path/file.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
April 26, 2011 at 4:10 pm
Thank you. I really appreciate your help!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply