November 25, 2011 at 3:20 am
Hi,
We regularly restore databases from our production environment to the development/test environments. So we don't mess up the backup chain, I take COPY_ONLY backups. The backup runs fine and completes successfully.
Now, when I take the backup file and try and restore it to the dev/test environment, when using the GUI, I select the backup file to restore, but nothing appears in the list of backup sets.
If I don't use the COPY_ONLY option, it works fine.
This is the backup command used:
BACKUP DATABASE [ht] TO DISK = N's:\ht.bak' WITH NOFORMAT, INIT, NAME = N'ht', SKIP, NOREWIND, NOUNLOAD, COPY_ONLY, STATS = 10
I've tried this 4 times now. Same results.
Restoring the database using tsql works fine.
I'm running identical version of SQL Server in all environments. 9.00.4035
Thanks, Andrew
November 25, 2011 at 3:33 am
use the 'from device' radio button and navigate to the location of your backup to select the backup you want to restore.
Note just in case - you only need to use the copy_only clause if you are also doing differential backups, a full backup will not break your log backup chain.
---------------------------------------------------------------------
November 25, 2011 at 3:38 am
george sibbald (11/25/2011)
use the 'from device' radio button and navigate to the location of your backup to select the backup you want to restore.Note just in case - you only need to use the copy_only clause if you are also doing differential backups, a full backup will not break your log backup chain.
I am using the 'from device' radio button. I can browse to and select the backup file fine, it's just nothing appears in the backup set list.
I take the copy_only backup because I don't want my 15 min trans log backups to be based on my adh-hoc support full backup...
thanks,
November 25, 2011 at 3:40 am
It's a known bug with copy-only backups. The GUI doesn't work, so you have to restore with T-SQL. It's a management studio bug.
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
November 25, 2011 at 3:41 am
cool - that's the answer I was looking for. Cheers,
November 25, 2011 at 3:43 am
adb2303 (11/25/2011)
I take the copy_only backup because I don't want my 15 min trans log backups to be based on my adh-hoc support full backup...
They won't be, whether you use copy_only or not. As George said, Copy_only only affects what full backup the differentials are based off, not the log backups. Full backups never truncate the transaction log
http://sqlinthewild.co.za/index.php/2011/03/08/full-backups-the-log-chain-and-the-copy_only-option/
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
November 25, 2011 at 3:46 am
I take the copy_only backup because I don't want my 15 min trans log backups to be based on my adh-hoc support full backup...
thanks,
why post and ignore the answers?
copy_only does not affect log backups.
---------------------------------------------------------------------
November 25, 2011 at 3:59 am
I don't get it. Maybe my brain's given up with it being Friday.
We take a regular full database backup every day at 10pm. Throughout the day, we take 15 minute transaction log backups.
Occassionally, we get a request to backup a production database and restore it to a test environment. We use COPY_ONLY on these ad-hoc backups so that we don't disrupt the full backup/log backup chain. I.e. if we had to restore in live, we'd want to use the 10pm full backup and subsequent 15 min trans log backup files. Surely if I didn't use COPY_ONLY, my 15min trans log backups taken after the ad-hoc full backup would then be based on the ad-hoc full backup? Also, the trans log backups taken prior to the full ad-hoc backup would become redundant.
If not, then I need to hit the books again...
November 25, 2011 at 4:04 am
i'm not ignoring the answers. I specifically take copy_only backups because I don't want to interfere with log backups! I think there's some wires crossed here.
November 25, 2011 at 4:07 am
hit the books.
A full backup DOES NOT truncate the transaction log, therefore you can restore a log through a full backup.
i.e.
full backup 1
log backup 1
log backup 2
full backup 2
log backup 3
You can get to the end of log backup 3 by restoring
full backup 1
log backup 1
log backup 2
log backup 3
full backup 2 is not needed. This is because a full backup does not remove the inactive part of the log, all it does it place a marker saying it has occurred, thereby maintaining the integrity of the log chain.
create a test database and try it.
---------------------------------------------------------------------
November 25, 2011 at 4:16 am
adb2303 (11/25/2011)
i'm not ignoring the answers. I specifically take copy_only backups because I don't want to interfere with log backups! I think there's some wires crossed here.
I said you don't need copy_only as it doesn't affect log backups, you replied thats why you are taking the copy_only backups!
---------------------------------------------------------------------
November 25, 2011 at 4:22 am
I'd believed that the log backups ran from the point the last full backup was taken... that was why I (incorrectly) assumed taking the copy_only backups was the way to avoid interfering with the backup chain. Apologies for my incorrect assumptions and thanks for clearing it up
November 25, 2011 at 4:51 am
no need to apologise for incorrect assumptions, I just felt you weren't taking the information on-board.
---------------------------------------------------------------------
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply