September 29, 2009 at 10:33 pm
Comments posted to this topic are about the item COPY_ONLY Backups
ATBCharles Kincaid
September 30, 2009 at 1:02 am
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 [font="Courier New"]is_copy_only[/font]. This is set to a 1 when a backup is taken with the COPY_ONLY switch.
September 30, 2009 at 2:11 am
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.
September 30, 2009 at 4:23 am
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?
September 30, 2009 at 5:10 am
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
September 30, 2009 at 6:47 am
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/
September 30, 2009 at 6:50 am
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
September 30, 2009 at 6:53 am
Thank you, thank you, thank you Charles. If only Microsoft's documentation could be as clearly written as your article.
Curt
September 30, 2009 at 9:18 am
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.
ATBCharles Kincaid
September 30, 2009 at 9:22 am
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.
September 30, 2009 at 9:31 am
David you are lucky. I've had that too. Mostly this is due to the databases having a high read to write ratio. This increases the probability that a fault at any given moment won't have a pending transaction. Some of our customers get all the incoming orders between 6PM and 10PM local time. Order filling takes place between 11PM and 3AM. The rest of the time our database just sits there. I could walk in and power cycle the server at 10AM without much fear.
That is why I said "risk". There is always a data loss risk. Full recovery is just one step in mitigating those risks.
ATBCharles Kincaid
September 30, 2009 at 9:45 am
Charles Kincaid (9/30/2009)
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.
If I understood you correctly then you claim that the database can be restored to it’s state at Tuesday 11:00:00 PM (The last log backup that was taken before the developer did a full backup on the database). If this is the case then I’m sorry, but I disagree with you. In my opinion you’ll be able to restore this database to the state that it was at Thursday 11:00:00 PM (the last log backup that was taken in your example). Since you are talking about full and log backups, then as long as you have a full backup and all the log backups that were done since that full backup, you can restore the database to the same state that it was when the last log backup was taken regardless of the number of full backups that were taken since the full backup that you have . Try the run the script that I wrote earlier that shows it and see for yourself.
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/
September 30, 2009 at 9:58 am
I just did Adi. Thank you. Very surprising. And here I was patting myself on the back for having all my research down. Then again one of the things that fully expected was to get my tail flamed. Articles are often good forum starters.
ATBCharles Kincaid
September 30, 2009 at 11:08 am
I know that all of this is (supposedly) documented more fully in BOL, but the purpose of these articles is to help clarify the topics they write about. This one makes it harder to understand. The article *mentions* Copy_Only backups but it doesn't give one whit of a clue, not a hint, about what a Copy_Only backup actually IS. Yes, I can read BOL, but an article like this ought to MENTION some broad overview of what a Copy_Only backup IS and what it's intended to do, and how it differs from the other backups.
Copy_Only is mentioned in the middle of a discussion about the problems with log backups, but how are we supposed to guess what a Copy_Only backup is good for?
September 30, 2009 at 1:37 pm
Yes even i am confused on the post. It does not say anywhere in the entire article, what is the main purpose of Copy_only backups. It starts with the growth of log file, but what has Copy_only has to do with growth of log file.
What i have discovered as the purpose of Copy_only is maintaining the archive point. It does not become a halt in our daily cycle of backup process. Say you have taken a Full backup and then a log backup, it takes the log backup and truncates the log. But, with Copy_only used while taking log backups, it does not truncate the logs. So, it does not hampers the daily backup cycle. If anyone asks for a backup in between, we can directly give the backup using Copy_only.
--Divya
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply