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.
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
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 ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
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.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

marrowyungSenior Technical architecture (Data)Author Commented:
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 ?
0
Brian CroweDatabase AdministratorCommented:
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
0
marrowyungSenior Technical architecture (Data)Author Commented:
"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 .
0
Brian CroweDatabase AdministratorCommented:
Differential backups are also truncating the log file so if you make a full backup outside the normal schedule than you will interfere with the timeline of the normal backup process.  Essentially there will be a block of transactions between the last full/differential backup and the vendor's backup that your local backups would have no knowledge of.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.