October 6, 2008 at 5:16 pm
Had a long debate with my manager over whether rebuilding indexes are necessary after migrating from SQL 2000 to 2005.
His argument was that Restoring database should automatically re-create the indexes hence no rebuild needed, i told him he's wrong giving that i've always rebuild after migrate, but he wants to find out exactly why since oracle would have done the same when restoring database (he more oracle inclined).
So his question was.
1. What does SQL do in terms of indexes when database are restore from backup.
2. Does it create indexes on restore and it's data.
can anyone elaborate on this describing axeacly what happens.
thanks
October 7, 2008 at 1:38 am
A restore creates the database exactly as it was at the time of backup, tables, indexes, etc. There's no rebuild necessary at all.
What you do need to do after upgrading to 2005 is to update all of the statistics. The reason is that 2005 keeps more detailed statistics and can't use the 2000 ones effectively. Stats can be updated by running the UPDATE STATISTICS statement or by rebuilding the indexes.
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
October 7, 2008 at 6:07 am
Make sure you do a DBCC UPDATEUSAGE first, in order to remove inaccuracies in the statistics.
After that, run sp_updatestats tot update your statistics.
Note: There are some discussions on the web about this issue (storing indexes in a backup). It has some advantages (smaller backup) and some disadvantages (for example rebuilding indexes after a restore).
So your manager's idea is not new.
Wilfred
The best things in life are the simple things
October 7, 2008 at 7:35 am
So far i understand what you guys are saying. I just want to find out exactly how SQL server restore database from a backup in reference to indexes (Will it recreate indexes during restoration or is it just an image?).
Another reason for asking is what if the Tables in the database were heavily fragmented? it was this concern why i had recommend rebuilding or reorganize indexes after database is restored.
thanks guys
October 7, 2008 at 7:44 am
A full backup is just a copy of every used page in your database. So if you have some sort of fragmentation in your database, it's there (again) after a restore.
This is also the reason why you could get database curruption in your backup: if you don't check your databases with dbcc checkdb, corruption is also in your backup. If you restore a recent backup, your curruption is also restored (in most cases). That's why Microsoft recommends a DBCC CHECKDB before a backup (but to my opinion that's too much overkill)
Wilfred
The best things in life are the simple things
October 7, 2008 at 7:59 am
Wilfred van Dijk (10/7/2008)
A full backup is just a copy of every used page in your database. So if you have some sort of fragmentation in your database, it's there (again) after a restore.
ok, So ur actually saying SQL does not recreate these indexes on restore (if it did, it would have resolve theses index fragmentations?), otherwise i would have to rebuild or reorganized indexes to fix fragmentation.
am i correct everyone?
thanks for your inputs
October 7, 2008 at 8:02 am
I ran DBCC ShowContig on a 2000 database, then restored it to 2005 and ran DBCC ShowContig. The results were exactly the same, which leds me to believe the indexes are not re-created.
Then, I ran ALTER INDEX ALL ON dbo.standards REBUILD on a table, reran DBCC ShowContig and that table had better Average page Density & Scan Density
God Bless,
Thomas
Thomas LeBlanc, MVP Data Platform Consultant
October 7, 2008 at 8:06 am
So ur actually saying SQL does not recreate these indexes on restore
Correct, indexes are not recreated after a restore. The indexpages are part of the backup and they are restored on the same page as they were before the backup.
Wilfred
The best things in life are the simple things
October 7, 2008 at 8:08 am
Denby (10/7/2008)
Wilfred van Dijk (10/7/2008)
A full backup is just a copy of every used page in your database. So if you have some sort of fragmentation in your database, it's there (again) after a restore.ok, So ur actually saying SQL does not recreate these indexes on restore
When the database is restored, the data and index pages are placed into the file exactly as they were when the backup was taken. The restored database will look exactly the same as it did at the time of backup.
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
October 7, 2008 at 8:08 am
ok great
thanks alot
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply