Solved

Restoring Full-Differential Backup

Posted on 2011-03-10
5
689 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

867 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now