Solved

Restoring Full-Differential Backup

Posted on 2011-03-10
5
690 Views
Last Modified: 2012-05-11
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
Comment
Question by:itbossman
  • 3
5 Comments
 
LVL 14

Accepted Solution

by:
Daniel_PL earned 500 total points
ID: 35099965
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
 

Author Comment

by:itbossman
ID: 35100268
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
 
LVL 7

Expert Comment

by:mikkilineni
ID: 35100273
It look slike you are using backup exec from symantec to take the backup too..You will need to disable it
0
 

Author Comment

by:itbossman
ID: 35100366
i am using 'Maintainance plan' under management tree on SSMS. How do i check about this backup exec?
How to disable. thanks
0
 

Author Comment

by:itbossman
ID: 35100437
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question