marrowyung
asked on
The checking of backup history and tools/method
Right now we experience backup chain problem that we are doing full + differential by using litespeed.
But our vendor doing another full backup themselves and that we guess is doing just between our full and diff, so when we tried to restore the diff. backup, SQL will complain that the backup chain has been broken. So we can't restore our differential backup.
someone give the following script for me to check backup but I can't see how to make the most out of it, please help to coach me what column of information I should use in order to proof/check it:
select * from msdb..backupset bs
inner join msdb..backupmediafamily bf
on bs.media_set_id = bf.media_set_id
where database_name = 'database name'
the physical_device_name cleare to be the backup file output name, but i don't see the differential backup deive name, so it don't exist ?? I can't see why.
But our vendor doing another full backup themselves and that we guess is doing just between our full and diff, so when we tried to restore the diff. backup, SQL will complain that the backup chain has been broken. So we can't restore our differential backup.
someone give the following script for me to check backup but I can't see how to make the most out of it, please help to coach me what column of information I should use in order to proof/check it:
select * from msdb..backupset bs
inner join msdb..backupmediafamily bf
on bs.media_set_id = bf.media_set_id
where database_name = 'database name'
the physical_device_name cleare to be the backup file output name, but i don't see the differential backup deive name, so it don't exist ?? I can't see why.
Brian Crowe
I don't know the answer to your direct question but you should change the vendor back up to a copy-only backup so it won't interfere with the log history.
ASKER
you can ansewr something like:
1) They stop their backup.
2) They use our backup instead of one done by them.
3) use copy backup that don't break the chain.
but what the above query help uS ?
1) They stop their backup.
2) They use our backup instead of one done by them.
3) use copy backup that don't break the chain.
but what the above query help uS ?
ASKER
one thing, now to do copy bakcup and is the copy backup output file restore in the same way as the normal backup ?
'
I am here referring to the full copy backup and full restoration.
'
I am here referring to the full copy backup and full restoration.
ASKER
BriCrowe,
how the copy-only backup file be restore ? restore exactly the same way as the full backup restore?
I am not sure why the COPY-ONLY backup introduce, what is it for ? the backup usually don't need to interferce, why need copy-only backup to do a backup between full and differential backup ?
how the copy-only backup file be restore ? restore exactly the same way as the full backup restore?
I am not sure why the COPY-ONLY backup introduce, what is it for ? the backup usually don't need to interferce, why need copy-only backup to do a backup between full and differential backup ?
Yes, you can restore a copy-only backup just as you would any other .bak file.
The copy-only backup exists specifically to avoid interfering with systems that use log-shipping or other transaction log dependent processes.
For instance our production environment currently uses log-shipping for disaster recovery purposes. If I want to make a backup for the stage or development environment than I can opt to create a copy-only backup so that it doesn't interfere with the log-shipping timeline. Below is a link to the relevant Microsoft technet page:
http://technet.microsoft.com/en-us/library/ms191495.aspx
The copy-only backup exists specifically to avoid interfering with systems that use log-shipping or other transaction log dependent processes.
For instance our production environment currently uses log-shipping for disaster recovery purposes. If I want to make a backup for the stage or development environment than I can opt to create a copy-only backup so that it doesn't interfere with the log-shipping timeline. Below is a link to the relevant Microsoft technet page:
http://technet.microsoft.com/en-us/library/ms191495.aspx
ASKER
"The copy-only backup exists specifically to avoid interfering with systems that use log-shipping or other transaction log dependent processes."'
it seems that I only helps on backup that relies on full and differential backup but not to log backup.
"For instance our production environment currently uses log-shipping for disaster recovery purposes. If I want to make a backup for the stage or development environment than I can opt to create a copy-only backup so that it doesn't interfere with the log-shipping timeline."
this is easy to understand .
it seems that I only helps on backup that relies on full and differential backup but not to log backup.
"For instance our production environment currently uses log-shipping for disaster recovery purposes. If I want to make a backup for the stage or development environment than I can opt to create a copy-only backup so that it doesn't interfere with the log-shipping timeline."
this is easy to understand .
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks.