SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Charles Kincaid / COPY_ONLY Backups / Latest Posts

RE: COPY_ONLY Backups

Charles Kincaid — Thu, 22 Oct 2009 15:46:40 GMT

[quote][b]jens.joensson (10/22/2009)[/b][hr]If I setup a normal backup schedule with full backup in weekend, differential every day and log every hour through SQL server Management Studio (like the one I have for remotebackup) and point it to local disk. Then if I make a new backup to lets say a NAS on the network, then would that have to be a COPY_ONLY backup to not disturb the other full, differential and log backup ?:-) Jens[/quote]Yes. In my opinion I would do the NAS backup as copy only. I'm not sure about editing the plans. There is an alternative though. You can write a SQL script to do the backup and set up a command files (in the old DOS days we called them a "batch" file) to run that script using SQLCMD. Then you can use your favorite task scheduler to automate that.

RE: COPY_ONLY Backups

http://www.veloci-group.com/remotebackup.html — Thu, 22 Oct 2009 15:10:44 GMT

Is it by the way possible to edit my SQL Server Maintenance Plan Backup Database Task to include the COPY_ONLY option ? I can see the code (View T-SQL) when I choose to edit the Backup Database Task, but I'm not able to edit it...Thanks for your help, I'm a newbie to SQL Server trying to learn and understand :-):-) Jens

RE: COPY_ONLY Backups

http://www.veloci-group.com/remotebackup.html — Thu, 22 Oct 2009 14:49:19 GMT

[quote][b]Charles Kincaid (10/22/2009)[/b][hr]I can't speak about 3rd party backup tools. That's because i don't know how they all work. If you are going to take ad-hoc backups, even on a scheduled basis, then COPY_ONLY would reduce the risk of interfering with your other tools. Check with your backup vendor.[/quote]The [url=http://www.veloci-group.com/remotebackup.html]remotebackup[/url] is actually using the standard SQL backup to disk commands. Afterward it copies the files to external system.If I setup a normal backup schedule with full backup in weekend, differential every day and log every hour through SQL server Management Studio (like the one I have for remotebackup) and point it to local disk. Then if I make a new backup to lets say a NAS on the network, then would that have to be a COPY_ONLY backup to not disturb the other full, differential and log backup ?:-) Jens

RE: COPY_ONLY Backups

Charles Kincaid — Thu, 22 Oct 2009 13:21:25 GMT

I can't speak about 3rd party backup tools. That's because i don't know how they all work. If you are going to take ad-hoc backups, even on a scheduled basis, then COPY_ONLY would reduce the risk of interfering with your other tools. Check with your backup vendor.

RE: COPY_ONLY Backups

http://www.veloci-group.com/remotebackup.html — Thu, 22 Oct 2009 12:25:53 GMT

I'm using [url=http://www.veloci-group.com/remotebackup.html]remotebackup[/url] to backup both full and log. Every weekend a full backup of the database, every day differential and hourly log backup.We want to have a backup of the database in house on our NAS drive. Should we use the COPY_ONLY option ?Thanks in advance,:-) Jens

RE: COPY_ONLY Backups

Charles Kincaid — Mon, 12 Oct 2009 09:33:14 GMT

I just got notice of [url=http://www.sqlskills.com/BLOGS/PAUL/post/New-script-is-that-database-REALLY-in-the-FULL-recovery-mode.aspx]this article[/url] by Paul S Randal. It was articles like this and our own observations about log growth that triggered me to write this in the first place.What floored me is when the scripts presented in this thread actually [b][i][u]worked[/u][/i][/b]. So If you have a good full backup and all the log backups since that point you can get good recovery. If you take full backups all the time in full mode and do so frequently enough (your tolerance for data loss dictates frequency) you also can get good recovery. This leaves the log growth thing.Lastly once you decide that you need a COPY_ONLY backup in 2005 you will have trouble doing it with the GUI. This then shows you how to get round that.

RE: COPY_ONLY Backups

jswong05 — Fri, 02 Oct 2009 14:56:30 GMT

COPY_ONLY option can be used anytime and the file can be taken away or disappeared without affecting LSN chain. If you take a full backup on-demand without using COPY_ONLY option, you need to keep the file in the same place where it can be found with other backups. There are two tables msdb..backupmediafamilymsdb..backupsetthat record how backups were taken (assuming you can recover msdb).For 3rd party tools, if it breaks after a ad-hoc full backup is taken (quit taking log backup), you need to quit using that crapy tool. SSMS native maintenance plan or T-SQL code do just fine.If somebody took a backup and the file can not be found, and you are the one need to restore with that file, you are screwed. Update your resume.With full and diff backups, you can always take an ad-hoc diff backup, follow the same rule above to safe-keep the file.---------BOL----------------------COPY_ONLYSpecifies 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.

RE: COPY_ONLY Backups

Karl Klingler — Fri, 02 Oct 2009 01:41:21 GMT

Hi Charles,the log file growth in that scenario will happen if the dba thinks that a full backup will cut of the transaction log.There seem to be quit a lot of people out there who live under this misconception - perhaps some other database product works that way.regardskarl

RE: COPY_ONLY Backups

Charles Kincaid — Thu, 01 Oct 2009 18:48:34 GMT

[quote][b]timothyawiseman (10/1/2009)[/b][hr]Perhaps I am missing something. You should be able to restore it to 11PM on Thursday when the last log backup was taken, presuming of course you still have an unbroken chain of log backups back to the last full backup that you do have available. Although I normally used Red Gate SQL Backup as an intermediate tool, I have actually been through a scenario somewhat similar to this and had no problem effecting the restore.[/quote]You are right about the restores. I was lead astray on that aspect. Yet I have seen the log file growth thing happen many times.

RE: COPY_ONLY Backups

timothyawiseman — Thu, 01 Oct 2009 18:12:55 GMT

[quote][b]Charles Kincaid (9/30/2009)[/b][hr]Vliet is correct. So are many of the other points raised. Yet there was one thing missed. Yet we have seen the following many times:(1) Set up a database in full recovery.(2) Use it for a long time without [b][i][u]ANY[/u][/i][/b] back up at all.(3) Observe Log file size growth patterns during step 2.(4) Take a full backup(5) Continue to do 2 and 3 with no Log backup at all.(6) Report on how long it takes to (a) reach the maximum Log file size in express, or (b) fill up your drive.Then too look at the other articles elsewhere on COPY_ONLY. You will see the stories of people who do full backup on the weekend and Log backups daily. Wednesday some developer takes a full backup at 8AM and th automatic Log backup fires at 11PM. The developer took the backup file away, used it and deleted it. Friday at 3PM the RAID controller faulted. Once the RAID is back online you need a restore. Um. Why does this now fail to restore all the backups? Was there anything of importance done on Thursday? Let's hope not.Microsoft introduced this for a reason. Yes even they get it right at times. :) Even so you can tell that the engine folk and the tools folk don't talk to each other that much. Note that the 2005 SSMS does not know about COPY_ONLY. Supposed to be fixed in 2008.[/quote]Perhaps I am missing something. You should be able to restore it to 11PM on Thursday when the last log backup was taken, presuming of course you still have an unbroken chain of log backups back to the last full backup that you do have available. Although I normally used Red Gate SQL Backup as an intermediate tool, I have actually been through a scenario somewhat similar to this and had no problem effecting the restore.

RE: COPY_ONLY Backups

Charles Kincaid — Thu, 01 Oct 2009 10:18:33 GMT

Thanks Jeff.The next one will be better though. It's another learning experience. Thanks to all the responders too. I take no exception to anything that was said. I'll improve.

RE: COPY_ONLY Backups

Jeff Moden — Wed, 30 Sep 2009 17:57:34 GMT

Great introduction to Copy_Only, Charles. It's about time you wrote an article. ;-)

RE: COPY_ONLY Backups

douglas.dawson — Wed, 30 Sep 2009 15:18:14 GMT

I thought the article was a little shy on details regarding the option itself. I'm a developer that often has to put on a DBA hat, as well. I understood the two scripts and the lead up, but I didn't see where it was explained exactly what COPY_ONLY does. I read the article twice and it's not very clear to me what problem is or how it was solved with COPY_ONLY.In the comments, there were some examples and such that fleshed it out, but I think the article could use some of those details.Thanks for taking the time to write it, though!

RE: COPY_ONLY Backups

Divya Agrawal — Wed, 30 Sep 2009 13:37:05 GMT

Yes even i am confused on the post. It does not say anywhere in the entire article, what is the main purpose of Copy_only backups. It starts with the growth of log file, but what has Copy_only has to do with growth of log file.What i have discovered as the purpose of Copy_only is maintaining the archive point. It does not become a halt in our daily cycle of backup process. Say you have taken a Full backup and then a log backup, it takes the log backup and truncates the log. But, with Copy_only used while taking log backups, it does not truncate the logs. So, it does not hampers the daily backup cycle. If anyone asks for a backup in between, we can directly give the backup using Copy_only.

RE: COPY_ONLY Backups

David Walker-278941 — Wed, 30 Sep 2009 11:08:19 GMT

I know that all of this is (supposedly) documented more fully in BOL, but the purpose of these articles is to help clarify the topics they write about. This one makes it harder to understand. The article *mentions* Copy_Only backups but it doesn't give one whit of a clue, not a hint, about what a Copy_Only backup actually IS. Yes, I can read BOL, but an article like this ought to MENTION some broad overview of what a Copy_Only backup IS and what it's intended to do, and how it differs from the other backups.Copy_Only is mentioned in the middle of a discussion about the problems with log backups, but how are we supposed to guess what a Copy_Only backup is good for?

RE: COPY_ONLY Backups

Charles Kincaid — Wed, 30 Sep 2009 09:57:59 GMT

I just did Adi. Thank you. Very surprising. And here I was patting myself on the back for having all my research down. Then again one of the things that fully expected was to get my tail flamed. Articles are often good forum starters.

RE: COPY_ONLY Backups

Adi Cohn-120898 — Wed, 30 Sep 2009 09:45:36 GMT

[quote][b]Charles Kincaid (9/30/2009)[/b][hr]Then too look at the other articles elsewhere on COPY_ONLY. You will see the stories of people who do full backup on the weekend and Log backups daily. Wednesday some developer takes a full backup at 8AM and th automatic Log backup fires at 11PM. The developer took the backup file away, used it and deleted it. Friday at 3PM the RAID controller faulted. Once the RAID is back online you need a restore. Um. Why does this now fail to restore all the backups? Was there anything of importance done on Thursday? Let's hope not.[/quote]If I understood you correctly then you claim that the database can be restored to it’s state at Tuesday 11:00:00 PM (The last log backup that was taken before the developer did a full backup on the database). If this is the case then I’m sorry, but I disagree with you. In my opinion you’ll be able to restore this database to the state that it was at Thursday 11:00:00 PM (the last log backup that was taken in your example). Since you are talking about full and log backups, then as long as you have a full backup and all the log backups that were done since that full backup, you can restore the database to the same state that it was when the last log backup was taken regardless of the number of full backups that were taken since the full backup that you have . Try the run the script that I wrote earlier that shows it and see for yourself.Adi

RE: COPY_ONLY Backups

Charles Kincaid — Wed, 30 Sep 2009 09:31:11 GMT

David you are lucky. I've had that too. Mostly this is due to the databases having a high read to write ratio. This increases the probability that a fault at any given moment [b][i][u]won't[/u][/i][/b] have a pending transaction. Some of our customers get all the incoming orders between 6PM and 10PM local time. Order filling takes place between 11PM and 3AM. The rest of the time our database just sits there. I could walk in and power cycle the server at 10AM without much fear. That is why I said "risk". There is always a data loss risk. Full recovery is just one step in mitigating those risks.

RE: COPY_ONLY Backups

don_goodman — Wed, 30 Sep 2009 09:22:00 GMT

Typically, Microsoft, development DBAs and production DBAs overthink the backup process.The COPY_ONLY is a good idea for the development dba but not relevant to production. The idea of using the full recovery model, taking weekly backups then incrementing and doing hourlies defines a massively large recovery cycle that can cripple a business. So, I first define what the business needs then I define a recovery model around those needs.Here's a simple set of criteria for deciding on a backup approach for production. I only use maintenance plans for replication. Never for backups. Use scripts.1) Always set a simple recovery model unless the business need dictates otherwise. When setting up a new SQL Server instance, change the recovery model of the Model database to Simple.2) If you need to backup at different times of the day, do a full backup of the simple recovery model. Do fulls at different times. 3) avoid incrementals because a rapidly changing database will create a massively large backup file on the disk. If you must do an incremental, expect to consume twice or three times as much disk space.4) If the business defines a need for log backups the goto a full recovery model but:a) full backup dailyb) incremental backups to the same file at critical points in the day and as the last act of the dayc) log backups of each log file. I find naming them with sequence numbers based upon the hours of the day is very simple and clear.5) Test recovering the database in a test env. before every guaranteeing the business you can recover the database. By testing I mean restore it and the users, have a test copy of the affected apps connect to it and run through their functions.6) for Terabyte-sized databases, switch to a file backup if there aren't too many files. If there are too many files, resort to replication or log shipping to a remote location for a recovery path. If that is impossible, you are forced to use the full, incremental and log backup method. Set the business expectation that it will take up to a week to restore the database and that you need 2 terabytes of continguous disk space for every terabyte of database.Many will disagree but I manage 500 user databases on 100 servers with 40,000 users. I do DR on a regular basis with this method applied to time keeping and critical financial systems. So it doesn't really matter if Microsoft approves or everyone thinks this is the best way. What matters is the business. Get them up and running as fast and as successfully as you can and you will be a hero by doing your job. If you don't you will be looking for another job after failing to do this one.

RE: COPY_ONLY Backups

Charles Kincaid — Wed, 30 Sep 2009 09:18:03 GMT

Vliet is correct. So are many of the other points raised. Yet there was one thing missed. Yet we have seen the following many times:(1) Set up a database in full recovery.(2) Use it for a long time without [b][i][u]ANY[/u][/i][/b] back up at all.(3) Observe Log file size growth patterns during step 2.(4) Take a full backup(5) Continue to do 2 and 3 with no Log backup at all.(6) Report on how long it takes to (a) reach the maximum Log file size in express, or (b) fill up your drive.Then too look at the other articles elsewhere on COPY_ONLY. You will see the stories of people who do full backup on the weekend and Log backups daily. Wednesday some developer takes a full backup at 8AM and th automatic Log backup fires at 11PM. The developer took the backup file away, used it and deleted it. Friday at 3PM the RAID controller faulted. Once the RAID is back online you need a restore. Um. Why does this now fail to restore all the backups? Was there anything of importance done on Thursday? Let's hope not.Microsoft introduced this for a reason. Yes even they get it right at times. :) Even so you can tell that the engine folk and the tools folk don't talk to each other that much. Note that the 2005 SSMS does not know about COPY_ONLY. Supposed to be fixed in 2008.

RE: COPY_ONLY Backups

ccoker-1050064 — Wed, 30 Sep 2009 06:53:34 GMT

Thank you, thank you, thank you Charles. If only Microsoft's documentation could be as clearly written as your article.Curt

RE: COPY_ONLY Backups

David Bird — Wed, 30 Sep 2009 06:50:14 GMT

Good Article it presented an often-missed backup option that I was unaware off and will now remember in the future. Based on the article contents, follow up discussion, and books online, I've concluded COPY ONLY backups are principally used for creating database backup's that may or may not be accessible in the future, such as sending a backup to another company. This is extremely important for databases setup for FULL Recovery. Your statement "[i] The reactionary solution is to set databases to "Simple" recovery and accept the risk of database corruption. The reactionary solution is to set databases to "Simple" recovery and accept the risk of database corruption.[/i]" Confused me because I do not see how it risks corruption. It does break the chain of log backups, which enable a database to be restored to a point in time. So far "Knock on Wood" we have had few issues with database corruption. Most unexpected outages have started up without issue. Yes, there was one time we did have an issue but still did not loose any data. However, we mostly use “Full Recovery” to help in fixing any erroneous updates from an application or an individual who will remain nameless. :-D

RE: COPY_ONLY Backups

Adi Cohn-120898 — Wed, 30 Sep 2009 06:47:50 GMT

Vliet is correct. If you are using log backups in a restore operation, you can use a full backup that was taken with all the log backups that were taken since even if in between there was another full backup that was taken. Below is a small script that shows it:[code="sql"]--Creating the database for the democreate database DemoDB go--Make sure that the database is in full recovery modelalter database DemoDB set recovery fullgo--Making the first full backup. From now on I can use log backupsbackup database DemoDB to disk = 'c:\DemoDB_1.bak' with initgo--Creating a new object in the database use DemoDBgocreate table DemoTable (i int)go--backup the logbackup log DemoDB to disk = 'c:\DemoDB_1.trn' with initgo--Inserting a record that doesn't exist in all the backups that were taken into the table insert into DemoTable (i) values (1)go--Creating a second full backup. I won't be using this backup when I'll restore the databasebackup database DemoDB to disk = 'c:\DemoDB_2.bak' with initgoinsert into DemoTable (i) values (2)go--Creating the second log backup that includes the second record in DemoTablebackup log DemoDB to disk = 'c:\DemoDB_2.trn' with initgo--Dropping the table (to mimic an error)drop table DemoTablego--Now I'll restore the database. I'll use only the first full backup--with the 2 log backups. This proves that I don't need to use the --copy_only switch, if I have a full backup with all log backups that--were taken since then even if another full backup was taken between--the log backupsuse mastergorestore database DemoDB from disk = 'C:\DemoDB_1.bak' with norecovery, replacerestore log DemoDB from disk = 'c:\DemoDB_1.trn' with norecoveryrestore log DemoDB from disk = 'c:\DemoDB_2.trn' with recoverygoUse DemoDBgo--Notice that I have the table with 2 recordsselect * from DemoTablego--cleanupuse mastergodrop database DemoDB[/code]Adi

RE: COPY_ONLY Backups

Karl Klingler — Wed, 30 Sep 2009 05:10:57 GMT

Hi,log backups are independent from database backups.If you are using full and differential database backups, you usually store them in a certain place, where you will allways find any differential backup with ist correlating full backup.If you use copy_only for a database backup, you can store that backup at any other place, or even delete it - the chain of full and differential backups is not interrupted.regardsKarl

RE: COPY_ONLY Backups

Shark Energy — Wed, 30 Sep 2009 04:23:51 GMT

What is the issue of taking an ad hoc backup on the fly? Are you saying the problem is that the log backups then follow this backup? If so that shouldn't be an issue as long as you leave the ad hoc backup available for restore until your next full backup takes place....or am i missing the point?

RE: COPY_ONLY Backups

vliet — Wed, 30 Sep 2009 02:11:00 GMT

Hello Charles Kincaid,Some statements you made in your article are not entirely correct. The log backup chain does not start when you make the first database backup, it starts when you make the first log backup. The log starts to grow from the moment the database is created if you choose the full recovery model at creation time. The log also starts to grow the moment you switch from the simple recovery model to the full recovery model. The log file starts to grow as soon as the log does not fit in this file anymore, assuming that you did allow it to grow.You don't need the most recent full backup to restore a database that uses the full recovery model, any full backup will do, as long as you still have all log backups available that were made after that full backup. A full backup replaces the entire log backup chain up to the time this full backup was made, when it comes to restoring your database. By the way, the same is true for a COPY_ONLY backup, there is no difference between an 'ordinary' full backup and a COPY_ONLY backup in this respect.Then why do we need COPY_ONLY backups anyway? Because there is a third kind of backup that you did not mention in you article: the differential backup. A differential backup also replaces the log chain like the full backup, but when restoring a database, you always need the most recent full backup preceeding that differential backup to be able to use this differential backup. Things might go wrong if you take a full database backup outside the normal maintenance sequence. This full backup might not be available at the time of the (unplanned) restore and without you can not restore your differential backup you might rely on.Certain backup tools (like BackUpExec) will always take the possibility of an intermediate differential backup into account. They need to make a full backup themselves to guarantee the usefulness of a potential differential backup, even if you don't use them yet. So they stop making log backups as soon as you made a full backup yourself outside the tool, for example to create a duplicate of a database on another SQL server instance. That's not a wise thing to do, because the log backups are still very useful if there is at least one full backup made with the tool, but that's just the way it is.Using COPY_ONLY to avoid warning messages from these tools has nothing to do with the log backup chain, but everything with differential backups needing the most recent full backup. That makes your article still pretty relevant, because there are times you do need a COPY_ONLY backup, only for a different(ial) reason.

RE: COPY_ONLY Backups

LythamsFinest — Wed, 30 Sep 2009 01:02:59 GMT

Thanks for information.I use COPY_ONLY backups if I need to take an ad-hoc copy of a production database for urgent debugging in a development environment without interrupting the current backup sequence for the database. All of my current production databases employ weekly full backups, daily differential and half hourly transaction log backups so if I perform a random full backup without specifiying COPY_ONLY all of the files the server team have been copying off onto tape for me become out of date very quickly!If you like me have written any custom disaster recovery type backup/restore scripting then you might have discovered that there is a new column (2005 onwards) in the msdb.dbo.backupset table called [font="Courier New"]is_copy_only[/font]. This is set to a 1 when a backup is taken with the COPY_ONLY switch.

COPY_ONLY Backups

Charles Kincaid — Tue, 29 Sep 2009 22:33:57 GMT

Comments posted to this topic are about the item [B]COPY_ONLY Backups[/B]