January 20, 2016 at 2:35 am
I wonder if anyone can help or if they have experienced a similar issue before....
As part of my maintenance plan I carry out a full native backup every evening at around 9pm. At around 1am I also have a RedGate backup that runs. The RedGate backup is set to "Copy Only" so that it doesn't reset the log chain.
When I try to do a point in time restore via the SSMS GUI, under the 'Backup sets to restore' the only full backup I can see is the RedGate backup followed by any transaction log backups taken after that time. Because the RedGate backup is being referenced, when I verify backup media it fails because the RedGate backup is in *.sql format. In the GUI this backup type is set to Full (Copy Only) so my question is why is SSMS referencing this backup rather than the native one taken at 9pm? Rather than changing my maintenance schedule to have the native backup run after the RedGate one I was wondering if anyone had experience this issue before?
January 20, 2016 at 3:00 am
COPY_ONLY will only affect differential backups, not log backups. Log backups can be associated with any full backup, even ones that have been taken using COPY_ONLY. It's only when you run log backups with copy_only that you have to be concerned with the log backup chain. You can read more at MSDN.
Now, as to why the SSMS GUI is putting the other backup first, it's probably just the latest. You still should be able to restore from the other backup and then go forward with log restores. If the GUI doesn't work, go to scripts. I prefer scripting anyway.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 20, 2016 at 3:16 am
Thanks! I think scripting is the way forward to be honest.
January 20, 2016 at 8:33 am
I would say to NEVER EVER use the SSMS GUI for such things (or creating/modifying objects or many other "DDL" type things - too many bugs and poor design choices)!!
You CAN use the GUI to set up something but then use the Script icon to generate a script to a new window or file. This allows you to review/fix the issues and/or get a good starting point for your scripted needs.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 24, 2016 at 5:21 pm
It appears that the SSMS reads the redgate file even though it should read the native as it is the full backup. As you stated, changing the backup timing might be the way to go.
I too recommend using script for such scenarios.
also a lot of times I use restore from files and file groups when I deal with broken LSN chain. I start with the native in unrecovery mode, then use restore from files for rest of files until you recover to a point in time.
http://www.sanssql.com/2013/08/unable-to-create-restore-plan-due-to.html
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply