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 «««123

COPY_ONLY Backups Expand / Collapse
Author
Message
Posted Friday, October 2, 2009 1:41 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 1:17 AM
Points: 477, Visits: 1,903
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
Post #796773
Posted Friday, October 2, 2009 2:56 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 20, 2014 1:11 PM
Points: 27, Visits: 475
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
Post #797242
Posted Monday, October 12, 2009 9:33 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 10:16 AM
Points: 808, Visits: 1,993
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.


ATB

Charles Kincaid

Post #801659
Posted Thursday, October 22, 2009 12:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 15, 2010 7:32 AM
Points: 9, Visits: 16
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
Post #807377
Posted Thursday, October 22, 2009 1:21 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 10:16 AM
Points: 808, Visits: 1,993
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.

ATB

Charles Kincaid

Post #807408
Posted Thursday, October 22, 2009 2:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 15, 2010 7:32 AM
Points: 9, Visits: 16
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
Post #807460
Posted Thursday, October 22, 2009 3:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 15, 2010 7:32 AM
Points: 9, Visits: 16
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
Post #807471
Posted Thursday, October 22, 2009 3:46 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 10:16 AM
Points: 808, Visits: 1,993
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.


ATB

Charles Kincaid

Post #807483
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse