Backups

Question

Backups

iseedeadpeople

Mr or Mrs. 500

Points: 566
More actions
November 2, 2017 at 8:40 am

#402643

Hi, Guys i have a one question. I have a db where backup is 50 gb.Is it possible to backup only data for the last 10 days and the backup will be 2,3 gb. Thank you 🙂
Caution, this is not a backup, but a data dump. (and actually bad advice)Caution, this is not a backup, but a data dump. (and actually bad advice)

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 1

iseedeadpeople - Thursday, November 2, 2017 8:40 AM
Hi, Guys i have a one question. I have a db where backup is 50 gb.Is it possible to backup only data for the last 10 days and the backup will be 2,3 gb. Thank you 🙂

No, but you could accomplish that in a different way, please keep in mind you might not have all the benefits of a real SQL Backup.

1. bcp table content out with a where clause on your date. Caution, this is not a backup, but a data dump. (and actually bad advice)
2. Full backup once a week, and differentials daily with transaction log backups in between.
3. Depending on your version and license of SQL, you can use Backup compression, and depending on you data, could see a significant decrease in backup size.
4. Implement an archiving strategy to keep you live database smaller, resulting in smaller backups.

You have some options, unsure of your exact reason and requirements, so the above are just options that come to mind, not necessarily fit for your purpose.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 2

iseedeadpeople - Thursday, November 2, 2017 8:40 AM
Hi, Guys i have a one question. I have a db where backup is 50 gb.Is it possible to backup only data for the last 10 days and the backup will be 2,3 gb. Thank you 🙂

No I'm afraid not. You could take a differential backup which would backup everything since the last full backup but you would still need the full backup to perform a restore.

Thanks

iseedeadpeople Mr or Mrs. 500 Points: 566 More actions · Answer 3

Thanks Henrico Bekker, i will chek this bcp utillity 🙂

jasona.work SSC Guru Points: 50117 More actions · Answer 4

iseedeadpeople - Thursday, November 2, 2017 8:40 AM
Hi, Guys i have a one question. I have a db where backup is 50 gb.Is it possible to backup only data for the last 10 days and the backup will be 2,3 gb. Thank you 🙂

Short answer?
No.

Long answer?
Sure, take a full backup on day 1, then differential backups each day. Of course, you still have to take at least one 50GB backup, and really if you go this route I'd lean towards a Full backup each week (at 50GB each time unless the DB has grown in the interim,) and if it's in Full Recovery you need to factor in doing Transaction Log backups as well.

jasona.work SSC Guru Points: 50117 More actions · Answer 5

iseedeadpeople - Thursday, November 2, 2017 8:54 AM
Thanks Henrico Bekker, i will chek this bcp utillity 🙂

Please note what he said about the BCP method: