Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Restoring Full-Differential Backup

Posted on 2011-03-10
5
Medium Priority
?
698 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 1500 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

971 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