what is Copy-Only Backup?
-
Hi,
What is Copy-Only backup and how does it works?
thanks
Koteswarrao
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
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"
-
It's not new though.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
let me re phrase that.
New in SQL Server 2005!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs"
-
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 -
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
-
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"
-
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.
-
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.
-
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.
-
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
-
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.:-)
-
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:-):-)--------------------------------------------------------------------------------
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply