Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


COPY_ONLY Backups


COPY_ONLY Backups

Author
Message
Karl Klingler
Karl Klingler
SSC-Addicted
SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)

Group: General Forum Members
Points: 488 Visits: 2074
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
jswong05
jswong05
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 476
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
:-P
Charles Kincaid
Charles Kincaid
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1041 Visits: 2383
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
http://www.veloci-group.com/re...
http://www.veloci-group.com/remotebackup.html
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
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
Charles Kincaid
Charles Kincaid
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1041 Visits: 2383
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
http://www.veloci-group.com/re...
http://www.veloci-group.com/remotebackup.html
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
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
http://www.veloci-group.com/re...
http://www.veloci-group.com/remotebackup.html
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
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
Charles Kincaid
Charles Kincaid
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1041 Visits: 2383
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search