April 8, 2002 at 1:58 pm
How can I find out the last restore date for a database ?
(both in 7.0 and 2000)
April 8, 2002 at 2:06 pm
If you are using SQL backup In Ent Mgr - right click the database - all tasks - restore database
you will get a list of the backups and dates
April 8, 2002 at 2:37 pm
I want the last "Restore" time for a database.
April 8, 2002 at 3:12 pm
that is the last time a specific database has been restored from a full backup file.
April 8, 2002 at 3:28 pm
Don't think this is available. You can check the logs, but that isn't an easy task.
Steve Jones
April 8, 2002 at 3:28 pm
Your request is confusing. Can you be more specific about what you mean. First it sounded like you wanted ther date of the last time the database was resotred. But your other statement sounds like you want to know how long the restore took. Please specify.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 8, 2002 at 3:33 pm
the date of the last time the database was restored.
April 9, 2002 at 6:55 am
it is in the Logs but I have them re-cycling daily to keep them small. Thus no records > 7 days past.
any other ideas ?
April 9, 2002 at 9:54 am
This should do the trick. Also does not show restores where a database was restored and later a different database was restored over it, only shows the last restore to the destination db.
SELECT
bus.database_name Org_DBName,
Restored_To_DBName,
Last_Date_Restored
FROM
msdb..backupset bus
INNER JOIN
(
SELECT
backup_set_id,
Restored_To_DBName,
Last_Date_Restored
FROM
msdb..restorehistory
INNER JOIN
(
SELECT
rh.destination_database_name Restored_To_DBName,
Max(rh.restore_date) Last_Date_Restored
FROM
msdb..restorehistory rh
GROUP BY
rh.destination_database_name
) AS InnerRest
ON
destination_database_name = Restored_To_DBName AND
restore_date = Last_Date_Restored
) As RestData
ON
bus.backup_set_id = RestData.backup_set_id
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
November 23, 2006 at 8:51 am
You are querying msdb database, but what if it was restored manually.
Not by SQL agent job.
Piotr
November 26, 2006 at 3:27 am
1. SQL Server ERRORLOG (if they are not recycled)
2. Application log (if they are not recycled)
If you have overwritten the database then no way else database creation date.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply