Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>To find the RESTORE DATABASE time, I have found that you can use this query:</p> <pre><code>declare @filepath nvarchar(1000) SELECT @filepath = cast(value as nvarchar(1000)) FROM [fn_trace_getinfo](NULL) WHERE [property] = 2 and traceid=1 SELECT * FROM [fn_trace_gettable](@filepath, DEFAULT) WHERE TextData LIKE 'RESTORE DATABASE%' ORDER BY StartTime DESC; </code></pre> <p>The downside is, you'll notice that, at least on my test server, the <code>EndTime</code> is always NULL.</p> <p>So, I came up with a second query to try and determine the end time. First of all, I apologize that this is pretty ugly and nested like crazy.</p> <p>The query below assumes the following:</p> <ol> <li>When a restore is run, for that DatabaseID and ClientProcessID, the next EventSequence contains the TransactionID we need.</li> <li>I then go and find the max EventSequence for the Transaction</li> <li>Finally, I select the record that contains <code>RESTORE DATABASE</code> and the maximum transaction associated with that record.</li> </ol> <p>I'm sure someone can probably take what I've done and refine it, but this appears to work on my test environment:</p> <pre><code>declare @filepath nvarchar(1000) SELECT @filepath = cast(value as nvarchar(1000)) FROM [fn_trace_getinfo](NULL) WHERE [property] = 2 and traceid=1 SELECT * FROM [fn_trace_gettable](@filepath, DEFAULT) F5 INNER JOIN ( SELECT F4.EventSequence MainSequence, MAX(F3.EventSequence) MaxEventSequence, F3.TransactionID FROM [fn_trace_gettable](@filepath, DEFAULT) F3 INNER JOIN ( SELECT F2.EventSequence, MIN(TransactionID) as TransactionID FROM [fn_trace_gettable](@filepath, DEFAULT) F1 INNER JOIN ( SELECT DatabaseID, SPID, StartTime, ClientProcessID, EventSequence FROM [fn_trace_gettable](@filepath, DEFAULT) WHERE TextData LIKE 'RESTORE DATABASE%' ) F2 ON F1.DatabaseID = F2.DatabaseID AND F1.SPID = F2.SPID AND F1.ClientProcessID = F2.ClientProcessID AND F1.StartTime &gt; F2.StartTime GROUP BY F2.EventSequence ) F4 ON F3.TransactionID = F4.TransactionID GROUP BY F3.TransactionID, F4.EventSequence ) F6 ON F5.EventSequence = F6.MainSequence OR F5.EventSequence = F6.MaxEventSequence ORDER BY F5.StartTime </code></pre> <p><strong>EDIT</strong></p> <p>I made some changes to the query, since one of the test databases I used is case-sensitive and it was losing some records. I also noticed when restoring from disk that the <code>DatabaseID</code> is null, so I'm handling that now as well:</p> <pre><code>SELECT * FROM [fn_trace_gettable](@filepath, DEFAULT) F5 INNER JOIN ( SELECT F4.EventSequence MainSequence, MAX(F3.EventSequence) MaxEventSequence, F3.TransactionID FROM [fn_trace_gettable](@filepath, DEFAULT) F3 INNER JOIN ( SELECT F2.EventSequence, MIN(TransactionID) as TransactionID FROM [fn_trace_gettable](@filepath, DEFAULT) F1 INNER JOIN ( SELECT DatabaseID, SPID, StartTime, ClientProcessID, EventSequence FROM [fn_trace_gettable](@filepath, DEFAULT) WHERE upper(convert(nvarchar(max), TextData)) LIKE 'RESTORE DATABASE%' ) F2 ON (F1.DatabaseID = F2.DatabaseID OR F2.DatabaseID IS NULL) AND F1.SPID = F2.SPID AND F1.ClientProcessID = F2.ClientProcessID AND F1.StartTime &gt; F2.StartTime GROUP BY F2.EventSequence ) F4 ON F3.TransactionID = F4.TransactionID GROUP BY F3.TransactionID, F4.EventSequence ) F6 ON F5.EventSequence = F6.MainSequence OR F5.EventSequence = F6.MaxEventSequence ORDER BY F5.StartTime </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POHow can I query how much time a SQL server database restore takes?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USLittleBobbyTables
    1. USLittleBobbyTables
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHow can I query how much time a SQL server database restore takes?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThat is awesome. I'll run this asap on my server(s)
      singulars
      1. PO
      1. USedosoft
    2. COThe first query indeed returns the start time of the restores, the second query returns nothing, maybe due to a join. I'm investigating...
      singulars
      1. PO
      1. USedosoft
    3. COThe only thing that comes to mind is if the database IDs weren't matching (because they were null) -- hopefully my edits will work for you.
      singulars
      1. PO
      1. USLittleBobbyTables
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload