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 Tuesday, September 29, 2009 10:33 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: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962
Comments posted to this topic are about the item COPY_ONLY Backups

ATB

Charles Kincaid

Post #795528
Posted Wednesday, September 30, 2009 1:02 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 4:38 AM
Points: 1,518, Visits: 873
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.

Post #795559
Posted Wednesday, September 30, 2009 2:11 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 5:34 AM
Points: 67, Visits: 423
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.
Post #795585
Posted Wednesday, September 30, 2009 4:23 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, May 19, 2014 2:44 AM
Points: 549, Visits: 1,016
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?
Post #795621
Posted Wednesday, September 30, 2009 5:10 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Monday, August 4, 2014 6:08 AM
Points: 477, Visits: 1,885
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
Post #795630
Posted Wednesday, September 30, 2009 6:47 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:20 AM
Points: 2,107, Visits: 5,405
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/
Post #795681
Posted Wednesday, September 30, 2009 6:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 9:32 AM
Points: 184, Visits: 1,009
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.


David Bird

My PC Quick Reference Guide
Post #795682
Posted Wednesday, September 30, 2009 6:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 19, 2010 3:23 AM
Points: 5, Visits: 31
Thank you, thank you, thank you Charles. If only Microsoft's documentation could be as clearly written as your article.

Curt
Post #795685
Posted Wednesday, September 30, 2009 9:18 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: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962
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.


ATB

Charles Kincaid

Post #795805
Posted Wednesday, September 30, 2009 9:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 6, 2010 1:30 PM
Points: 34, 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.
Post #795808
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse