August 5, 2010 at 11:09 am
Hi,
What is Copy-Only backup and how does it works?
thanks
Koteswarrao
August 5, 2010 at 11:28 am
Have you looked in Books Online? I'm pretty sure the answer is in there.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 8, 2010 at 12:02 pm
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" 😉
August 8, 2010 at 11:34 pm
It's not new though.
August 9, 2010 at 8:38 am
let me re phrase that.
New in SQL Server 2005!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 10, 2010 at 7:00 am
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 on googles mail service
August 10, 2010 at 7:05 am
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
August 10, 2010 at 9:49 am
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" 😉
August 10, 2010 at 5:18 pm
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.
August 11, 2010 at 9:41 pm
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.
August 11, 2010 at 9:54 pm
eng_mgomaa (8/11/2010)
Copy-only backups are new to SQL Server 2005. They allow a backup of any type to betaken 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.
you can only take a full or a log backup with copy_only. Differential backup will ignore the copy_only.
August 12, 2010 at 12:22 am
Hi,
when you take copy only backup. The Log Sequence number doesn't change it. and
i think a copy only backup can take upto 4
Regards
Balaji.G
August 15, 2012 at 2:13 am
Thank you Rookie. That certainly helped me understand why the Copy-only backups are used. In fact the description given in MSDN was not clear to know why the Copy-only backups are used.:-)
December 12, 2013 at 12:31 pm
eng_mgomaa (8/11/2010)
Copy-only backups are not supported via SQL Server Management Studio (SSMS)and must be performed via the Transact-SQL (T-SQL) BACKUP command.
I dont think so.. I have a check box with copy only in SSMS here.. Its 2008 though. I donno what version u r using
--Pra:-):-)--------------------------------------------------------------------------------
December 12, 2013 at 2:03 pm
Please note: 3 year old thread.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply