August 13, 2008 at 5:27 am
I have setup our SQL Cluster to perform Full Backups every day with T-Log backups every 2 hours, which works fine. However, we also have Netbackup backing up the same DBs
(The reason for using both is we keep a couple of days backup on disk from maintenance plan for a quick recovery, and netbackup to tape for offsite storage)
When trying to restore from disk the DB and Tlog it works fine with the Full backup and first Tlog, but gives an error implying that there should be an earlier tlog to restore, would this be a tlog from Netbackup? if so is there a way to allow me to have the maintence plans backup the files as well as Netbackup?
thanks
August 14, 2008 at 3:03 pm
You need to restore from the latest full backup, the transactional backups one by one.
August 19, 2008 at 8:12 am
Something like this in T-SQL.
BACKUP LOG ULTIPRO_ACS
TO DISK = 'path\dbname.trn'
WITH NO_TRUNCATE;
GO
RESTORE DATABASE ULTIPRO_ACS
FROM DISK = 'path\yourlastdbbackup_200808192301.bak'
WITH NORECOVERY;
GO
RESTORE LOG ULTIPRO_ACS
FROM DISK = 'path\dbname_2008008190301.trn'
WITH NORECOVERY;
GO
RESTORE LOG ULTIPRO_ACS
FROM DISK = 'path\dbname_2008008190316.trn'
WITH NORECOVERY;
GO
RESTORE LOG ULTIPRO_ACS
FROM DISK = 'path\dbname_200808190331.trn'
WITH NORECOVERY;
GO
....... continued until the log time is equal to your last db backup.
August 19, 2008 at 8:28 am
I can get that far but, i get the errror when restoring the 2nd transaction log. I thin k i have found out why,
The netbackup backups where running as well as the mainteneace plan ones which meant the transaction log sequence was wrong.
ie. the full backup was from MPlan,
1st tlog from mPlan
2nd Tlog from netbackup
etc,etc
resolution was to run the netbackup backups with the COPYONLY command, so it did not write the backup events to the MSDB DB.
August 20, 2008 at 8:22 am
To avoid this situation, I always backup to disk (full and log) and then copy those disk backups to tape on a daily basis. Then there is no need to worry about the backup chain being broken and recovery is much easier.
August 20, 2008 at 8:31 am
Andrew Collins (8/19/2008)
I can get that far but, i get the errror when restoring the 2nd transaction log. I thin k i have found out why,The netbackup backups where running as well as the mainteneace plan ones which meant the transaction log sequence was wrong.
ie. the full backup was from MPlan,
1st tlog from mPlan
2nd Tlog from netbackup
etc,etc
resolution was to run the netbackup backups with the COPYONLY command, so it did not write the backup events to the MSDB DB.
I run full and diffs with netbackup and logs using native SQL. If I need a certain point in time, i can use the netbackup full/diffs and replay any logs on top of that. The timing has to be coordinated, which SUCKS, but it's what I have to deal with at my new employer. Be sure to test out recovery of any scenario that you choose to go with. It can/will be a headache but with proper planning and scheduling, you can get it to work for you and your business.
-- You can't be late until you show up.
August 20, 2008 at 12:40 pm
we make all backups with netbackup
full, diff and t-log and all works fine also the restores
but we have virtual tapes (disks) to buffer the streams and if there is time left the streams are moved to tape.
the mssql module for netbackup is really nice.
bye
*M*
August 21, 2008 at 9:00 am
It is always best if you only have one system doing the backups. Otherwise you will always run into issues with which order to restore in. I would suggest you use your Maint. Plan backups for backing up SQL and then just have NetBackup backup the files created from the Maint. Plan. That way you still have a certain number on disk and all of them going to tape.
We actually experienced several issues with having NetBackup and SQL Native backups running. Our NetBackup solution did not have a set time of when it would backup a server. It depended on when the tapes device was free. Because of this, we actually had times that both backups tried running at the same time and would bring the server to its knees.
September 25, 2008 at 4:55 am
We have a similar setup to Andrew using a combination of Backup Exec and the Maintenance plans, and it was only the other day when thinking through our setup I realised this particular gotcha waiting to bite! (I've been lucky thus far!)
The problem with just using one or other method is each of them has specific benefits, so it would be a shame to have to pick one over the other. In particular, when you have a large number of db's on a server I'd much rather have a Backup Exec backup available in the case of a DR restore being required, since you can do a single restore to recover all of them, rather than having to restore each individual databases one by one. On the other hand, the most common type of restore I tend to handle involves problems between keyboard and chair, most notably developers, where it's a single database which has been screwed up and needs to be restored. For this, having the local SQL backup is way faster and easier than using Backup Exec.
After looking through the options and best way to keep both systems running without them tripping up over each other I'm looking at the current resolution :
Backup Exec - Use a combination of Full backups (1 per week) and Differential Backups (6 per week), with no transaction log backups being run.
SQL Server - Use a combination of Full backups (daily) and Transaction Log backups (at least daily).
My thinking is that since the Full and Diff backups don't touch the transaction log they won't cause any problems with the SQL Server backups, while still providing daily backups, and ensuring the transaction log is backed up, and old transactions removed. Since we currently only do transaction log backups once per day anyway, losing the ability to do point in time restores direct from Backup Exec wouldn't be much of an issue. Additionally, in a DR situation Backup Exec would have a backup of the filesystem backup files anyway, so if we really needed to we could still do a point in time restore for a specific database using the SQL Server backup files, once Backup Exec had restored the base SQL Server install with it's databases.
Now the question is, can anyone see any glaring flaws in this plan / my logic?
September 25, 2008 at 7:00 am
The real question is - what do your business needs dictate (the old "it depends" answer)? Full backups and diffs provide protection but the logs and point-in-time restores will prove essential, based on business need. Are you covered, based on SLAs and such? If so, you have the proper protection in place. If your needs dictate otherwise, revise your plans. Once daily logs backups do not work for me but if they do for you, cool. I do full backups weekly, diffs nightly and logs throughout the day, using Backup Exec. The one drawback is the time to restore. I hate not being able to restore a 20GB (small in the grand scheme of things) in under 4 hours (not counting tape recall - if it went offsite). What works for your business needs is the most critical question and answer.
-- You can't be late until you show up.
September 25, 2008 at 8:56 am
One issue with it is that your Differential backups will NOT be tied to your FULL backup from Backup Exec. Differential backups are changes since the last full backup was taken. Since you are doing full backups using SQL daily, your differential backups done by Backup Exec will not contain what you are expecting.
This is the problem with multiple backup methods is that they step on each other and increase the complexity of any restore. The best method that I see is to choose just one method for doing your backups. This simplifies things greatly.
Chad
September 25, 2008 at 9:09 am
Chad Carter (9/25/2008)
One issue with it is that your Differential backups will NOT be tied to your FULL backup from Backup Exec. Differential backups are changes since the last full backup was taken. Since you are doing full backups using SQL daily, your differential backups done by Backup Exec will not contain what you are expecting.This is the problem with multiple backup methods is that they step on each other and increase the complexity of any restore. The best method that I see is to choose just one method for doing your backups. This simplifies things greatly.
Chad
If you do a "full" on Sunday (or whatever day) and differentials each evening thereafter, restore the full and then the latest diff backup. Replay any logs on top of that and you should be fine. I've yet to see that fail in the event of recovery.....While I agree with Chad about mutiple methods and trying to get down to one method - whatever works to support your business is the most appropriate. I prefer backing up to disk and then a tape sweep of my files from disk but that does not work for everyone. Ultimately, be sure you have a vaild, usable backup and that you can get to whatever point necessary in the event of disaster. Test your recovery methods to ensure they work and support your businees (or SLA) needs and then sleep a little easier at night knowing you can get the data back when needed....
-- You can't be late until you show up.
September 25, 2008 at 9:30 am
Terry,
I agree with everything that you said included your method for backing up to disk and then have the tape backup sweep from there. To clarify, the reason I stated what I did was because of Keith's proposal:
Backup Exec - Use a combination of Full backups (1 per week) and Differential Backups (6 per week), with no transaction log backups being run.
SQL Server - Use a combination of Full backups (daily) and Transaction Log backups (at least daily).
With this proposal of doing SQL Server full backups daily, the differential backups done by Backup Exec will not be tied to the full backup done by backup exec. They will be tied to the full backup done daily.
Keith,
So in a recovery mode, your full backup done by backup exec is useless unless you only want to recover to that point. Any other recovery method would require your full backup from SQL and possibly the differential or tlog backups from there.
Does that make sense?
Thanks,
Chad
September 25, 2008 at 9:57 am
Schedule your full backup and then your tran log backups but when netapp will do a full backup of your db it will reset the check point on tran log backups hence the previous tran log backup becomes out of sync.
Is there any reason why you have a netapp backup running also. Why not just do the sql full and tran log backups and then move the backups from disk to tape for archival purposes..eliminate the netapp backup from the loop.
September 25, 2008 at 10:00 am
Thanks Chad. Many methods to the same result. The biggest item, and I cannot stress this enough, is to test your D/R scenario. Be absolutely certain that if you need to restore that you can and with the least amount of data loss. Your data, your job. It's that simple.
-- You can't be late until you show up.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply