Differential backup created without a FULL backup?!
-
Good morning,
I thought that the only way to create a differential backup is to have a FULL backup exist in the first place. But I have a database created on the 18th this month and the daily differential backup job has complet3ed successfully and created diffs for this database.
Thanks,
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn -
Abu Dina (10/26/2012)
Good morning,I thought that the only way to create a differential backup is to have a FULL backup exist in the first place. But I have a database created on the 18th this month and the daily differential backup job has complet3ed successfully and created diffs for this database.
Thanks,
This is an easy one to test.
Create a database (call it what you choose) and then attempt to do a Diff. Backup.
Note the message it returns.
-
I think its not possible... but can you pls share the screen shot of successful backup OR can you gimme the logs of the same successful backup ?
-
jitendra.padhiyar (10/26/2012)
I think its not possible... but can you pls share the screen shot of successful backup OR can you gimme the logs of the same successful backup ?A Differential Backup requires a Differential Base (Full Backup).
You might want to check the backup history of that databse in msdb.
-
So If we have not taken full backup, than differential backup will work ? Is it just check with MSDB file and run ? Please clear my doubt...
-
I'm just being stupid. I KNOW you can't have a diff without a full backup. Here is a test I did:
Something's very strange going on.
On this particular server I have two backup jobs, 1 that runs every two weeks which does full backups and another job which does the daily differentials.
The Full backup job last ran on the 16th October. The database in question was created on the 18th and I have looked in MSDB and there are only 6 entries for this database and the backup type is I so there are no Ds!
Something's weird going on....
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn -
A differential backup is a backup of all pages changed since the last full backup, so there MUST be a preceding full backup somewhere. However, there is no requirement for said full backup to have been made on the same server as the diff backup. So, if you have created a database and restored it from a full backup made elsewhere, you CAN proceed to make a differential backup, which will then be based upon the same full backup you used to restore the database.
Kind regards,Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)It is better to light a candle than to curse the darkness. (Chinese proverb) -
That makes sense. I know you're right.. there must be a FULL somewhere but as you said, if the DB was created from a backup on a different server then this probably explains why there are no record of a full backup on the server of the new DB.
Thanks.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn -
Yep. Your server's backup history will only show backups that were actually made on this server.
However, the server also keeps track of your restore history.
So, if you need to know which restores have been made and from what source, you can ask SQL Server and it will gladly provide the details for you. 🙂
-- check_db_restore_time_and_source.sql
-- This script will list out all DB restores made on a SQL Server
-- and provide the following details regarding each restore:
--
-- destination_database_name = name of DB that was restored
-- restore_date = date and time of restore start
-- backup_start_date = start date and time of backup used for restore
-- backup_finish_date = end date and time of backup used for restore
-- source_database_name = DB name from original backup
-- backup_file_used_for_restore = full path to the backup file used for restore
----- Begin Script, check_db_restore_time_and_source.sql -----
SELECT [rs].[destination_database_name],
[rs].[restore_date],
[bs].[backup_start_date],
[bs].[backup_finish_date],
[bs].[database_name] as [source_database_name],
[bmf].[physical_device_name] as [backup_file_used_for_restore]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs
ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf
ON [bs].[media_set_id] = [bmf].[media_set_id]
ORDER BY [rs].[restore_date] DESC
Kind regards,Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)It is better to light a candle than to curse the darkness. (Chinese proverb)
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply