May 20, 2008 at 3:21 am
Hi guys,
I've this strategy situation:
A sql server agent that:
- make a total backup during the first day of the week.
- make a differential backup during other days (log backup are disabled, but will not talk abuot that decision...).
Backups are made out into sql server machine hd...
An external backup software that in parallel to the sql server agent, make a total backup into an external device (another machine), every day.
This sftw interact with sql server agent, because in the backup chronology I can see both backups type in this sequence :
First Day:
BCK TOT SQL Server
BCK TOT ExtSoftware
Others Day:
BCK TOT ExtSoftware
BCK DIFF SQL Server
That was a problem trying to restore dbs, because differential backup operation considered the BCK TOT ExtSoftware made every day as the total backup to use, like a starting base to do the differential backup...
It's a problem because in the backup chronology the device signed for the BCK TOT ExtSoftware is a tape that don't exist!
This, i suppose, append because this external software simulate a tape backup using the sql agent... (there's no way to explain to my boss, that is unhelpful...)
So before every differential backup, I delete the backup chronology made out of external sftw using the same query (re-adapted) contained into msdb.sp_delete_backuphistory...
Infact now, into backup chronology, you can see only sql server agent's backup.
But when I try to restore a differential backup, it happend that I can restore only the first (the backup made during the second day).
When I try to restore others differential backup, sql server restore at first step the total backup, but it can't restore the differential (for example made durind the third day)...
It's so strange but I'm sure that the problem is this external software (that I can't stop)...
Do anyone know a possible workaround? Or how sql server manage the chronology backup, for make the differetial backup?
Hope to be as clear as possible (my english is a little bit rusty)...
Regards.
Andrea
May 20, 2008 at 4:22 am
"delete the backup chronology made out of external sftw using the same query (re-adapted) contained into msdb.sp_delete_backuphistory"
As the information in the msdb backup tables is not used by the restore process, modifying or deleting this information is not a useful activity.
Each backup file contains information about the Log Sequence Number (LSN) as shown from a message in the ERRORLOG file:
Database backed up: Database: msdb, creation date(time): 2000/08/06(01:40:56), pages dumped: 1930, first LSN: 632:351:1, last LSN: 632:354:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'\\usfgchidata2-4\shareddata18\Data48\DSOD\msdb\msdb_db_200805182000.BAK'}).
When restoring a database with no-recovery, the LSN information is recorded in the database being restored and for each subsequent restore of differential or transaction logs, the LSN of the backup is compared to insure that the LSN is higher and that there are no gaps.
In your case, to restore a database:
1. Use the full backup from the third party software.
2. Use the differential backup from SQL Server.
SQL = Scarcely Qualifies as a Language
May 20, 2008 at 4:51 am
Hi, thanks...
...I supposed it was the most logical solution...
But I hoped there was something most 'elegance'... that allow both backup strategy to be independent each other...
It's useless make a total backup and a differential back up every night... especially if sql server differential back up depend on a third parts software total backup, placed onto another machine...
I think we must re-organize the backup strategy...
Thank you so much.
Andrea
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply