December 12, 2011 at 5:58 am
Is there a way to find out the last refresh date and time for a SQL server 2005 database?
December 12, 2011 at 6:06 am
What do you mean by "refresh"? There's not a command or process that specifically defines this, so you could be referring to a lot of different things.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 12, 2011 at 6:32 am
We create test database from a backup copy of production database every weekend. I want to find out whether there is a way I can find the last refresh/restore date for these test databases that are created from the backup of the production? (We call this process "Refresh of the databases.)
December 12, 2011 at 6:40 am
ashanair70 (12/12/2011)
We create test database from a backup copy of production database every weekend. I want to find out whether there is a way I can find the last refresh/restore date for these test databases that are created from the backup of the production? (We call this process "Refresh of the databases.)
'Refresh' is certainly not the Standard Term for this task.
FYI... Viewing Information About Backups
December 12, 2011 at 6:43 am
ashanair70 (12/12/2011)
We create test database from a backup copy of production database every weekend. I want to find out whether there is a way I can find the last refresh/restore date for these test databases that are created from the backup of the production?
You can query MSDB for backup/restore info. It won't distinguish between these and any other backup/restores you may have, but it's better than nothing.
(We call this process "Refresh of the databases.)
As do many people. It's just not a standard term and there was a chance you could have been referring to something else, like a non-backup based copy of the DB from prod to test
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 12, 2011 at 7:22 am
ashanair70 (12/12/2011)
We create test database from a backup copy of production database every weekend. I want to find out whether there is a way I can find the last refresh/restore date for these test databases that are created from the backup of the production? (We call this process "Refresh of the databases.)
Last place I worked used the phrase database refresh to talk about setting up test databases using a restore operation too. I wasn't throwing rocks. But I've also seen the term used to describe a CI process that rebuilt a database from scratch, and an SSIS reload of data into a database, just for starters. Since it's not a technical term, it can mean whatever people want, so I wanted clarification.
Gail has you covered on the answer. Sorry I wasn't able to get back here soon enough.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 12, 2011 at 12:37 pm
In 2005 you can right click on the db, reports and it will show you the last time it was restored... just like Gail stated.
December 21, 2011 at 12:57 am
Try this. As Gail and others stated, MSDB stores history of Backup, restore etc etc lot many things. You have to explore it as per your need.
use MSDB
go
SELECT MAX(restore_date) as LAST_RESTORE_DT
FROM restorehistory
WHERE destination_database_name = 'yourdb'
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply