Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Differential backup question Expand / Collapse
Author
Message
Posted Thursday, February 14, 2013 5:49 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 26, 2014 4:08 AM
Points: 708, Visits: 3,286
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

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
Post #1420004
Posted Thursday, February 14, 2013 5:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:53 AM
Points: 5,132, Visits: 4,922
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.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1420008
Posted Friday, February 15, 2013 8:07 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 26, 2014 4:08 AM
Points: 708, Visits: 3,286
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

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
Post #1420567
Posted Wednesday, March 6, 2013 3:53 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 18, 2014 12:55 AM
Points: 386, Visits: 363
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.
Post #1427276
Posted Wednesday, March 6, 2013 4:00 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 18, 2014 12:55 AM
Points: 386, Visits: 363
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, 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!


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.
Post #1427279
Posted Thursday, March 7, 2013 8:10 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 21, 2014 9:46 AM
Points: 387, Visits: 1,078
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?
Post #1428010
Posted Thursday, March 7, 2013 9:41 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:44 PM
Points: 42,443, Visits: 35,498
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1428071
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse