February 14, 2013 at 5:49 am
I do FULL backups every two weeks and daily differentials on a number of SQL instances.
The backup command I use for the differential backups in my sproc is like the below:
BACKUP DATABASE MyDatabase TO DISK = 'MyDatabase.Differential' WITH DIFFERENTIAL, NAME = 'MyDatabase Differential Backup' , BUFFERCOUNT = 1024, CHECKSUM
At the end of a two week cycle I end up with two files
MyDatabase.FULL - Day 1
MyDatabase.Differential - DAY13
When I do my restores I start with the FULL obviously and then my latest differential. at this stage I am given the option to choose from 13 files, 1 for each day of the differential backup. This is great because it means I can restore to any day I want but what I don't get is, I am not creating a new differential backup file. I am overwriting the backup file using the same name. How does SQL Server work this out?
Hope I make sense!
---------------------------------------------------------
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
February 14, 2013 at 5:54 am
You dont have an INIT and FORMAT so it is appending the diff backup to the media set not overwriting it, so it contains all 13 days diff backups.
Highly risky move, should be backing up to individual files not one big file, as should anything happen to that 1 file you cannot restore to any of the subfiles contained within.
February 15, 2013 at 8:07 am
Thank Tony. I've re-read the info on the BACKUP DATABASE command and I see what you mean.
Lucky I've not had any files get corrupted YET!
---------------------------------------------------------
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
March 6, 2013 at 3:53 am
anthony.green (2/14/2013)
You dont have an INIT and FORMAT so it is appending the diff backup to the media set not overwriting it, so it contains all 13 days diff backups.Highly risky move, should be backing up to individual files not one big file, as should anything happen to that 1 file you cannot restore to any of the subfiles contained within.
Agree. I put each backup to a separate file with name of the database, type of backup, and time stamp. example: TestDB_Full_20130305_235548.BAK
--------------------------------------------------------------------------------------
Hai Ton
My Db4Breakfast blog.
March 6, 2013 at 4:00 am
Abu Dina (2/14/2013)
I do FULL backups every two weeks and daily differentials on a number of SQL instances.The backup command I use for the differential backups in my sproc is like the below:
BACKUP DATABASE MyDatabase TO DISK = 'MyDatabase.Differential' WITH DIFFERENTIAL, NAME = 'MyDatabase Differential Backup' , BUFFERCOUNT = 1024, CHECKSUMAt the end of a two week cycle I end up with two files
MyDatabase.FULL - Day 1
MyDatabase.Differential - DAY13
When I do my restores I start with the FULL obviously and then my latest differential. at this stage I am given the option to choose from 13 files, 1 for each day of the differential backup. This is great because it means I can restore to any day I want but what I don't get is, I am not creating a new differential backup file. I am overwriting the backup file using the same name. How does SQL Server work this out?
Hope I make sense!
You can use a dynamic tsql string and build the file_name in this format:
fileName = databaseName + '_' + backupType + '_' + dateTime + fileExtension
just declare these variables with varchar data type. For the dateTime, use the convert or cast function to convert to varchar.
Hope this helps.
--------------------------------------------------------------------------------------
Hai Ton
My Db4Breakfast blog.
March 7, 2013 at 8:10 am
anthony.green (2/14/2013)
Highly risky move, should be backing up to individual files not one big file, as should anything happen to that 1 file you cannot restore to any of the subfiles contained within.
Anthony Green, Could you please explain me? If 1 file corrupted, can not I restore 5th differential file?
March 7, 2013 at 9:41 am
SQL Show (3/7/2013)
anthony.green (2/14/2013)
Highly risky move, should be backing up to individual files not one big file, as should anything happen to that 1 file you cannot restore to any of the subfiles contained within.Anthony Green, Could you please explain me? If 1 file corrupted, can not I restore 5th differential file?
No, because the scenario described there are multiple backups in a single file, corrupt the file and all backups are gone, not just one.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply