September 30, 2009 at 3:18 pm
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!
September 30, 2009 at 5:57 pm
Great introduction to Copy_Only, Charles. It's about time you wrote an article. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
October 1, 2009 at 10:18 am
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.
ATBCharles Kincaid
October 1, 2009 at 6:12 pm
Charles Kincaid (9/30/2009)
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 ANY 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.
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.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
October 1, 2009 at 6:48 pm
timothyawiseman (10/1/2009)
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.
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.
ATBCharles Kincaid
October 2, 2009 at 1:41 am
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.
regards
karl
Best regards
karl
October 2, 2009 at 2:56 pm
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..backupmediafamily
msdb..backupset
that 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_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.
Jason
http://dbace.us
😛
October 12, 2009 at 9:33 am
I just got notice of this article 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 worked. 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.
ATBCharles Kincaid
October 22, 2009 at 12:25 pm
I'm using remotebackup 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
October 22, 2009 at 1:21 pm
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.
ATBCharles Kincaid
October 22, 2009 at 2:49 pm
Charles Kincaid (10/22/2009)
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.
The remotebackup 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
October 22, 2009 at 3:10 pm
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
October 22, 2009 at 3:46 pm
jens.joensson (10/22/2009)
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
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.
ATBCharles Kincaid
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply