SQL SERVER backup (differential and full)

I started working recently with sql server after years of working with Access.  The SQL database will be written to, read from, and backed up mostly from a C# desktop application.  I'm in the process of running through some trial backups,  using BACKUP DATABASE [C:\Data\Data.mdf] TO DISK ='C:\Backup\DATA.BAK' and found that each full backup seems to added (in terms of file size) to the full backup before it, so that DATA.BAK just doubles in size when I write sequential full backups to that location.  Is there an option to OVERWRITE the previous backup?  

Also, if I use the previous statement, but throw in 'WITH DIFFERENTIAL', how does that work?  When I did it, the differential seemed to be saved to DATA,BAK, but I thought it was supposed to be saved to a different file?  As you can see, I am new to SQL Server, so any advice would be appreciated.

Oh, the C:\Backup\DATA.BAK is just an example.  The actual path would be specified by the software user and would presumably be a network or external drive location.  Also, my plan is to have a full backup saved weekly, but a differential created daily (or more than daily) automatically by the software.
l2BravoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Deepak ChauhanSQL Server DBACommented:
--Full backup command to overwrite existing

BACKUP DATABASE <database name> TO  DISK = N'<location\backupfilename.bak>' WITH   INIT,   STATS = 10

--Differential Backup

BACKUP DATABASE <database name> TO  DISK = N'<location\backupfilename.bak>' WITH Differential, NOINIT,   STATS = 10

Open in new window

Deepak ChauhanSQL Server DBACommented:
You can see INIT keyword in the backup statement.

INIT = overwrite existing backupset
NOINIT ==Appand into existing backupset
--Full backup command to overwrite existing

BACKUP DATABASE <database name> TO  DISK = N'<location\backupfilename.bak>' WITH   INIT,   STATS = 10

--Differential Backup

BACKUP DATABASE <database name> TO  DISK = N'<location\backupfilename.bak>' WITH Differential, NOINIT,   STATS = 10

Open in new window

PadawanDBAOperational DBACommented:
So I don't know how other experts feel, but I hate dealing with backup sets (multiple backups in one backup file).  It's annoying to have to figure out what's in there when you're restoring.  For that reason, I give all of my backups unique file names (ie. add a timestamp at the end of the name) so that I don't have to worry about all that craziness.  If you're looking at automating your backups, there's no reason to reinvent the wheel, take a look at Ola Hallengren's backup stored procedures: https://ola.hallengren.com/.

To your question, it's saving within the backup set in the one backup file.

To your other question as to how differential backups work: SQL Server maintains a diff bitmap per database which is a mapping of 1 bit : 1 extent.  0 is unmodified, 1 is modified.  If it's modified, it gets included in the differential backup (64kb of data), otherwise it's not included.  The diff bitmap gets reset at each full backup.  So the longer you go between full backups, the larger your differential backup will become.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Brian CroweDatabase AdministratorCommented:
A better solution would be to decide how many days of backup history you would like and add a maintenance task to delete old .bak files.
Deepak ChauhanSQL Server DBACommented:
Yes i am also agree with PadawanDBA. One more disadvantage is that if disk sector become corrupted then all backups might be useless within backupset.
l2BravoAuthor Commented:
OK.  So, the differential backup is added to the .bak file that is specified as the last full backup.  I like the idea of the time stamped backup.  I can easily create some code to store backups by month and then date the backups in that month, for example.  The customers who use my software (I only have troubleshooting access to their computers after the software is deployed) need to keep their records for 10 years.  That doesn't necessarily mean they need backups for 10 years, but I can allow them to determine when to delete the backup files if they know where the files are located.  

But, what I think the best solution would be is to use monthly folders, perform a full backup each day (or week) and then use  differential backups at smaller intervals within that full backup plan.  

If there are any other suggestions (or corrections), please let me know.
PadawanDBAOperational DBACommented:
Differential backups are only added into that file if it is being added to the backup set.  If you give it a completely different filename, it will be it's own file with it's own backup set.  Another thing to keep in mind is that due to the nature of how differential backups work, for restoration purposes, they are dependent on that last full backup (you essentially restore the full backup and then whatever differential backup you determine is the one that you need).

I love the stuff out of the SQLSkills group, so I definitely recommend reading some of their stuff about on backups/restores: http://www.sqlskills.com/blogs/paul/category/backuprestore/

Brent Ozar and Company also have some great material for everything you could want to know: http://www.brentozar.com/sql/backup-best-practices/
l2BravoAuthor Commented:
So, then, I could create one full backup at the beginning of a month, then save 30 differential backup files, and, if necessary, restore the, say, May 1st backup file and then the May 30th differential which will restore all the data changed since May 1st?

Thanks also for the links.  I will look through those.
Deepak ChauhanSQL Server DBACommented:
Yes you are right.
PadawanDBAOperational DBACommented:
Yes.  But like I said earlier, the diff bitmap is going to continue to get dirtier and dirtier, which reduces your performance/storage benefits from differentials over time.  You also run the risk of basing the usefulness of 30 backups on the health of one backup (if there is some sort of corruption on that initial full backup, your differentials are all essentially useless)
l2BravoAuthor Commented:
OK.  I understand. I could do a daily backup (on login to the software) and then run differentials over the course of each day.  The customers who run the software perform anywhere from 50 - 500 tests each day, the results of which needed to be saved to the database.  The fewer tests that are lost in the event of hardware failure the better.  I would just have to do some calculations to figure out how much space would be required by all these backups.  The database is not huge, by any means, but still given enough time the backups could consume plenty of space.
PadawanDBAOperational DBACommented:
So in a case like that, you may want to consider something like weekly full backups, daily differentials, and half hour transaction log backups.  But you have the right idea about not making these types of decisions.  You want to back into it from the perspective of how much data is the company willing to tolerate being lost.  If it's a day's worth of data, who cares about transaction log backups.  If it's an hour, better start backing up those transaction logs.  If it's a week, forget differentials too - just roll full backups weekly.  But these are decisions the company should be making and you implementing those decisions into reality.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
l2BravoAuthor Commented:
This comment is only part of the solution to the original question, but the overall feedback PadawanDBA provided was excellent.  There were many good points, and references, that will help me formulate how to implement a backup strategy.
l2BravoAuthor Commented:
Thanks for your feedback.  I will have to at least frame the discussion for many of the customers my company deals with.  In truth, the answer may be that they cannot tolerate a single result being lost, which means, for some, INSERT transactions that are processed every minute.  If the customer loses one of the test results, it could mean a fine of several hundred dollars.  How pragmatic is it to backup the transaction logs every time a test (INSERT) is performed?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.