Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Restoring Full-Differential Backup

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

Suggested Solutions

Title # Comments Views Activity
SQL query with cast 38 51
SQL Insert parts by customer 12 42
SQL Server 2012 r2 - Varible Table 3 31
Why I am having this error in my query? 2 25
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

790 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