November 2, 2011 at 4:23 am
Hello All,
I have just dropped indexes and created new ones on few tables in my database. Now within next couple of hours I am going to apply a transaction log backup to this database and that backup will not have these new indexes and it will have the old indexes.
So what will happen to my current indexes after I apply the log backup?
Thanks
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
November 2, 2011 at 4:36 am
If by apply you mean restore. Then those new index will be gone because they don't exist in the log backups you had before creating them.
Is this theorical only or you have a problem you need to solve?
November 2, 2011 at 5:05 am
You actually mean "restore the most recent full backup, then restore the subsequent transaction log backups in order" right? That's how a restore works...you can't just "apply a log backup"...
November 2, 2011 at 8:39 am
NinjasRGurus,
This was for testing some of the indexes that were newly created on a database which is a backup copy of our production database. I restored a diff. backup from this morning once the full backup was restored and then created new indexes. Now to get it to point in time I needed to apply a log backupthat was taken few minutes back. So i wondered if the newly created indexes will be gone if I restore a log backup.
Thanks,
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
November 2, 2011 at 8:45 am
Depends how you do it.
If you create the indexes, THEN take a log backup.
And then restore all the way to that last log backup then yes they'll be available.
The backup is an exact copy of the db as it was when the backup finished.
So assuming you restore all the relevant log & don't use STOPAT then you,ll have everything back the way it was.
November 2, 2011 at 9:26 am
Sapen (11/2/2011)
This was for testing some of the indexes that were newly created on a database which is a backup copy of our production database. I restored a diff. backup from this morning once the full backup was restored and then created new indexes. Now to get it to point in time I needed to apply a log backupthat was taken few minutes back. So i wondered if the newly created indexes will be gone if I restore a log backup.
You won't be able to apply a log backup in that case. The database is online, it has to be for you to have created indexes. If you try to restore a log backup now you'll get an error.
Restore failed, no files are ready to roll forward.
To restore log backups, the DB has to be in standby or restoring, neither of which allow modifications to be made to the 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
November 2, 2011 at 9:53 am
Yes..Thanks Gail.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply