October 13, 2008 at 1:42 pm
I accidentally deleted the .tuf file on the log shipped server and the restore job is now failing (no surprise). Is it possible to recreate the .tuf file or is my only option to completely restart log shipping? (!?!?!)
October 13, 2008 at 2:09 pm
I did this myself recently and tried a number of things to avoid a full restore. all that mucking about has blurred my memory of what I did to get out of it but I am sure I had to do a full restore and restart log shipping.
The contents of the tuf file must be reapplied to recover rolled back uncommited transactions before applying the next tran log, so I see no way of applying the next log without the tuf file.
---------------------------------------------------------------------
October 13, 2008 at 2:18 pm
Thanks for the reply -- it makes sense. I'm starting the process again.
October 24, 2008 at 3:15 pm
Hi,
I´m on the same trouble.
My operations team have deleted a .tuf file form one of my logshiping servers.
My trouble is bigger than usually do because my database has 2.5 Tera Bytes so I gonna take 3 days to recevery it at least.
I did the same thing (deleted .tuf file) in a controlled server, and try to recover some logs without the .tuf file, but didn´t work in any way.
Let me know if somebody has solved this trouble without restart the whole logshiping processes.
Regards....
Pin
October 25, 2008 at 5:12 am
without tuf file it cannot roll forward ready for next log, so I see no way round this without starting from a restore, sorry.
Any chance its on a tape backup or in ther recycle bin? I believe there's tools out there that can recover deleted files as long as that part of the disk has not been re-used yet.
thought I saw your post in another thread? please dont cross post it wastes peoples time and dilutes responses, so doen't help you.
---------------------------------------------------------------------
October 27, 2008 at 6:23 am
I saw your post in another thread? please dont cross post it wastes peoples time and dilutes responses, so doen't help you.
Sorry, I did post this issue in another thread. You know it´s called 'Despair'
About the software to recovery deleted files I tried some that found many files but no one signal of the ".tuf". I think that this .tuf file never been there and I started to believe that this issue is related to a bug.
Analyzing the SQL Server Error log I saw that the time of the .tuf file that SQL server is looking for doesn't match to the time of backups and restores. Looks like SQL server got lost.
I gonna to start the full recovery (2.5 TB), probably it will take 3 days to finish.
Follow a piece of SQL server error log, if somebody is interested looks a challenge:
Log was restored. Database: PRD, creation date(time): 2008/08/12(18:13:23), first LSN: 372133:8797:1, last LSN: 372134:114707:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'G:\Standby\PRD_20081023123003.trn'}). This is an informational message. No user action is required.
2008-10-23 21:42:47.35 Backup Log was restored. Database: PRD, creation date(time): 2008/08/12(18:13:23), first LSN: 372130:157761:1, last LSN: 372133:8797:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'G:\Standby\PRD_20081023121501.trn'}). This is an informational message. No user action is required.
2008-10-23 21:43:15.75 spid54 Starting up database 'PRD'.
2008-10-23 21:43:15.79 spid54 The database 'PRD' is marked RESTORING and is in a state that does not allow recovery to be run.
2008-10-23 21:43:28.96 spid54 Recovery is writing a checkpoint in database 'PRD' (5). This is an informational message only. No user action is required.
2008-10-23 21:43:29.53 spid54 Starting up database 'PRD'.
2008-10-23 21:43:30.64 spid54 CHECKDB for database 'PRD' finished without errors on 2008-08-08 17:13:42.870 (local time). This is an informational message only; no user action is required.
2008-10-23 21:43:30.64 Backup Log was restored. Database: PRD, creation date(time): 2008/08/12(18:13:23), first LSN: 372133:8797:1, last LSN: 372134:114707:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'G:\Standby\PRD_20081023123003.trn'}). This is an informational message. No user action is required.
2008-10-24 00:00:44.22 spid18s This instance of SQL Server has been using a process ID of 2024 since 10/22/2008 6:16:59 PM (local) 10/22/2008 9:16:59 PM (UTC). This is an informational message only; no user action is required.
2008-10-24 15:03:16.38 spid54 Error: 3441, Severity: 17, State: 1.
2008-10-24 15:03:16.38 spid54 During startup of warm standby database 'PRD' (database ID 5), its standby file ('G:\Standby\PRD_20081024004247.tuf') was inaccessible to the RESTORE statement. The operating system error was '2(The system cannot find the file specified.)'. Diagnose the operating system error, correct the problem, and retry startup.
2008-10-24 15:18:16.32 spid54 Error: 3441, Severity: 17, State: 1.
2008-10-24 15:18:16.32 spid54 During startup of warm standby database 'PRD' (database ID 5), its standby file ('G:\Standby\PRD_20081024004247.tuf') was inaccessible to the RESTORE statement. The operating system error was '2(The system cannot find the file specified.)'. Diagnose the operating system error, correct the problem, and retry startup.
2008-10-24 16:20:54.14 spid68 During startup of warm standby database 'PRD' (database ID 5), its standby file ('G:\Standby\PRD_20081024004247.tuf') was inaccessible to the RESTORE statement. The operating system error was '2(The system cannot find the file specified.)'. Diagnose the operating system error, correct the problem, and retry startup.
2008-10-24 16:21:24.42 spid68 Error: 3441, Severity: 17, State: 1.
2008-10-24 16:21:24.42 spid68 During startup of warm standby database 'PRD' (database ID 5), its standby file ('G:\Standby\PRD_20081024004247.tuf') was inaccessible to the RESTORE statement. The operating system error was '2(The system cannot find the file specified.)'. Diagnose the operating system error, correct the problem, and retry startup.
2008-10-24 16:23:12.69 spid55 Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
2008-10-24 16:23:13.56 spid53 Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
2008-10-24 16:23:44.22 spid68 Error: 3441, Severity: 17, State: 1.
2008-10-24 16:23:44.22 spid68 During startup of warm standby database 'PRD' (database ID 5), its standby file ('G:\Standby\PRD_20081024004247.tuf') was inaccessible to the RESTORE statement. The operating system error was '2(The system cannot find the file specified.)'. Diagnose the operating system error, correct the problem, and retry startup.
2008-10-24 16:25:28.77 spid68 Error: 3445, Severity: 17, State: 2.
2008-10-24 16:25:28.77 spid68 File 'G:\Standby\PRD_20081024004247.tuf' is not a valid undo file for database 'PRD (database ID 5). Verify the file path, and specify the correct file.
Thanks, regards....
Eduardo Pin
May 7, 2009 at 1:33 am
I know this thread is very old, but I am replying for the sake of those who might get into this issue in the future.
I was recently testing my logshipping configuration and deleted the standby file. So far, the same behaviour as you have encountered, no subsequent or older tran log file could be applied.
My server is a kind of old, so I am on SQL 2000. What I did in order to solve it was to enable the updates to system tables:
sp_configure 'allow updates', 1
reconfigure with override
and then manually modify the status of the database in the sysdatabases table.
This is not recommended by MS, but it works.
So take your dbname, and perform:
begin tran
update sysdatabases set status = 16 where name = 'dbname'
Check that you only modified one row, belonging th your database, and then, if so,
commit tran.
Hope it helps other people.
May 7, 2009 at 3:40 am
what does this do? the TUf file hold info on transactions that were not committed at the time of the tran log backup and may therefore need rolling back or completing on next tran log restore. Does this update leave you open to losing transactions?
---------------------------------------------------------------------
May 7, 2009 at 3:48 am
Yes, I suppose those transactions are lost.
May 7, 2009 at 3:52 am
which kind of defeats the purpose of logshipping..................you might want to restore from primary and start again 🙂
---------------------------------------------------------------------
May 7, 2009 at 4:05 am
Well... true 🙂 but at least you have a valid copy of the database with some of the transactions that are lost... and supposing that the primary database is also lost forever the brief period while you realize the logshipping has issues, you are able to recover the data to a point in time, which is better than having both databases unavailable...
This is the worst scenario I was thinking of: lose the primary server, and someone deleting accidentally the standby file (as you are able to delete it, as compared to the mdf/ldf files which can be deleted only when sql server service is stopped).
May 7, 2009 at 4:09 am
in any case, you are right, in order to have the secondary DB synchronized, there is no other way than restarting logshipping...
what I was looking for was a way to reuse that secondary database, which, without its standby file, is no longer usable (only readonly I suppose).
July 6, 2011 at 9:42 am
I have a very small database size , i am trying logshipping, we are taking full backups nightly and sending them to remote location on remote server where our secondry database exists,
1. do i need to restore full backup every night ??and again start the logshipping??...
2. as copying full database backup takes 2 hrs, so do i need to wait for 2 hrs as my transactional restore job will lag by 2 hrs
July 6, 2011 at 10:17 am
amitwadhawan (7/6/2011)
I have a very small database size , i am trying logshipping, we are taking full backups nightly and sending them to remote location on remote server where our secondry database exists,1. do i need to restore full backup every night ??and again start the logshipping??...
2. as copying full database backup takes 2 hrs, so do i need to wait for 2 hrs as my transactional restore job will lag by 2 hrs
note 2 yr old thread!
no, you only need one full restore to start off the log chain, after that you just copy over and restore the log backups
---------------------------------------------------------------------
July 7, 2011 at 4:12 am
amitwadhawan (7/6/2011)
I have a very small database size , i am trying logshipping, we are taking full backups nightly and sending them to remote location on remote server where our secondry database exists,1. do i need to restore full backup every night ??and again start the logshipping??...
2. as copying full database backup takes 2 hrs, so do i need to wait for 2 hrs as my transactional restore job will lag by 2 hrs
if you have implemented log shipping why are you copying database backups. Do you understand how log shipping works?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply