Restore database and trx log

Hi:
My db is set to full recovery mode. Do i need to restore the latest
transaction log after restore a latest complete db backup ?
eg:
1.00pm Trx log backup
2.00pm Complete db backup
3.00pm Trx log backup
There is no activity after 2.00pm, when i restore the 2.00pm db backup
to another server, do i need to restore 3.00pm trx log backup ? Will
the complete db backup include the data between 1.00pm and 2.00pm ?
Please help.
Thanks
JCVoon
Hi
> There is no activity after 2.00pm, when i restore the 2.00pm db backup
> to another server, do i need to restore 3.00pm trx log backup ? Will
> the complete db backup include the data between 1.00pm and 2.00pm ?
No. Even though you restore full backup from ther previous week and
applied all log files including this one made on 1PM you will not have the
data between 1PM and 2PM.
If it is crtical for you so consider backu log file every 15 minute for
exampe
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142835431.892296.151550@.z34g2000cwc.googlegr oups.com...
> Hi:
> My db is set to full recovery mode. Do i need to restore the latest
> transaction log after restore a latest complete db backup ?
> eg:
> 1.00pm Trx log backup
> 2.00pm Complete db backup
> 3.00pm Trx log backup
> There is no activity after 2.00pm, when i restore the 2.00pm db backup
> to another server, do i need to restore 3.00pm trx log backup ? Will
> the complete db backup include the data between 1.00pm and 2.00pm ?
> Please help.
>
> Thanks
> JCVoon
>
|||The database backup made a 2pm will include all transactions up through 2
pm. If you restore the backup with recovery, you should have the database as
it was as of 2 pm.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142835431.892296.151550@.z34g2000cwc.googlegr oups.com...
> Hi:
> My db is set to full recovery mode. Do i need to restore the latest
> transaction log after restore a latest complete db backup ?
> eg:
> 1.00pm Trx log backup
> 2.00pm Complete db backup
> 3.00pm Trx log backup
> There is no activity after 2.00pm, when i restore the 2.00pm db backup
> to another server, do i need to restore 3.00pm trx log backup ? Will
> the complete db backup include the data between 1.00pm and 2.00pm ?
> Please help.
>
> Thanks
> JCVoon
>
|||Uri Dimant, Kalen Delaney:
Thanks for the quick reply.
I did a small test, i create a new db and pump in some data, then i
perform a trxlog backup, after the log backup, i pump in another chunk
of data, then i perform full db backup, i restore the full db backup to
another db and i notice that the data that i insert after the 1st
trxlog backup is exist in the new db. But my customer complain that
after he restore the full db backup, data enterd before full db backup
is not exist ! Can u please explain what is going wrong ?
Thanks
JCVoon
|||maybe when you perform full db backup, the transaction is not yet committed
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142839424.157319.177960@.i40g2000cwc.googlegr oups.com...
> Uri Dimant, Kalen Delaney:
> Thanks for the quick reply.
> I did a small test, i create a new db and pump in some data, then i
> perform a trxlog backup, after the log backup, i pump in another chunk
> of data, then i perform full db backup, i restore the full db backup to
> another db and i notice that the data that i insert after the 1st
> trxlog backup is exist in the new db. But my customer complain that
> after he restore the full db backup, data enterd before full db backup
> is not exist ! Can u please explain what is going wrong ?
> Thanks
> JCVoon
>
|||Can you provide a script yopu tested on?
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142839424.157319.177960@.i40g2000cwc.googlegr oups.com...
> Uri Dimant, Kalen Delaney:
> Thanks for the quick reply.
> I did a small test, i create a new db and pump in some data, then i
> perform a trxlog backup, after the log backup, i pump in another chunk
> of data, then i perform full db backup, i restore the full db backup to
> another db and i notice that the data that i insert after the 1st
> trxlog backup is exist in the new db. But my customer complain that
> after he restore the full db backup, data enterd before full db backup
> is not exist ! Can u please explain what is going wrong ?
> Thanks
> JCVoon
>
|||> But my customer complain that
> after he restore the full db backup, data enterd before full db backup
> is not exist !
That does not happen. Most likely they restored incorrectly. Perhaps they appended to the backup
file (?) and then restored one of the older backups on that file? Use RESTORE HEADERONLY to
investigate, and the FILE option of the restore command to specify which to restore.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142839424.157319.177960@.i40g2000cwc.googlegr oups.com...
> Uri Dimant, Kalen Delaney:
> Thanks for the quick reply.
> I did a small test, i create a new db and pump in some data, then i
> perform a trxlog backup, after the log backup, i pump in another chunk
> of data, then i perform full db backup, i restore the full db backup to
> another db and i notice that the data that i insert after the 1st
> trxlog backup is exist in the new db. But my customer complain that
> after he restore the full db backup, data enterd before full db backup
> is not exist ! Can u please explain what is going wrong ?
> Thanks
> JCVoon
>
|||Tibor Karaszi:
Thanks for your reply.
So i can confirm that restoring the full db backup will bring the db to
the state that i perform the full db backup right ?
Any for the uncommitted transaction during the full db backup, i have
to backup the trxlog after perform the full db backup and apply it to
the new db right ?
Thanks
JCVoon
|||Atenza:
This make sense.
Thanks.
JCVoon
|||Hi
When database is restoring CHECKPOINT occures. It checks what transactions
to undo or redo
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142844757.254805.129640@.i39g2000cwa.googlegr oups.com...
> Tibor Karaszi:
> Thanks for your reply.
> So i can confirm that restoring the full db backup will bring the db to
> the state that i perform the full db backup right ?
> Any for the uncommitted transaction during the full db backup, i have
> to backup the trxlog after perform the full db backup and apply it to
> the new db right ?
> Thanks
> JCVoon
>