July 19, 2010 at 4:19 am
Hi, one quick question plz.
I have a database and have a full maintenance plan for it, with full Back Up, differential back up and taillog back up, lets call it Maintenance plan 1. I was wondering if i can i create a second maintenance plan on the same database , lets call it Maintenance Plan 2 - which will contain only a full back up. I want however to make sure that the differential and transaction log back ups of maintenance plan 1 will not be based on the full back up of the maintenance plan 2. Is this the case?
Regards
Dionisis
July 19, 2010 at 4:32 am
No, you cant, you will confuse SQL and your LSN sequence.
The Maint1 log backups will fail if you do a second full backup in between.
Differential will also be based on what changed since last Full Backup, so you will have 4 sets of backups that cant be restored to a point in time, only full.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
July 19, 2010 at 4:38 am
Thank you very much for your reply. So is there any way that i can have two types of back up in different folders, for extra security? For instance, lets say something goes wrong with the maintenance plan 1. What additional option do i have to save my database?
July 19, 2010 at 4:43 am
Henrico Bekker (7/19/2010)
No, you cant, you will confuse SQL and your LSN sequence.The Maint1 log backups will fail if you do a second full backup in between.
Log backups won't fail. Full backups do not truncate the transaction log. the log backups won't be affected at all by the second full backup. Log backups are only based on the last log backup, unless there's been a break in the log chain.
The differentials are another matter. Differentials are based on the last full backup that ran, so the diff will be based on the full of main plan 2. you can avoid this by taking that full backup WITH COPY_ONLY.
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
July 19, 2010 at 5:03 am
Thank you very much for your time.
So let me see if i get this write. I have a maintenance plan (that i allready have build) and it does contain a full back up at 24.00 each day, then every 6 hours a differential back up and every half an hour log back ups. This I have allready build and test for my Company's ERP database.
1. Now i can go to the same database, create a new maintenance plan, with only one full back up every night (this is what i want for extra security), and by using the option with COPY_ONLY on the second maintenance plan, I can have both my maintenance plans working perfectly. Is this correct? You make me very happy! :-). Now if for whatever reason i lose the maintenance plan 1 (lets say hard drive failure, where it is stored), i can at least "go back" at last nights database, from the full back up of maintenance plan 2.
2. One more thing i need you to comment. Because Developers from the external "implementor" company are still working on our ERP system, i have to communicate to all, that they must not create full back ups unless they use the With_COPY ONLY option. Otherwise, if somebody decides to take a full back up to test his work, my maintenance plan will "blow up" without me even knowing it correct? (That is one of the reasons that i wanted a "back up" back up maintenance plan).
Thank you very - very much.
July 19, 2010 at 5:11 am
Gail's right of course, but can I ask what the purpose of the second full backup is?
If you just want a separate physical copy of the same full backup you could use the MIRROR TO option of the BACKUP DATABASE statement. I don't recall offhand whether the Maintenance Plan Wizard thing supports that option, but I do know it's only available in Enterprise Edition. The other obvious option is to copy the original backup somewhere else, maybe to tape.
Just another possible option for you. A COPY_ONLY backup does have some advantages, but you would not be able to apply and of the normal differentials to it, though you could roll forward using the regular log backups.
The best thing you can do to make sure you can recover your database, is to regularly perform a real restore (to a test or disaster-recovery system) from your main backup files.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 19, 2010 at 5:16 am
Dfalir (7/19/2010)
Otherwise, if somebody decides to take a full back up to test his work, my maintenance plan will "blow up" without me even knowing it correct? (That is one of the reasons that i wanted a "back up" back up maintenance plan).
The maintenance plan won't blow up. You won't notice anything at backup time. It's at restore, when you restore the full then go to restore the diff and find that it won't restore that you'll notice something is wrong.
I'm also curious. Why a second full backup? What does that gain you that copying the midnight full doesn't?
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
July 19, 2010 at 5:50 am
Ok, you both have more knowledge than me, so i will tell you why i was thinking to do things the way i described. Both of the options you mentioned (mirror to and transfer to tape), assume by default that the original Full Back Up of maintenance plan 1, will be fully functional each and every time it is created, and there is not a chance that this full back up may become corrupted for whatever reasons, at the point of creating it.
If there is a chance of the above happening then both the Mirror and the Tape Full back ups, will also be corrupted, as these will be copies of the Full back up. If this is the case then i do not need the second with copy_only full back up.
Do you by your experience consider this as an extremely unlikely event, ever to take place?
July 19, 2010 at 5:55 am
Whoa! sorry for my bad advice there, and thank you to Gail for setting me straight.
I learned from it as well.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
July 19, 2010 at 6:03 am
Dfalir (7/19/2010)
Do you by your experience consider this as an extremely unlikely event, ever to take place?
It's certainly possible. Which is why the recommended practice is to test backups by restoring them (restore verify only is not sufficient unless the backup was taken WITH CheckSum and all the database pages have checksums).
Test the backup by restoring it and, if it restores, you can be pretty confident that the backup is OK and can be archived (tape/whereever)
It's not 100% certain that there won't be a tape problem later. This is why there are typically retention period for backups that are long enough that should a backup be found faulty, there's an alternative restore part available.
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
July 19, 2010 at 6:52 am
Just one small thing to add to that - even if a full backup is somehow corrupted, you might still be able to recover from the previous full backup, assuming it is still available.
You would not be able to apply any differentials taken after the corrupted full backup, but a differential prior to that (one based on the previous full backup) could be used. From that point forward, you could just apply all the transaction log backups (both before and after the failed full) to bring you right up to date (though you would also need to apply a tail-log backup).
That said, applying a long chain of log backups will be slower, and raises the risk a bit since one duff log backup would leave you without a database.
There's certainly not much harm in taking a separate COPY_ONLY full backup as well, but you have to draw the line somewhere. Testing the full backup with a restore (or using the checksum + verify option Gail noted) is generally enough for me, but then most of the systems I work with have additional high availability/recovery options (like mirroring or replication) in addition to extremely robust and redundant storage systems.
So, once again, the answer is "it depends!" 😀
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 19, 2010 at 7:28 am
1. As regarding, the "Test the back up to ensure its integrity..." A new full back up each night, doent it mean that i have to test the full back up each morning? If this is the case, then it is time consuming for an everyday task, and that is why i was thinking the second solution of creating full back up with COPY ONLY option.
2. You guys said something about checksum. I will find more info about this, because if this is what it takes to ensure back up integrity without me checking the full back up each day.
3. Regarding the last comment "Just one small thing to add to that - even if a full backup is somehow corrupted, you might still be able to recover from the previous full backup, assuming it is still available". With a maintenance plan, the latest full back up, overwrites the previous, is this the case?
4. A tail Log back up, is actually a transaction log back up at the point where the database got damaged. I mean i use the exact same options for this back up, as if i were to perform a transaction log back up. Is this right?
5.
You would not be able to apply any differentials taken after the corrupted full backup, but a differential prior to that (one based on the previous full backup) could be used.... From that point forward, you could just apply all the transaction log backups (both before and after the failed full) to bring you right up to date (though you would also need to apply a tail-log backup).
--- Is this correct? how?
a transaction log back up, is based on the differential back up if it exists. So if i have the following sequence
Full back up1 (working)--->TLB1 ...TLB10 --> Differential1 --> TLB11-TLB20 --> Full back up2 (Damaged)--> TLB21-TLB30 --> Differential2 (based on damaged Full back up 2) --> TLB31 - TLB40.
If the Full back up2 is damaged, I can not apply the Differential2, because this differential is based on the damaged Full back up2. So all i have is the sequence Full back up1 (working)--->TLB1 ...TLB10 --> Differential1 --> TLB11-TLB20 --> After this TLB20, the damaged full back up occurs, so arent all the rest of the transaction log back ups [TLB31 - TLB40], useless since they are based on the differential2 which in turn is based on the full back up 2? What am i missing?
both before and after the failed full?
Hence, the company does not provide funds for high availability/recovery options (like mirroring or replication) in addition to NONE extremely robust and NONE redundant storage systems. :w00t:
Don't supply too much info to me, because i will keep asking more stuff! 🙂 thank you so much, for all your replies.
July 19, 2010 at 7:55 am
Dfalir (7/19/2010)
1. As regarding, the "Test the back up to ensure its integrity..." A new full back up each night, doent it mean that i have to test the full back up each morning? If this is the case, then it is time consuming for an everyday task, and that is why i was thinking the second solution of creating full back up with COPY ONLY option.
Yes, it needs doing every day, but why do it manually? Schedule and automate. The only time you should have to spend any time is in the initial setup and any time it fails.
3. Regarding the last comment "Just one small thing to add to that - even if a full backup is somehow corrupted, you might still be able to recover from the previous full backup, assuming it is still available". With a maintenance plan, the latest full back up, overwrites the previous, is this the case?
That's not the default afaik.
4. A tail Log back up, is actually a transaction log back up at the point where the database got damaged. I mean i use the exact same options for this back up, as if i were to perform a transaction log back up. Is this right?
Not quite. A tail log backup has either the no_truncate or no_recovery option. No_Truncate if the DB is damaged, no_recovery if you just want to backup the last of the log records and make the DB unavailable so as to restore
After this TLB20, the damaged full back up occurs, so arent all the rest of the transaction log back ups [TLB31 - TLB40], useless since they are based on the differential2 which in turn is based on the full back up 2? What am i missing?
Neither full nor diff backups truncate the transaction log. Log backups are only based off the previous log backup. They are not tied to a specific full or differential.
To restore with logs, you need a full, optionally a diff and then all the log backups that were taken after that full (or diff), in order, up to the point that you want to recover to. Full or diff backups taking during that interval are irrelevant, providing you have all the log backups in order, no one has explicitly truncated the log (which they can't in 2008) and the recovery model has never been switched to simple.
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
July 19, 2010 at 8:15 am
July 19, 2010 at 8:16 am
1. Yes, testing the backup everyday does get old pretty fast. Many shops have a development or test environment that is 'refreshed' from the production systems - so that serves as the restore test.
2. The CHECKSUM thing only works under the conditions Gail specified. The tricky bit is that all the pages for the database must have checksums. While this is controlled by the Page Verify database option, it's not always easy to guarantee that checksums actually exist on all pages, since they are only added to pages when they are prepared for writing to disk. Unless your database was created in (at least) SQL Server 2005 - upgraded from 2000 or earlier does not work - and it was created with the Page Verify option set to checksum, and if it has never been changed from that setting, you might be able to say with some confidence that all pages have checksums!
3. I believe it is configurable, so you can keep x days worth before overwriting. Best practice is never to overwrite an old full backup until you have a fully verified new full backup somewhere safe. Otherwise, what would you do if the backup fails?
4. A tail log backup specifies WITH NORECOVERY (and possibly NO_TRUNCATE, if the database is damaged). It is a bit more than a regular transaction backup, since it captures the active portion of the log too. Without this extra log backup, you could only ever hope to recover to the point of the last ordinary log backup. If you want to get back to the point of failure, you need the tail of the log.
5. A transaction log backup is based on one thing, and one thing only: the last transaction log backup. [Ok, so you need to take a full backup right at the start of the database's life to allow you to start taking log backups, but that's a technicality]. In principle, you could restore the first-ever full database backup and apply every single log backup taken since then (regardless of other full backups and differentials!) to bring a restored copy right up to date (including the tail of the log backup of course). I can't stress enough that log backups (the log chain) are independent of fulls and differentials. Differentials have a full as their base, transaction log backups do not. The only thing that every truncates the log is a log backup. Therefore, a full chain a logged changes can be maintained in a complete set of transaction log backups. It would be impractical to do the restore I have just described, since applying logs is a relatively slow process. People take full backups and differentials (all changes since the last full) in order to speed recovery time, and reduce risk.
Hence, the company does not provide funds for high availability/recovery options (like mirroring or replication) in addition to NONE extremely robust and NONE redundant storage systems.
:laugh: I see. Well in that case (and having written a book above) it seems that a COPY_ONLY backup is not such a bad idea for you. Still, you must test your recovery strategy regularly, somehow. You don't have a backup until you have restored it successfully.
Don't supply too much info to me, because i will keep asking more stuff! 🙂 thank you so much, for all your replies.
That's fine, we get paid by the word :laugh:
Please find the time to read Paul Randal's excellent blog that covers most subjects, and backup/restore in particular. Here's his 30 database myths entry to get you started: http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(3030)-backup-myths.aspx
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply