Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 701
  • Last Modified:

Restoring Full-Differential Backup

My fullbackups happen at 12:15am and my diffrential at 6:15a everyday, I have attatched 3 screenshots of last 3 days backs, that I got from the following query:
SELECT  
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   msdb.dbo.backupset.database_name,  
   msdb.dbo.backupset.backup_start_date,  
   msdb.dbo.backupset.backup_finish_date,
   msdb.dbo.backupset.expiration_date,
   CASE msdb..backupset.type  
       WHEN 'D' THEN 'Database'  
       WHEN 'L' THEN 'Log'  
   END AS backup_type,  
   msdb.dbo.backupset.backup_size,  
   msdb.dbo.backupmediafamily.logical_device_name,  
   msdb.dbo.backupmediafamily.physical_device_name,  
   msdb.dbo.backupset.name AS backupset_name,
   msdb.dbo.backupset.description
FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE  (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)  
ORDER BY  
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_finish_date


-------------------------------------------------------------
I run my restore backup query to restore the FUll backup of my 8th and then my differntial at 8th and then i get the following error:
'This differential backup cannot be restored because the database has not been restored to the correct earlier state.'

I run the above query to find out  that there seems to be a fullbackup 'Battelle_MSCRM_00__6e62932d_6f2b_4358_8d93_f64063b2a773_' right after my intended fullbackup,
'D:\DbBackups\Battelle_MSCRM\Battelle_MSCRM_backup_201103080015.bak'. So, when i run the following query:

RESTORE DATABASE Battelle_test --FULL BACKUP
   FROM DISK = 'C:\Documents and Settings\Administrator\Desktop\battelle\Battelle_MSCRM_backup_201103080015.bak'
   WITH  NORECOVERY;
go


RESTORE DATABASE Battelle_test --Differential BACKUP
   FROM DISK = 'C:\Documents and Settings\Administrator\Desktop\battelle\Battelle_MSCRM_backup_201103080615.bak'
   WITH  RECOVERY;
go

This is giving error because I am not putting in my Full 'Battelle_MSCRM_00__6e62932d_6f2b_4358_8d93_f64063b2a773_'. But I dont know where this 'missing' backup is getting stored. I have
not scheduled this backup. This doesnt look like temporary file either because, this doesnt happen on the 3/7 backups as you can see. Also, this happens on 3/9 backups, but it happens before my
intended 'C:\Documents and Settings\Administrator\Desktop\battelle\Battelle_MSCRM_backup_201103090015.bak', so it doesnt affect my sequence as it doesnt not fall between my intended full and differential.
Where as in 3/8, this 'unintended backup' is finished right after my 'intended' Full and i am not able to restore my differential.

So, my question is what is this 'Battelle_MSCRM_00__6e62932d_6f2b_4358_8d93_f64063b2a773_' and why is it happening on some days and not others. Even on the days it is happening, sometimes it is finishing
after my intended FULl and sometimes before.

3-7.PNG
3-8.PNG
3-9.PNG
0
itbossman
Asked:
itbossman
  • 3
1 Solution
 
Daniel_PLDB Expert/ArchitectCommented:
You cannot restore because there is newer full backup and log chain numers won't fit.
Please verify your backup exec tool settings because it seems it is its fault.
If you want take ocasional backups please use WITH COPY_ONLY clause, it will not brake the chain of log numbers.
0
 
itbossmanAuthor Commented:
thank you. I know i had made anothr backup plan before, but deleted it from the sql agent but had not deleted that from the management tool tree. Both of these backup plans had the same time for backup schedule. I deleted the old backup plan from the management tool and did the test backups of full and differential again and this time did not see that 'unintended' backup appear. I will check ahgain tomorrow.

How do i verify my backup exec tool settings.? I want to be confident on this is what is happening. Thanks for help.

0
 
mikkilineniCommented:
It look slike you are using backup exec from symantec to take the backup too..You will need to disable it
0
 
itbossmanAuthor Commented:
i am using 'Maintainance plan' under management tree on SSMS. How do i check about this backup exec?
How to disable. thanks
0
 
itbossmanAuthor Commented:
I checked my Symantec Backup Exec Remote Ageent Utility, and its settings, there seems to be nothing that is triggering database backups.

However i do see 'Backup Exec SQL Server Agent' under 'Backupset_name' when i run the above query on those 'unintended backups'.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now