September 3, 2004 at 12:03 pm
I have a client that when a transactional backup (automated) is performed wants to know IF this backup should generate some message that the LSN sequence is out of sync and a full database backup is required to re-establish a recovery point.
I completed the following steps
1. -Establish a test database with Full Recovery mode enabled
-- TRANS_DB_TEST
FULL BACKUP = 2004-09-01 20:50
POINT IN TIME RECOVERY = 2004-09-02 19:30
2. -Create a full database backup to establish original recovery point
Created Automated Maint Plan
- Full Backup Job -created and run
- Trans Backup Job -created (not run at this step)
- Verified 3020 error set to log (NT Level)
3. -Execute first set of test data updates to generate transactions in the log
- Create load table (insert 1000 Test Records)
--2nd Test run Create load table (insert 1000 Test Records)
4. -Issue a transaction log backup similar to our production 30 minute log backups
- Trans Backup Job -run no errors
--2nd Test run Trans Backup Job -run no errors
5. -Execute second set of test data updates to generate additional transactions in the log
- Create load table (insert 2000 Test Records)
--2nd Test run Create load table (insert 2000 Test Records)
6. -Issue a TRUNCATE LOG on the database
- TRUNCATE LOG RUN (T-SQL)
- T-SQL CMD= Backup Database NESEQUIPRENT WITH TRUNACTE_ONLY
- Error 17055 thrown To NT EVENT LOG from SQL 3020 Error
--2nd Test run TRUNCATE LOG RUN (T-SQL)
- T-SQL CMD= Backup Database NESEQUIPRENT WITH TRUNACTE_ONLY
- Error 17055 thrown To NT EVENT LOG from SQL 3020 Error
7. -Execute final set of test data updates to generate transactions in the log
- Create load table (insert 3000 Test Records)
--2nd Test Create load table (insert 3000 Test Records)
8. -Issue a transactional log backup as in step 4
8.1. -> this backup should generate some message that the LSN sequence is out of sync and a full database backup is required to re-establish a recovery point
- Transactional Backup job run (verify option set)
--No LSN or Error thrown
Jbabington
Jbabington@hotmail.com
September 6, 2004 at 8:00 am
This was removed by the editor as SPAM
September 7, 2004 at 4:17 pm
I'm having a difficult time following exactly what occured in your test, but it looks like the truncate you attempted in step 6 did not succeed, therefore the log sequence did not get broken. The command you issued in step 6 is incorrect.
BACKUP LOG NESEQUIPRENT WITH TRUNCATE_ONLY
All this aside, the error message that your client is getting is caused by some action that reset the recovery point, like a truncate. Just have the client issue a full backup to establish a new recovery point, and all should be well.
Steve
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply