August 19, 2008 at 12:09 am
Comments posted to this topic are about the item Copy Only Backups - SQL School Video
August 19, 2008 at 2:31 am
Thanks Steve, This is very good information about copy_only backup and this resolves so much of my tension to take backups for developers while using transactional log backup policy. I did not perform it myself but if you please explain that when we restore this backup is there special way to restore or it is as simple as normal backup restore.
Thanks,
Shamas Saeed MCTS (70-431)
August 19, 2008 at 4:48 am
Copy Only backups should not be seen as a 'second class' backup. For large-scale OLTP shops they have an important role to play in maintaining availability. They are also useful in any shop that does not use differential backups.
When a normal backup clears the change bits needed for a differential backup, this causes contention on those pages. For big OLTP shops this becomes a bottleneck (maybe a small one, but still a problem). If there is no use made of differential backups, then why spend time resetting the change bits.
The ability for a backup to not clear the change bits was introduced to DB2 in about 1997. The place I worked in then welcomed this facility as it gave greater freedom to schedule backups without impacting the heavy OLTP load. Because DB2 logs these backups much the same as normal backups, and because relatively few DB2 shops use differential backups, this option rapidly became the standard way to do things. It will be interesting if the same happens with SQL Server installations.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
August 19, 2008 at 4:56 am
Thanks Steve.
However there is a puzzle in my head as I cannot catch the point,
how subsequent log recovery by DBA will be affected if developer takes a full backup without copy_only during the day ?
I think unplanned log backup, if not done with copy_only, will cause log truncation and thus the the original backup plan is harmed.
I also think that unplanned full backup, if not done with copy_only, will harm subsequent differential backup, as the data page may marked as already backup.
However, if my backup plan consist of only full backup and log backup, how an unplanned full backup if not done with copy_only can harm the recovery ?
I just thought that if I can successfully maintain every log backup, say from Mon day to Friday, then even if I lost all full backup during Tue to Thur, I can still recover the DB using Mon full backup and all subsequent log backup to recover Fri image.
If there is any wrong with my thought, please kindly correct me ... Eric
August 19, 2008 at 8:55 am
Steve,
Thank you for the information. I had seen this option but never really investigated it. While I can understand part f the reason for this option, I do not see using it. When I have provided a right now backup to someone, I just gave them a copy of the backup I just ran. This way I am always in control of the backups. We also suppose to log every single action on a server.
This is something I will have to look closer at to see if it make since to use.
Stacey W. A. Gregerson
August 19, 2008 at 10:33 am
Steve,
Great information! Thanks!
Mark
August 19, 2008 at 10:57 am
Glad you enjoyed it. Wasn't sure that I was up for this video training stuff, but perhaps I'll do a few more 🙂
A good DBA might not be deterred by this, but what if you have automated processes that move stuff to a DR site or somewhere else? They might not pick up a copy only backup if it's not in the same folder. I've seen, and done backups ad hoc and dropped them somewhere else. Or what if you have a process that removes old backups since they take up too much space, they might remove this morning's backup and leave you your ad hoc one and you need the other one for a recovery from the morning.
What if you provide the ability (through a proc or job) for a developer to run a backup? Don't want them to necessarily move your backup chain.
It's not necessarily for everyone, but there might be a time and place for many people. Especially if they're not the full-time dBA.
August 19, 2008 at 11:13 am
Very interesting, I had not yet run across this option. I can definitely see a use for it, as you show, to avoid breaking the backup chain. Without this, I would probably lean towards giving the developer a copy of last night's backup along with the transaction logs up to the current point.
This is something that I'm going to have to experiment with to get a better understanding. If I do a full backup without the copy option at 9am, and I backup to my normal backup area, a 10am (and subsequent) tlog backup will catch the changes and will be in sync with the 9am backup. If I need to do a point in time restore to 8:55, I restore the previous night's backup and the tlogs up to that point.
I don't see an issue unless you're absolutely fanatical about the backup chain being unbroken or you don't have a pristine copy of the 9am backup. The bit that Ed put in about DB2 and the change bits is fascinating and something that I had not considered, but we don't have enough transactions going through for that to be a concern here.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
August 19, 2008 at 11:19 am
One more thing, I sometimes grab backups before updates or patches, and sometimes I want that in a separate location. I might want that on another drive, this gives me a way to do that, not messing with where I keep everything else.
August 20, 2008 at 1:05 am
Great one Steve. Excellent.
August 20, 2008 at 1:20 pm
Wow, I never knew that one-off backups disrupted the LSN. Of course I'm a first year DBA so DUH!
I love learning new tools or methods that make my life easier.
This is one of those tips that make the short-list in my day-to-day routine.
Thanks Steve
August 20, 2008 at 2:06 pm
Bit confused about this :
We take Full Backups from Litespeed daily at 1AM. DEV team takes a Full backup at 9PM. We have hourly log backups running. After viewing the video I thought that if a failure occured at 10PM, then we would be able to restore till 8:10PM ( 1am full backup + 1:10 log backup + till 8:10PM ) , as the 9PM DEV Full backup will break the chain & we would need that DEV backup to go beyond 9PM to 9:10 T-log & so on.
I took a copy of our backups 1AM till 9:10 AM, I was able to restore them to a different server using Litespeed.
Also I took the Full backup of 1AM & I was able to restore to a point beyond the 1AM backup of the next day, how is this happening as the LSN should have been broken? If this is the normal behaviour then how is COPY_ONLY of help?
August 20, 2008 at 10:31 pm
Hi bakwas28,
As I understand when your Dev team take a full backup at 9.00 PM this will you backup to restore for 9.10 PM. Now 1.00 AM backup is not to use for this.
You can take a backup for Dev team at 9.00 PM using Copy_Only command and then you can use your 1AM backup and logs to restore at any time.
Important thing for me is when i tried to restore copy_only backup on another system i am failed to do so because it is not returning any backup header record.
Then i used these steps to restore copy_only backup.
1- Resotre Previous Full Backup Using Not-Committed Transaction (Option2 in backups)
2- Resotre Previous Transactional Backup Using Not-Committed Transaction (Option2 in backups)
3- Copy Only Backup Using Commited transaction (Option1 in Backups)
Is there any other way to restore Copy_only backup.
Last thing is i have a Full text file in my file group. how can i backup and restore it using copy_only.
Thanks.
August 21, 2008 at 4:03 am
Hi all
I think eric.fung and bakwas28 are correct -
As Eric said:
I just thought that if I can successfully maintain every log backup, say from Mon day to Friday, then even if I lost all full backup during Tue to Thur, I can still recover the DB using Mon full backup and all subsequent log backup to recover Fri image.
Steve, please can you post a reply to this? It's confusing a number of people, and in my own humble experience, taking a second full backup does NOT invalidate any previous backup as far as LSN goes! The SQL 2005 documentation does not state this either.
Thanks
Si
PS: Loved the video apart from the minor nit-pick! Now, if those log backups in your video had been DIFFERENTIAL backups, I think you would have had a valid point! 😀
August 21, 2008 at 5:22 pm
Shamas, your comment doesn't make sense.
Steve, I think you should clarify as you went into all this trouble of making a video which is confusing people in such a serious matter as BACKUPS. From BOL:
------------------------------------------------------------------------------------------------
COPY_ONLY
Specifies that the backup not affect the normal sequence of backups. A copy-only does not affect the overall backup and restore procedures for the database.
You can create a copy-only backup for any type of backup. The effect of the COPY_ONLY option varies with the general backup type, as follows:
A data backup taken with the COPY_ONLY option cannot be used as a base backup for differential backups. Differential backups taken later will behave as if the copy-only backup does not exist.
A differential backup is unaffected by the COPY_ONLY option.
A log backup taken using the COPY_ONLY option does not truncate the transaction log.
-------------------------------------------------------------------------------------------------
Is it that, its the differential backups, which are the ones affected by a one-off (out of place) backup & that could be avoided by using COPY_ONLY, I don't have time to implement it & check it right now. They certainly help with T-log as they don't truncate the log ( see above BOL defn).
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply