July 5, 2023 at 11:20 pm
is there a stored proc somewhere for sql server 2016 that can return latest restore database duration in seconds and in minutes for each database on the server?
I cannot find a ready code, and the GPT cannot help.
it is hard to believe though that there is no working code somewhere. I looked and did not find. like an SP that will return the report: DBName, RestoreStartTime, RestoreEndTIme, duration_Sec, duration_min.
Likes to play Chess
July 6, 2023 at 2:51 am
I ended up writing this
-- Declare variables
DECLARE @DatabaseName VARCHAR(100)
SET @DatabaseName = 'WideWorldImporters' -- Replace with your database name
-- Create a temporary table to store the error log data
CREATE TABLE #ErrorLog (
LogDate DATETIME,
ProcessInfo VARCHAR(100),
Text VARCHAR(MAX)
)
-- Insert error log data into the temporary table
INSERT INTO #ErrorLog (LogDate, ProcessInfo, Text)
EXEC xp_readerrorlog 0, 1, N'Restore is complete'
-- Extract the duration in seconds of the last restore for the specific database
DECLARE @LastRestoreTimeEnd_ERRLOG DATETIME
DECLARE @DurationInSeconds INT
DECLARE @REstoreStartDate DATETIME
SELECT @REstoreStartDate = max(restore_date) from msdb.dbo.restorehistory
WHERE destination_database_name='WideWorldImporters'
select @REstoreStartDate as '@REstoreStartDate'
SELECT TOP 1 @LastRestoreTimeEnd_ERRLOG = LogDate
FROM #ErrorLog
WHERE Text LIKE '%' + @DatabaseName + '%'
ORDER BY LogDate DESC
select @LastRestoreTimeEnd_ERRLOG as '@LastRestoreTimeEnd_ERRLOG'
SET @DurationInSeconds = DATEDIFF(SECOND, @LastRestoreTimeEnd_ERRLOG, @REstoreStartDate)
-- Display the duration in seconds and minutes
SELECT
@DatabaseName AS DatabaseName,
@DurationInSeconds AS RestoreDuration_inseconds,
(@DurationInSeconds / 60) AS RestoreDuration_inminutes
DROP TABLE #ErrorLog
Likes to play Chess
July 6, 2023 at 2:52 am
I ended up writing this
-- Declare variables
DECLARE @DatabaseName VARCHAR(100)
SET @DatabaseName = 'WideWorldImporters' -- Replace with your database name
-- Create a temporary table to store the error log data
CREATE TABLE #ErrorLog (
LogDate DATETIME,
ProcessInfo VARCHAR(100),
Text VARCHAR(MAX)
)
-- Insert error log data into the temporary table
INSERT INTO #ErrorLog (LogDate, ProcessInfo, Text)
EXEC xp_readerrorlog 0, 1, N'Restore is complete'
-- Extract the duration in seconds of the last restore for the specific database
DECLARE @LastRestoreTimeEnd_ERRLOG DATETIME
DECLARE @DurationInSeconds INT
DECLARE @REstoreStartDate DATETIME
SELECT @REstoreStartDate = max(restore_date) from msdb.dbo.restorehistory
WHERE destination_database_name='WideWorldImporters'
select @REstoreStartDate as '@REstoreStartDate'
SELECT TOP 1 @LastRestoreTimeEnd_ERRLOG = LogDate
FROM #ErrorLog
WHERE Text LIKE '%' + @DatabaseName + '%'
ORDER BY LogDate DESC
select @LastRestoreTimeEnd_ERRLOG as '@LastRestoreTimeEnd_ERRLOG'
SET @DurationInSeconds = DATEDIFF(SECOND, @LastRestoreTimeEnd_ERRLOG, @REstoreStartDate)
-- Display the duration in seconds and minutes
SELECT
@DatabaseName AS DatabaseName,
@DurationInSeconds AS RestoreDuration_inseconds,
(@DurationInSeconds / 60) AS RestoreDuration_inminutes
DROP TABLE #ErrorLog
Likes to play Chess
July 6, 2023 at 2:52 am
I ended up writing this
-- Declare variables
DECLARE @DatabaseName VARCHAR(100)
SET @DatabaseName = 'WideWorldImporters' -- Replace with your database name
-- Create a temporary table to store the error log data
CREATE TABLE #ErrorLog (
LogDate DATETIME,
ProcessInfo VARCHAR(100),
Text VARCHAR(MAX)
)
-- Insert error log data into the temporary table
INSERT INTO #ErrorLog (LogDate, ProcessInfo, Text)
EXEC xp_readerrorlog 0, 1, N'Restore is complete'
-- Extract the duration in seconds of the last restore for the specific database
DECLARE @LastRestoreTimeEnd_ERRLOG DATETIME
DECLARE @DurationInSeconds INT
DECLARE @REstoreStartDate DATETIME
SELECT @REstoreStartDate = max(restore_date) from msdb.dbo.restorehistory
WHERE destination_database_name='WideWorldImporters'
select @REstoreStartDate as '@REstoreStartDate'
SELECT TOP 1 @LastRestoreTimeEnd_ERRLOG = LogDate
FROM #ErrorLog
WHERE Text LIKE '%' + @DatabaseName + '%'
ORDER BY LogDate DESC
select @LastRestoreTimeEnd_ERRLOG as '@LastRestoreTimeEnd_ERRLOG'
SET @DurationInSeconds = DATEDIFF(SECOND, @LastRestoreTimeEnd_ERRLOG, @REstoreStartDate)
-- Display the duration in seconds and minutes
SELECT
@DatabaseName AS DatabaseName,
@DurationInSeconds AS RestoreDuration_inseconds,
(@DurationInSeconds / 60) AS RestoreDuration_inminutes
DROP TABLE #ErrorLog
Likes to play Chess
July 6, 2023 at 2:52 am
I ended up writing this-- Declare variables
DECLARE @DatabaseName VARCHAR(100)
SET @DatabaseName = 'WideWorldImporters' -- Replace with your database name
-- Create a temporary table to store the error log data
CREATE TABLE #ErrorLog (
LogDate DATETIME,
ProcessInfo VARCHAR(100),
Text VARCHAR(MAX)
)
-- Insert error log data into the temporary table
INSERT INTO #ErrorLog (LogDate, ProcessInfo, Text)
EXEC xp_readerrorlog 0, 1, N'Restore is complete'
-- Extract the duration in seconds of the last restore for the specific database
DECLARE @LastRestoreTimeEnd_ERRLOG DATETIME
DECLARE @DurationInSeconds INT
DECLARE @REstoreStartDate DATETIME
SELECT @REstoreStartDate = max(restore_date) from msdb.dbo.restorehistory
WHERE destination_database_name='WideWorldImporters'
select @REstoreStartDate as '@REstoreStartDate'
SELECT TOP 1 @LastRestoreTimeEnd_ERRLOG = LogDate
FROM #ErrorLog
WHERE Text LIKE '%' + @DatabaseName + '%'
ORDER BY LogDate DESC
select @LastRestoreTimeEnd_ERRLOG as '@LastRestoreTimeEnd_ERRLOG'
SET @DurationInSeconds = DATEDIFF(SECOND, @LastRestoreTimeEnd_ERRLOG, @REstoreStartDate)
-- Display the duration in seconds and minutes
SELECT
@DatabaseName AS DatabaseName,
@DurationInSeconds AS RestoreDuration_inseconds,
(@DurationInSeconds / 60) AS RestoreDuration_inminutes
DROP TABLE #ErrorLog
Likes to play Chess
July 6, 2023 at 6:13 am
Did you notice you end up with a negative DurationInSeconds ?
-- result_unit start END
SET @DurationInSeconds = DATEDIFF(SECOND, @REstoreStartDate, @LastRestoreTimeEnd_ERRLOG);
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 6, 2023 at 4:59 pm
Backup and Restore history is kept in the MSDB database and there are plenty of code examples of pulling that information. Instead of trying to pull that information from the error log - you should be using msdb.dbo.restorehistory.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 7, 2023 at 5:15 am
Yes, I found more examples once I spent more time searching.
this is a good one, too:
https://sqlnotesfromtheunderground.wordpress.com/2014/10/02/restore-duration-time-for-database/
Likes to play Chess
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply