January 13, 2010 at 9:21 am
Today, one of my tests failed for checking database backups. I tried restoring via SSMS by selecting the FULL and most recent DIFF in the history and restore on the same database and instance. This one database keeps telling me it's for a different database though this is the one that is in the history. I shouldn't have to force an overwrite and this works for other databases on the instance.
What could I be missing? We are running a custom script, based on Paul Randal's way of calculating the percentage of change since last full backup, that determines if we should do a FULL or DIFF backup. The script runs one of the lines below so the fact that I can do this for other databases on that instance has me stumped.
-- FULL backup code
BACKUP DATABASE @db TO DISK = @Filename WITH COPY_ONLY, NOFORMAT, NOINIT, NAME = @Desc, SKIP, REWIND, NOUNLOAD, STATS = 25
-- DIFF backup code
BACKUP DATABASE @db TO DISK = @Filename WITH COPY_ONLY, NOFORMAT, NOINIT, NAME = @Desc, SKIP, REWIND, NOUNLOAD, STATS = 25, DIFFERENTIAL
/* Anything is possible but is it worth it? */
January 13, 2010 at 9:46 am
Gatekeeper (1/13/2010)
Today, one of my tests failed for checking database backups. I tried restoring via SSMS by selecting the FULL and most recent DIFF in the history and restore on the same database and instance. This one database keeps telling me it's for a different database though this is the one that is in the history. I shouldn't have to force an overwrite and this works for other databases on the instance.What could I be missing? We are running a custom script, based on Paul Randal's way of calculating the percentage of change since last full backup, that determines if we should do a FULL or DIFF backup. The script runs one of the lines below so the fact that I can do this for other databases on that instance has me stumped.
-- FULL backup code
BACKUP DATABASE @db TO DISK = @Filename WITH COPY_ONLY, NOFORMAT, NOINIT, NAME = @Desc, SKIP, REWIND, NOUNLOAD, STATS = 25
-- DIFF backup code
BACKUP DATABASE @db TO DISK = @Filename WITH COPY_ONLY, NOFORMAT, NOINIT, NAME = @Desc, SKIP, REWIND, NOUNLOAD, STATS = 25, DIFFERENTIAL
A COPY_ONLY backup can not be the base for restoring a differential backup.
January 13, 2010 at 9:56 am
Whoops, wrong window on the copy and paste but makes me wonder if the initial FULL was done with the improper arguments. But had that been the case, wouldn't the DIFFs failed because there wasn't a proper FULL backup to begin with?
-- FULL
BACKUP DATABASE @db TO DISK = @Filename WITH NOFORMAT, NOINIT, NAME = @Desc, SKIP, REWIND, NOUNLOAD, STATS = 25
-- DIFF
BACKUP DATABASE @db TO DISK = @Filename WITH NOFORMAT, NOINIT, NAME = @Desc, SKIP, REWIND, NOUNLOAD, STATS = 25, DIFFERENTIAL
/* Anything is possible but is it worth it? */
January 13, 2010 at 10:02 am
Gatekeeper (1/13/2010)
Whoops, wrong window on the copy and paste but makes me wonder if the initial FULL was done with the improper arguments. But had that been the case, wouldn't the DIFFs failed because there wasn't a proper FULL backup to begin with?
-- FULL
BACKUP DATABASE @db TO DISK = @Filename WITH NOFORMAT, NOINIT, NAME = @Desc, SKIP, REWIND, NOUNLOAD, STATS = 25
-- DIFF
BACKUP DATABASE @db TO DISK = @Filename WITH NOFORMAT, NOINIT, NAME = @Desc, SKIP, REWIND, NOUNLOAD, STATS = 25, DIFFERENTIAL
Not if there was an earlier, valid, full backup.
Example:
Full Backup -- Base for Log and Differential backups
Diff -- Changes since last full backup
Full Backup WITH COPY_ONLY -- basically a full backup snapshot
Diff -- Changes since last full backup (first one listed)
January 13, 2010 at 10:08 am
Run the following substituting your database name where indicated.
select
database_name,
is_copy_only,
type as BackupType
from
msdb.dbo.backupset
where
database_name = 'YourBDNameHere'
order by
backup_start_date;
January 13, 2010 at 10:09 am
Lynn Pettis (1/13/2010)
Not if there was an earlier, valid, full backup.Example:
Full Backup -- Base for Log and Differential backups
Diff -- Changes since last full backup
Full Backup WITH COPY_ONLY -- basically a full backup snapshot
Diff -- Changes since last full backup (first one listed)
Thanks! Makes sense as to why the diffs continued. The FULL I was trying was most likely a COPY_ONLY (during our initial testing three months ago). It was near the beginning of our testing on Dev so it makes sense why the newer databases restore fine on Dev.
/* Anything is possible but is it worth it? */
January 13, 2010 at 10:26 am
Gatekeeper (1/13/2010)
The FULL I was trying was most likely a COPY_ONLY (during our initial testing three months ago)
Figured it out. The message we correct the first time of it being a different database. The database was repopulated from Prod a month ago using a backup, breaking the FULL/DIFF history before. Thanks for the script because it got me in the right direction. I couldn't figure out why someone issued a single backup three hours before the normal backup time and it was exactly the same time as the Prod backup. Forgot that restoring the database logs it in the backup history. I should probably include the instance name on the backups so I know when it's repopulated.
/* Anything is possible but is it worth it? */
January 13, 2010 at 10:40 am
Thank you for the feedback. I'm glad I could help.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply