Problem statement: - I
am writing this article to address similar kind of problem that we faced in our
staging environment. On our staging server there was a database named “StagingDatabase”
which was a copy of our production
database. We made some changes on that database( added few columns in a table)
on Friday but when we came back office on Monday , we found that all the changes
which were done on Friday are missing from the database. We were surprised how
our changes got disappeared from the database. We done same changes on Monday and
when we came back office on Tuesday, we found that the changes which we done on
Monday are also missing. Somehow we concluded that our database was being
restored each day, hence our changes were being overwritten. At that point we
required a script that can tell us when last restore was done on the database.
Solution: - Before
I write a query to get the details when the last database restore was done, I
would like to write a query when last database backup was taken.
Query for Last backup
taken:-
Select a.Name, case when cast( max(backup_finish_date) AS varchar(50)) is null then 'No Backup Taken' else cast(max(backup_finish_date) as varchar(50)) end as Last_Backup_Date
from sys.sysdatabases a left join msdb.dbo.backupset b on a.name=b.database_name
group by a.name
Query to determine
when Last Restore was done:-
Select
Destination_database_name, restore_date,database_name as
Source_database,
Physical_device_name as Backup_file_used_to_restore from msdb.dbo.restorehistory rh inner join msdb.dbo.backupset bs on rh.backup_set_id=bs.backup_set_id
inner join msdb.dbo.backupmediafamily bmf on
bs.media_set_id =bmf.media_set_id
ORDER BY [rh].[restore_date]
DESC
Through this query we found that our database was being restored everyday at 6 AM, and later on we got to know that there was job scheduled at 6:00 AM to copy production database backup and restore that backup at staging server.
Please let me know if you have any query related to this article.
No comments:
Post a Comment