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 12»»

Create a policy to avoid backups withou copy_only option Expand / Collapse
Author
Message
Posted Wednesday, October 20, 2010 12:36 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 3, 2011 2:56 AM
Points: 69, Visits: 92
Hi.
We have several databases in full recovery model. Daily we make full backups, but sometimes we need take a full backup for development purposes. I would like guarantee that everyone "remember" check copy_only option. Can I create a policy, or a DDL trigger that avoid this mistake?
Thanks in advance,
Fran
Post #1007482
Posted Wednesday, October 20, 2010 2:15 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: Yesterday @ 9:36 AM
Points: 42,765, Visits: 35,863
Are you doing diff backups? If not, it's not so critical to use the copy_only option


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 #1007516
Posted Friday, October 22, 2010 4:42 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 3, 2011 2:56 AM
Points: 69, Visits: 92
Since I 'm not doing differentials backups, hasn't this option any effect?
Thank you!
Post #1009073
Posted Friday, October 22, 2010 8:24 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: Yesterday @ 9:36 AM
Points: 42,765, Visits: 35,863
Correct. No full backup (with or without copy_only) breaks the log chain.


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 #1009231
Posted Friday, October 22, 2010 1:28 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 1:26 PM
Points: 405, Visits: 1,431
GilaMonster (10/22/2010)
Correct. No full backup (with or without copy_only) breaks the log chain.


Gail, I am confused here.

For example, from FULL BAKCUP A to FULL BACKUP B, we have a log chain. If you issue a FULL backup C without COPY_ONLY between A and B, then, you break the previous log chain A to B.

Right?

According to Microsoft, you should backup log after the Full backup C to establish a new log chain.
Post #1009461
Posted Friday, October 22, 2010 10:35 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, June 21, 2014 2:00 AM
Points: 343, Visits: 1,517
For example, from FULL BAKCUP A to FULL BACKUP B, we have a log chain. If you issue a FULL backup C without COPY_ONLY between A and B, then, you break the previous log chain A to B.

Right?

No. You can make as many full backups as you want between full backup A and B, and you will not break the log chain.

According to Microsoft, you should backup log after the Full backup C to establish a new log chain.

Could you please provide the URL to the article? I would like to see the context the recommendation was made in.

Thanks.


Ray Mond
Yohz Software
Providing SQL Server database tools for 9 years and counting.
http://www.yohz.com
Post #1009561
Posted Friday, October 22, 2010 11:32 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 1, 2014 1:26 PM
Points: 2,242, Visits: 3,645
Full Backups do not break transaction log chain.
However they do reset differential backups. If you take full backup wth copy_only option, the differential backup will contain pages that were modified after the last full backup without copy_only option.






Pradeep Singh
Post #1009564
Posted Friday, October 22, 2010 11:35 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 1, 2014 1:26 PM
Points: 2,242, Visits: 3,645
If you have truncated the log, you will need to take a full OR diff backup to start a new log chain.
another case, if ur db has never been backed up(full recovery) then until you take full backup a new log chain wont start. db behaves as if it were in simple recovery mode.





Pradeep Singh
Post #1009565
Posted Saturday, October 23, 2010 3:11 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: Yesterday @ 9:36 AM
Points: 42,765, Visits: 35,863
Wildcat (10/22/2010)
If you issue a FULL backup C without COPY_ONLY between A and B, then, you break the previous log chain A to B.

Right?


Wrong.
Full backups do not and never have broken the log chain.

It's trivial to test and, if you want, I can find a blog post from the former program manager of the Storage Engine stating and proving this.

The only things that break the log chain are:
* Switch to simple recovery
* Backup log with nolog/truncate only
* Deleting a log backup (won't stop you taking log backups, will stop you recovering with them)

According to Microsoft, you should backup log after the Full backup C to establish a new log chain.

Link please.
I need to write a scathing email to either the author or the MS documentation people if such an official recommendation exists



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 #1009577
Posted Thursday, October 28, 2010 11:54 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 1:26 PM
Points: 405, Visits: 1,431
[quote
The only things that break the log chain are:
* Switch to simple recovery
* Backup log with nolog/truncate only
* Deleting a log backup (won't stop you taking log backups, will stop you recovering with them)

[/quote]

Sorry, after reading Microsoft document 3 times, I misunderstood the concept.

Thanks, Gail.
Post #1012546
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse