Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
catania
Starting Member
1 Post |
 Posted - 2014-11-16 : 02:22:34
|
| Can anyone give me explanation whether this action is correct ? :I have ms sql server 2005 database .I do full backup of database every day at 9.00 PMand backup of transaction log every 2 hours.backup schedule : .... 6 PM -transaction log backup 8 PM -transaction log backup 9 PM -full backup of the database 10 PM -transaction log backup ....Transaction log backup contains all transactions from the last log backup.Transaction log at 10 PM will contain all transaction from 8 PM to 10 PM.But at 9 PM full backup of database is done.At 11 PM happens database failure .I do database restore from full backup created at 9 PM.A now I want to do a restore from transaction log backup created at 10 PM.This transaction log backup contains all transaction from 8 PM to 10 PM.It means that it contains transaction executed after full backup andalso transactions executed before full backup. My question is :Is correct to do a restore of transaction log created at 10 PM ?Will the database be after restore in correct state ?From the transaction log backup will be restored only transactions which were executed after full backup ?Has a restored full backup some information about last transaction executed ? |
|
|
Kristen
Test
22859 Posts |
Posted - 2014-11-16 : 03:30:34
|
quote:
Originally posted by catania
Is correct to do a restore of transaction log created at 10 PM ?
You need to restore:Full Backup from 9PMThen Log Backup from 8PMthen Log Backup from 10PMquote:
Will the database be after restore in correct state ?
Yesquote:
From the transaction log backup will be restored only transactions which were executed after full backup ?
Yes, restoring the 8PM Log Backup, after restoring the 9pm Full Backup, will only restore transactions made after the Full Backupquote:
Has a restored full backup some information about last transaction executed ?
Yes (The full backup contains the state at the END of that backup, rather than the beginning, so in effect will restore to, say, 9:01pm) |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2014-11-16 : 03:32:33
|
| P.S. You should make Log Backups more often than every 2 hours. If you make log backups every 15 minutes then the total disk space used, in a day, is the same as if you make log backups every 2 hours (plus a bit more for "housekeeping" on each file), but if you have a disaster you will lose at most 15 minutes work, rather than 2 hours work. Also your Log File will not have to store as much information, between backups, so will be smaller - may a LOT smaller (particularly when you do Index Rebuild etc.) |
|
|
|
|
|
|
|
|
Transaction log restore