October 13, 2009 at 6:43 am
I have a production server. Daily the databases go through a full backup. The backups are copied to a testing server and restored there.
For auditing purposes, i now need to send a mail after the restore to the test server that provides:
-The date the backups were taken on.
-The date they were restored to the Test server
I can get the last restore date using the below query:
select destination_database_name, max(restore_date) as restore_date from msdb.dbo.restorehistory
group by destination_database_name
The date the backups were taken on is trickier though. is this backup header info stored in any system tables after a backup is restored?
Note the backups are litespeed backups so the standard backup with headeronly type stuff wont work.
Thanks.
October 13, 2009 at 7:29 am
you would have to refer back to the prod server, so a linked server would be required.
Presuming you know the full path to the backup file you could use this query as a starter
select a.backup_start_date from backupset a join backupmediafamily b on a.media_set_id = b.media_set_id where b.physical_device_name = 'drive:\path to file\backup file name'
you would have to use 4 part naming convention if querying back to your prod server.
---------------------------------------------------------------------
October 13, 2009 at 8:02 am
When you restore a backup from another server, the info about the backup you restored from is stored in the same tables where info about local backups goes:
msdb.dbo.backupset
msdb.dbo.backupmediafamily
October 13, 2009 at 8:24 am
We name our backup files w/ date/time stamps, so it's always clear on which date they were created. The standard SQL Backup job in the maintenance plan does this for you and as far as I know all available 3rd party programs can do the same.
Or you can create your own date/time stamp filename w/ something like:
set @strDBName = @strDBName + '_' +
CAST(DatePart(yyyy,GetDate()) AS varchar(4)) +
RIGHT('0' + CAST(DatePart(mm,GetDate()) AS varchar(2)),2) +
RIGHT('0' + CAST(DatePart(dd,GetDate()) AS varchar(2)),2) +
RIGHT('0' + CAST(DatePart(hh,GetDate()) AS varchar(2)),2) +
RIGHT('0' + CAST(DatePart(n,GetDate()) AS varchar(2)),2) +
'.bak'
This will add a date/time stamp as follows to the @strDBName string: _mmddhhnn.bak
October 13, 2009 at 9:08 am
Michael Valentine Jones (10/13/2009)
When you restore a backup from another server, the info about the backup you restored from is stored in the same tables where info about local backups goes:msdb.dbo.backupset
msdb.dbo.backupmediafamily
apologies, so it does. :blush: . I learnt something. you can join on backup_set_id:
select a.backup_start_date from msdb..backupset a join msdb..restorehistory b on a.backup_set_id = b.backup_set_id
where b.restore_date = (select max(restore_date) from msdb..restorehistory where destination_database_name = 'your db')
taking the date off the backup file name looks like a nice simple option if your file name is date suffixed.
---------------------------------------------------------------------
October 13, 2009 at 10:13 am
Taking this a step further, you can see quite a bit of information about the backup it was restored from.
select
a.backup_start_date,
a.backup_finish_date,
Backup_Elapsed_Hours =
convert(numeric(6,2),round(
datediff(ms,0,a.backup_finish_date-a.backup_start_date)
/(3600000.0),2)),
Backup_Size_GB =
convert(numeric(8,2),round(a.backup_size/(1073741824.),2)),
Source_Server = a.server_name,
Source_Database = a.database_name,
Backup_File_Name =c.physical_device_name
from
msdb..backupset a
join
msdb..restorehistory b
on a.backup_set_id = b.backup_set_id
join
msdb.dbo.backupmediafamily as c
on a.media_set_id = c.media_set_id
where
b.restore_date in (
select
max(restore_date)
from
msdb..restorehistory
where
destination_database_name = 'MyDatabase' )
October 13, 2009 at 11:02 am
Michael Valentine Jones (10/13/2009)
When you restore a backup from another server, the info about the backup you restored from is stored in the same tables where info about local backups goes:msdb.dbo.backupset
msdb.dbo.backupmediafamily
Cool, i found this table but thought it only stored backup info of the current server, and not from the other server where the backups came from. this is perfect for my needs. thanks for all the responses!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply