June 5, 2015 at 4:30 am
Hi!
I have some issues with our backup plan since we implemented a new business application that requires a FULL backup every night of our Production DB and restores it automatically to our a database in our Test environment. (Let's call it DB1) This Backup/Restore procedure is builtin in these specific DB's (implemented by the application developers) and are independent of our normal backup plans.
Our normal backup plan that we use on all our databases (including the one mentioned above) looks like this:
FULL backup once a week
DIFF backup every night
TRANS-LOG every hour
I thought that by running DB1's nightly FULL backup in COPY-ONLY mode, it wouldn't interfere with our normal backup plans restore sequence.
Been googling about this alot and got the impression that this is the main purpose of the COPY-ONLY option. Unfortunately not!
If I try to restore DB1 through Management Studio in the morning, I'm only able to restore it from the COPY-ONLY Fullbackup that run that night and a few Trans logs that's been backed up since that. (My weekly FULL, the DIFFs and other Trans logs are nowhere to be seen) It completely "resets" my backup set every night because of this and that's unacceptable. I need to be able to restore DB1 on a "hour to hour" basis, at least 3 days back in time.
Have I completely misunderstood what the "Copy-Only" option does, or am I doing something terribly wrong here? I'm far from a SQL Guru but thought I'd at least I got a hang of how SQL Backup/Restore works, but obviously not 🙁
Some help would be very appreciated
//Chrille
June 5, 2015 at 4:45 am
Chrille (6/5/2015)
I thought that by running DB1's nightly FULL backup in COPY-ONLY mode, it wouldn't interfere with our normal backup plans restore sequence.
Correct, it doesn't.
Well, more specifically it doesn't interfere with differential backups and the restore thereof, as full backups never interfere with log backups.
If I try to restore DB1 through Management Studio in the morning, I'm only able to restore it from the COPY-ONLY Fullbackup that run that night and a few Trans logs that's been backed up since that. (My weekly FULL, the DIFFs and other Trans logs are nowhere to be seen) It completely "resets" my backup set every night because of this and that's unacceptable. I need to be able to restore DB1 on a "hour to hour" basis, at least 3 days back in time.
The GUI, however, is stupid.
With full backups weekly, diffs daily and log backups every hour, your restore path is the latest full backup (the weekly ones, not the copy only), the latest differential and all log backups since the diff.
Restore your full WITH NORECOVERY, restore your DIFF WITH NORECOVERY, restore the log backups since the diff all WITH NORECOVERY and then once you're sure you've restored all log backups, recover the database (RESTORE DATABASE <Database name> WITH RECOVERY)
If you're doing it regularly it shouldn't be too hard to write up some code that will locate and restore all the needed backups.
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
June 5, 2015 at 4:45 am
I think I know what you mean... then again I am probably reading your email all wrong.
If you are using the GUI (which I hate) to restore I think it picks up whatever the most recent backup is. I suggest you click on the specific date.time button - see screenshot. Or even better write the TSQL.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply