February 24, 2012 at 3:04 am
Hi All,
What happen when any user has taken .trn backup from primary database during the processing of the log shipping ?
Suppose I have set agent on primary database to take backup, copy & restore in ever 15 minutes for my .trn files.
Scenario :-
Suppose my first .trn backup has been done by 4:30 pm.
As per my agent job next backup will be 4:45 pm, but by mistake any person(administrator) taken manually backup by 4:40 pm in other places(different place what the place defined in agent to backup and copy) from primary server database.
Then what will be in this case if .trn backup will be done by agent by 4:45 pm.
it will be right .trn backup (4:45 pm) for log shipping or any sln will be miss.
Please suggest me.
Regards,
Sachin.
February 24, 2012 at 3:46 am
If that ad-hoc log backup wasn't taken WITH COPY_ONLY and wasn't copied over and restored to the secondary, then the next log restore after that will fail, and so will all subsequent ones. You'll need to either restore a differential backup on the secondary to bridge that missing log backup, or restore a full backup so as to start over.
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
February 24, 2012 at 4:23 am
hi
what if we forget to restore that backup to secondary fro 2 hrs and after that
if i restore thatbckup file means
will other logshipping job backup files will move automatically or should i copy and restore all of them???
lest say
normal admin done at 4:40 and
ligshipping job backup at 4:45 and 5 and 5:15 , 5:30
if i copy and restore admin bakup of 4:40
will all other logship job backups will go/ copy and restore automatically???
February 24, 2012 at 4:35 am
satishm94 (2/24/2012)
hiwhat if we forget to restore that backup to secondary fro 2 hrs and after that
if i restore thatbckup file means
will other logshipping job backup files will move automatically or should i copy and restore all of them???
lest say
normal admin done at 4:40 and
ligshipping job backup at 4:45 and 5 and 5:15 , 5:30
if i copy and restore admin bakup of 4:40
will all other logship job backups will go/ copy and restore automatically???
if i remember, you can do a manual backup as long as you backup the trn file to the same location where logshipping backs up the trn files to, this will then get copied and restored as normal.
if you backup to a folder which is not used in logshipping then you should be able to copy the trn file to the seconday node and restore it manually or place it in the trn folder and SQL will restore it and the rest of the logs. the longer you leave it the longer the logs will take to catch up
February 24, 2012 at 6:01 am
kumar.sachu08 (2/24/2012)
Hi All,What happen when any user has taken .trn backup from primary database during the processing of the log shipping ?
Suppose I have set agent on primary database to take backup, copy & restore in ever 15 minutes for my .trn files.
Scenario :-
Suppose my first .trn backup has been done by 4:30 pm.
As per my agent job next backup will be 4:45 pm, but by mistake any person(administrator) taken manually backup by 4:40 pm in other places(different place what the place defined in agent to backup and copy) from primary server database.
Then what will be in this case if .trn backup will be done by agent by 4:45 pm.
it will be right .trn backup (4:45 pm) for log shipping or any sln will be miss.
Please suggest me.
Regards,
Sachin.
Log shipping will be disrupted IF you dont copy tht .trn backup to the same folder ( used in log shipping) OR manually restore the secondary database with the .trn backup.
Regards,
Skybvi
Regards
Sushant Kumar
MCTS,MCP
February 24, 2012 at 8:38 am
thanks to all for more clarification.
February 29, 2012 at 4:37 am
If that ad-hoc log backup wasn't taken WITH COPY_ONLY and wasn't copied over and restored to the secondary, then the next log restore after that will fail, and so will all subsequent ones. You'll need to either restore a differential backup on the secondary to bridge that missing log backup, or restore a full backup so as to start over.
Hi Gail,
Can we restore a differential backup and then proceed restoring with Log backups ? how is it possible ? can you please give more details on this
Thanks
Chelladurai
February 29, 2012 at 6:13 am
You should be able to, yes. Straightforward, standard restore of the differential with either norecovery or standby, whichever you are using for the log shipping.
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
February 29, 2012 at 9:45 pm
I tried with below command and i get error. Please help.
restore database logtest
with standby='e:\sql\primarydb_diff.bak'
Msg 3153, Level 16, State 3, Line 1
The database is already fully recovered.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
February 29, 2012 at 9:47 pm
But i am able to restore log backups with same command as below
restore log logtest
with standby='e:\sql\primarydb_20120223093001.trn'
February 29, 2012 at 11:37 pm
as per my knowledge , u should use
log for restoring log file not databse
like
restore log dbname
from disk='d:\logbckup.trn'
with recovery
or u can use stansby
not
restore database dbname
but i dont know about the command u used
"
restore log logtest
with standby='e:\sql\primarydb_20120223093001.trn'
"
thanks
March 5, 2012 at 3:04 am
Hi GAIL,
Could you please help me on below issue and let me know with little example on how to restore full and then diff and then followed by log backups...
I tried with below command and i get error. Please help.
restore database logtest
with standby='e:\sql\primarydb_diff.bak'
Msg 3153, Level 16, State 3, Line 1
The database is already fully recovered.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
But i am able to restore log backups with same command as below
restore log logtest
with standby='e:\sql\primarydb_20120223093001.trn'
March 5, 2012 at 3:39 am
Hi,
Can someone help me how to restore diff backup and then a log backup? I am getting error as below
I tried with below command and i get error.
restore database logtest
with standby='e:\sql\primarydb_diff.bak'
Msg 3153, Level 16, State 3, Line 1
The database is already fully recovered.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
But i am able to restore log backups with same command as below
restore log logtest
with standby='e:\sql\primarydb_20120223093001.trn'
March 5, 2012 at 4:03 am
Your syntax is wrong.
RESTORE DATABASE <database name> FROM DISK = <backup location>
WITH STANDBY = <Undo file location>
and
RESTORE LOG <database name> FROM DISK = <backup location>
WITH STANDBY = <Undo file location>
p.s. We're volunteers here. Nagging is a good way to get ignored.
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 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply