SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


COPY_ONLY Backups


COPY_ONLY Backups

Author
Message
Charles Kincaid
Charles Kincaid
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3789 Visits: 2384
Comments posted to this topic are about the item COPY_ONLY Backups

ATBCharles Kincaid
LythamsFinest
LythamsFinest
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1616 Visits: 882
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 is_copy_only. This is set to a 1 when a backup is taken with the COPY_ONLY switch.
vliet
vliet
SSChasing Mays
SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)

Group: General Forum Members
Points: 612 Visits: 781
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.
Shark Energy
Shark Energy
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2319 Visits: 1018
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?
Karl Klingler
Karl Klingler
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1816 Visits: 2112
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.

regards
Karl

Best regards
karl
Adi Cohn
Adi Cohn
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10473 Visits: 6596
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:


--Creating the database for the demo
create database DemoDB
go

--Make sure that the database is in full recovery model
alter database DemoDB set recovery full
go

--Making the first full backup. From now on I can use log backups
backup database DemoDB to disk = 'c:\DemoDB_1.bak' with init
go

--Creating a new object in the database
use DemoDB
go
create table DemoTable (i int)
go

--backup the log
backup log DemoDB to disk = 'c:\DemoDB_1.trn' with init
go

--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 database
backup database DemoDB to disk = 'c:\DemoDB_2.bak' with init
go

insert into DemoTable (i) values (2)
go

--Creating the second log backup that includes the second record in DemoTable
backup log DemoDB to disk = 'c:\DemoDB_2.trn' with init
go

--Dropping the table (to mimic an error)
drop table DemoTable
go

--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 backups
use master
go
restore database DemoDB from disk = 'C:\DemoDB_1.bak' with norecovery, replace
restore log DemoDB from disk = 'c:\DemoDB_1.trn' with norecovery
restore log DemoDB from disk = 'c:\DemoDB_2.trn' with recovery
go

Use DemoDB
go
--Notice that I have the table with 2 records
select * from DemoTable
go


--cleanup
use master
go
drop database DemoDB




Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
David Bird
David Bird
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1360 Visits: 1244
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 " 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." 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

David Bird
ccoker-1050064
ccoker-1050064
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 31
Thank you, thank you, thank you Charles. If only Microsoft's documentation could be as clearly written as your article.

Curt
Charles Kincaid
Charles Kincaid
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3789 Visits: 2384
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. Smile 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.

ATBCharles Kincaid
don_goodman
don_goodman
SSC-Enthusiastic
SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)

Group: General Forum Members
Points: 176 Visits: 82
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 daily
b) incremental backups to the same file at critical points in the day and as the last act of the day
c) 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.
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