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 12»»

what is Copy-Only Backup? Expand / Collapse
Author
Message
Posted Thursday, August 5, 2010 11:09 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 14, 2010 6:06 AM
Points: 76, Visits: 199
Hi,

What is Copy-Only backup and how does it works?

thanks
Koteswarrao
Post #964523
Posted Thursday, August 5, 2010 11:28 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:19 AM
Points: 42,766, Visits: 35,864
Have you looked in Books Online? I'm pretty sure the answer is in there.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #964538
Posted Sunday, August 8, 2010 12:02 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:23 AM
Points: 6,287, Visits: 13,544
Hi

copy_only is a new feature, it is basically a backup that does not interfere with the current backup chain. As Gail said, BOL does document this feature


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #965677
Posted Sunday, August 8, 2010 11:34 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 15, 2014 5:01 PM
Points: 1,613, Visits: 1,539
It's not new though.



My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #965728
Posted Monday, August 9, 2010 8:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:23 AM
Points: 6,287, Visits: 13,544
let me re phrase that.

New in SQL Server 2005!


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #965998
Posted Tuesday, August 10, 2010 7:00 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 12:11 PM
Points: 4,343, Visits: 6,150
Perry Whittle (8/9/2010)
let me re phrase that.

New in SQL Server 2005!


I still refer to numerous 2005 features as "new". When you have been using the product since 6.5 first came out . . .

In any case, BOL is a WONDERFUL resource. I do SQL Server Relational Engine work every day and have for over a dozen years, and BOL is ALWAYS open on my desktop!!!


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #966650
Posted Tuesday, August 10, 2010 7:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 4, 2014 4:22 AM
Points: 28, Visits: 1,040
No normal master database entries are added to aid recovery but when I run

backup database thedatabase to disk='c:\thedatabase.bak' with copy_only

I still get a up to date last backup date in database properties in SQL Server Management Studio.

Useful but could be confusing...

J
Post #966654
Posted Tuesday, August 10, 2010 9:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:23 AM
Points: 6,287, Visits: 13,544
TheSQLGuru (8/10/2010)
I still refer to numerous 2005 features as "new". When you have been using the product since 6.5 first came out . . .

In any case, BOL is a WONDERFUL resource. I do SQL Server Relational Engine work every day and have for over a dozen years, and BOL is ALWAYS open on my desktop!!!


Hi Kevin

the only reason i referred to the option as new was because the OP has posted in the SQL Server 2005 forum and at this version it was a new option!

Regards



-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #966812
Posted Tuesday, August 10, 2010 5:18 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, May 29, 2011 9:01 AM
Points: 211, Visits: 4,106
hi Jason,

it will be in the backupset table.

however, the is_copy_only field will be 1, which confirms it is a copy_only backup.
Post #967071
Posted Wednesday, August 11, 2010 9:41 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 2:43 AM
Points: 34, Visits: 159
Copy-only backups are new to SQL Server 2005. They allow a backup of any type to be
taken without affecting any other backups. Normally, a database backup is recorded in the
database itself and is identified as part of a chain that can be used for restore. For
example, if a full database backup is taken, any subsequent differential database backups
use this full database backup as their base. A restore process utilizing the differential database
backups would have a reference to the full database backup, and that backup would
have to be available.
Copy-only backups do not affect the restore chain. They are useful in situations in which
you simply want to get a copy of the database for testing purposes or things of this
nature. Copy-only backups are not supported via SQL Server Management Studio (SSMS)
and must be performed via the Transact-SQL (T-SQL) BACKUP command.
Post #967921
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse