May 22, 2011 at 9:19 am
Hi Team,
In logshipping two tables are missing one of my team member is saying those are truncated is it possible
In logshipping primary server Database 'a'
table 'xxx' is truncated
in case it is truncated how can restore it from secondary server please guide me..
Tx
May 22, 2011 at 9:52 am
It's certainly possible, just means someone ran a TRUNCATE TABLE or DROP TABLE (depending whether the table is there and empty or not) on the primary server.
Fixing this kind of problem is not what log shipping is for. If you caught the problem before the log backup that contained the truncate went to the secondary, then you can bring the secondary online, copy the table out, then reconfigure log shipping. If you caught the problem after the log backup went to the secondary, then the tables will be empty there too.
What you'll likely need to do to solve this has nothing to do with log shipping. Find out when it occurred. Restore a full backup as a new DB. Restore log backups up until just before the truncate happened. Copy the tables across to the main DB.
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
May 22, 2011 at 7:54 pm
Hi Gail,
As per your comments i will try to restore the log bacup and we had taken full backup also but no use please suggest me
But some body says we will truncate the table in primary database automatically it will truncate in secondary database is it True ?
How is it possbile..
Thanks
May 22, 2011 at 9:40 pm
shiv-356842 (5/22/2011)
Hi Gail,But some body says we will truncate the table in primary database automatically it will truncate in secondary database is it True ?
How is it possbile..
Thanks
Yes, it is very much possible. Infact , it will always happen in log shipping. As you might be aware about what log shipping does, it simply moves the log backups taken on primary database to secondary server and then finally restores them on Secondary database with (Norecovery or Standby ) options. So, the moment you made any change in the primary database it will be captured in logs and hence the log backup taken after the change will keep it and then it will automatically get restored to secondary. There are jobs involved with log shipping which keeps the logs rolling on the Secondary servers.
Note: But remember in log shipping the changes will not occur on secondary database in real time like in mirrorring or in Replication. It will take time to get restored depending upon the frequency of your jobs.
Pls. let us know incase you need any other update on this issue.
Regards,
Sachin
May 22, 2011 at 11:31 pm
shiv-356842 (5/22/2011)
But some body says we will truncate the table in primary database automatically it will truncate in secondary database is it True ?
Automatically, no.
Log shipping works by transferring all database modifications to the secondary via log backups. All modifications in a DB are included in the logs, hence included in the log backup. So when the log backup that contains the log records for the truncate table is restored on the secondary, the table there will be truncated.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply