question

wilsondav8 avatar image
wilsondav8 asked

Setting up a differential backup

I do a Full Backup every day of my Database. I am trying to use the previous Full backup to set up an additionnal Differential Backup, how can I do this? Thanks in advance
sql serverbackupdifferential-backup
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Are you just asking how to take a differential backup? - https://msdn.microsoft.com/en-us/library/ms188248.aspx
0 Likes 0 ·
You have several answers below. For each of those answers that are helpful you should click on the thumbs up next to the answers so that it turns green. If any one of the answers below lead to a solution to your problem, click on the check mark next to that one answer so that it turns green. This web site works best if you do the voting and marking of answers so that the next person who looks at your question knows what the solution was.
0 Likes 0 ·
JohnSterrett avatar image
JohnSterrett answered
I would recommend taking a look at Ola Hallengren's open source maintenance solution. This can create the backup jobs for you. Keeo in mind that a full backup that is taken without copy_only will clear the bitmap used for pages on the differential backup. This means, the page will be marked clean and the next differential backup will not include the previous changed pages from the time of this backup to the previous full backup. https://ola.hallengren.com/ I would also recommend taking a look at this book. It's under $10 US. http://www.amazon.com/SQL-Server-2014-Backup-Recovery/dp/150257389X
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

davismick643 avatar image
davismick643 answered
I would suggest you to do the daily backups as copy_only, so that it doesn't break the chain for transaction log backups or differential backups. After that the daily full backup won't be taken into account as basis for the differential backups. There are few articles for more information [ https://msdn.microsoft.com/en-us/library/ms191495%28v=sql.105%29.aspx][1] [ http://sqlbak.com/blog/copy-only-backups/][2] [1]: https://msdn.microsoft.com/en-us/library/ms191495%28v=sql.105%29.aspx [2]: http://sqlbak.com/blog/copy-only-backups/
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

"I would suggest you to do the daily backups as copy_only" NO! From the link you quoted: "A copy-only full backup cannot serve as a differential base or differential backup and does not affect the differential base."
1 Like 1 ·
It might also be fair to point out that a full backup, copy_only or otherwise, will not break a log backup chain.
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.