September 7, 2009 at 2:37 am
Hi i have heard that we cant restore copy only back up.because it does not make change in LSN.but we are using this command and restore is going successfully please find the error or is this method is right.
backup database AdventureWorks
to disk='e:\AdventureWorks.bak'
with copy_only
restore database replica
from disk ='e:\adventureWorks.bak'
with file =1,
move 'AdventureWorks_Data' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\replica.mdf',
move 'AdventureWorks_Log' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\replica_log.ldf',
replace
September 7, 2009 at 3:11 am
anshu84onnet (9/7/2009)
Hi i have heard that we cant restore copy only back up.because it does not make change in LSN.but we are using this command and restore is going successfully please find the error or is this method is right.backup database AdventureWorks
to disk='e:\AdventureWorks.bak'
with copy_only
restore database replica
from disk ='e:\adventureWorks.bak'
with file =1,
move 'AdventureWorks_Data' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\replica.mdf',
move 'AdventureWorks_Log' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\replica_log.ldf',
replace
I have just run your commands and they backup and restore perfectly, I use copy only backups for adhoc backups for change requests and refreshes.
BOL mentions :
A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. Usually, taking a backup changes the database and affects how later backups are restored. However, occasionally, it is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database. For this purpose, copy-only backups were introduced SQL Server 2005.
Copy-only full backups (all recovery models)
A copy-only full backup cannot serve as a differential base or differential backup and does not affect the differential base.
Copy-only log backups (full recovery model and bulk-logged recovery model only)
A copy-only log backup preserves the existing log archive point and, therefore, does not affect the sequencing of regular log backups. Copy-only log backups are typically unnecessary. Instead, you can create another routine, current log backup (using WITH NORECOVERY), and then use that backup together with all other previous log backups that are required for the restore sequence. However, a copy-only log backup can be created for performing an online restore.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 13, 2009 at 5:17 pm
A copy only backup can be restored.
It could be used though in situations where you want to take a full database backup to restore somewhere else but have diff backups running as part of the daily houskeeping routines.
As it doesn't reset the base point at which a diff backup refers to it can be deleted once it is no longer required without having to archive it to tape.
September 14, 2009 at 4:34 am
From BOL:
Restoring a copy-only full backup is the same as restoring any full backup.
"Keep Trying"
September 14, 2009 at 5:31 am
You're both correct, but I prefer CC's answer.
The full reference starts by saying:
Books Online
A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. Usually, taking a backup changes the database and affects how later backups are restored. However, occasionally, it is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database. For this purpose, copy-only backups were introduced SQL Server 2005
That is an important point, as CC emphasized.
That said, it is also true that the act of restoring a copy-only full backup is identical to a normal full backup, as far as the restore operation is concerned.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 14, 2009 at 6:23 am
This is a good point. A new learning for me. Thanks 🙂
September 14, 2009 at 6:23 am
.:-)
August 10, 2012 at 6:40 am
Well I have another question about copy only backups. Clearly the purpose is to be able to do a backup without interferring the normal backup sequence but it looks like the GUI in SSMS can't handle it when it comes to restore. Here's the scenario :
Full backup
Differential backup
Copy only backup
I move the copy only the backup to my laptop for a later restore.
Then using the GUI in SSMS to restore it suggest to use the copy only backup to restore. This will obviously fail in my scenario. After doing another differential backup the GUI correctly list the Full backup and the last differential as the sequence. But is this expected behaviour ?
June 22, 2014 at 10:34 pm
Hi All,
Is it possible to restore a copy only backup followed by an differential backup??
Regards,
k7.
December 23, 2017 at 5:07 am
bobby henningsen - Friday, August 10, 2012 6:40 AMWell I have another question about copy only backups. Clearly the purpose is to be able to do a backup without interferring the normal backup sequence but it looks like the GUI in SSMS can't handle it when it comes to restore. Here's the scenario :Full backupDifferential backupCopy only backupI move the copy only the backup to my laptop for a later restore.Then using the GUI in SSMS to restore it suggest to use the copy only backup to restore. This will obviously fail in my scenario. After doing another differential backup the GUI correctly list the Full backup and the last differential as the sequence. But is this expected behaviour ?
Although the quoted post is over 5 years old, it is still applicable nowadays. Googling this issue brings almost no results. We run COPY_ONLY backup on daily basis on production system and move it off from production to QA system. Once restored on QA, it is discarded. The great workaround is to schedule COPY_ONLY backup just before the full/differential backup scheduled for the day.
It is stupid SSMS cannot handle this situation better.
December 23, 2017 at 6:16 am
Been using copy-only for 10 years without issue. They are good for having a point in time backup without affecting the normal restore schedule, so as to have a backup to restore to Dev, also useful when third party suppliers ask for a copy for upgrade testing or support purposes; assuming a support contract exists.
...
December 23, 2017 at 6:27 am
HappyGeek - Saturday, December 23, 2017 6:16 AMBeen using copy-only for 10 years without issue. They are good for having a point in time backup without affecting the normal restore schedule, so as to have a backup to restore to Dev, also useful when third party suppliers ask for a copy for upgrade testing or support purposes; assuming a support contract exists.
I fully agree, same here. The issue is the SSMS behavior described by "bobby henningsen" five years ago.
December 23, 2017 at 12:16 pm
goodmirek - Saturday, December 23, 2017 5:07 AM[...]The great workaround is to schedule COPY_ONLY backup just before the full/differential backup scheduled for the day.
It is stupid SSMS cannot handle this situation better.
The great workaround is to use that backup you're taking anyway, and not take a separate COPY_ONLY backup.
There appears to be a lot of confusion as to what a COPY_ONLY backup is, and what it provides.
When used with a full backup, the COPY_ONLY switch prevents resetting the differential bits, meaning differential backups taken after a COPY_ONLY full backup still apply to the same backup they did before the COPY_ONLY backup was taken. If you're not using differential backups, then COPY_ONLY does nothing for you at all.
If you need to restore somewhere, and you have a full backup and t-logs, use those. Don't take an additional backup.
All of this information is clearly detailed , with examples, in the short BoL articles for BACKUP DATABASE and RESTORE DATABASE.
Fully understanding those two articles is critical to protecting your data.
Eddie Wuerch
MCM: SQL
December 23, 2017 at 12:28 pm
Eddie Wuerch - Saturday, December 23, 2017 12:16 PMThe great workaround is to use that backup you're taking anyway, and not take a separate COPY_ONLY backup.There appears to be a lot of confusion as to what a COPY_ONLY backup is, and what it provides.
When used with a full backup, the COPY_ONLY switch prevents resetting the differential bits, meaning differential backups taken after a COPY_ONLY full backup still apply to the same backup they did before the COPY_ONLY backup was taken. If you're not using differential backups, then COPY_ONLY does nothing for you at all.If you need to restore somewhere, and you have a full backup and t-logs, use those. Don't take an additional backup.
All of this information is clearly detailed , with examples, in the short BoL articles for BACKUP DATABASE and RESTORE DATABASE.
Fully understanding those two articles is critical to protecting your data.
I do use differential backups.
In my use case, I need to restore somewhere an adhoc COPY_ONLY backup on demand. It is not an option to restore full backup + differential backup + twenty or so transaction log backups.
I believe I do understand COPY_ONLY behavior in detail. What I do not like is behavior of SSMS, which I had to workaround.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply