<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Charles Kincaid
»
COPY_ONLY Backups
28 posts, Page 3 of 3
««
«
1
2
3
COPY_ONLY Backups
Rate Topic
Display Mode
Topic Options
Author
Message
Karl Klingler
Karl Klingler
Posted Friday, October 02, 2009 1:41 AM
SSC-Addicted
Group: General Forum Members
Last Login: Yesterday @ 8:55 AM
Points: 475,
Visits: 1,677
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
jswong05
jswong05
Posted Friday, October 02, 2009 2:56 PM
SSC Rookie
Group: General Forum Members
Last Login: Monday, March 18, 2013 7:54 AM
Points: 27,
Visits: 421
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
Charles Kincaid
Charles Kincaid
Posted Monday, October 12, 2009 9:33 AM
Right there with Babe
Group: General Forum Members
Last Login: Friday, May 17, 2013 9:18 AM
Points: 772,
Visits: 1,825
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
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
Charles Kincaid
Charles Kincaid
Posted Thursday, October 22, 2009 1:21 PM
Right there with Babe
Group: General Forum Members
Last Login: Friday, May 17, 2013 9:18 AM
Points: 772,
Visits: 1,825
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
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
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
Charles Kincaid
Charles Kincaid
Posted Thursday, October 22, 2009 3:46 PM
Right there with Babe
Group: General Forum Members
Last Login: Friday, May 17, 2013 9:18 AM
Points: 772,
Visits: 1,825
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 »
28 posts, Page 3 of 3
««
«
1
2
3
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
