November 21, 2005 at 10:30 am
Hi,
I was reading about file and filegroup backup, and came across the concept that when tables and their indexes are stored on separate files, those files must be backed up and restored together. The question is when you have transaction logs, what difference does it make if the files are not backed up together. Plus I am not sure whether a transaction log can be applied to a specific file and not the whole database if only one of the files is corrupt. In any case, the transaction log will bring the file up to date, regardless of whether it stores tables or their indexes. Why do we have to back up and restore these files together? I would highly appreciate if someone clarified this.
Karim
November 24, 2005 at 8:00 am
This was removed by the editor as SPAM
November 24, 2005 at 9:54 am
I've asked one of my team who's responsible for backup and restore to reply to this - may not happen until Monday though.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
November 27, 2005 at 3:39 pm
If you have a database which is in full recovery mode, and if you roll the database forward to some point in time after the restore, then you could have separate backups for data tables and the related indexes.
On the other hand, if a database was in simple recovery mode, backing them up separately would almost certainly lead to a disconnect, requiring the index to be rebuilt. Logically, the table and related index are one entity, and should be handled as such.
Think of it as what can be made to work vs best practices. Best practice is to always handle tables and their related indexes as a unit.
November 28, 2005 at 9:35 pm
Dear Kevin,
I am sorry but I couldn't follow you exactly. First of all, I don't understand what you mean by rolling forward a database. I also don't understand how you would do something like that and why you would do it after a restore. Plus, how would this lead to separate backups for tables and indexes and what's the impact of such a situation?
Secondly, when the database is in simple recovery mode, then will backing up the tables and indexes separately lead to a disconnect because further transactions will take place after the backups? Or is it because the log file cannot be used to bring the two in sync?
I would highly appreciate if you shed some light on the above.
Karim
December 14, 2005 at 12:32 pm
OK, I'll see if I can explain it better.
The database as a whole MUST be at the same point in time.
If a database is in full recovery mode, you can restore the files from their various backups, and then apply transaction logs (rolling forward the database) until the entire database represents the same point in time.
If, however, the database is in Simple recovery mode, you do not have the option of applying logs. In this case, the table would be at time t0 and the index would be at time t1 due to the fact that their backups happened at different points in time. If ANY change was made in between the backups, you have the chance that there would be a disconnect between the content of the table and the content of the index. For example, if you backed up the index first, then deleted one or more rows, and then backed up the table, after restoring from the backup you would have entries in the index for rows that no longer exist.
December 14, 2005 at 9:43 pm
Thanks a lot, Kevin. Your explanation of the need to backup both table and index at the same time in simple recovery model makes a lot of sense b/c transaction logs are not present to bring the files in sync.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply