Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In SQL Server, if Database Restore Time takes less time compare to Index Rebuilds OR Reorganize . Can we Choose Restore as a option for DE-fragmentation. If its not a 24/7 database.

share|improve this question
add comment

migrated from stackoverflow.com Jul 12 '12 at 23:10

This question came from our site for professional and enthusiast programmers.

4 Answers

That doesn't make much sense. If you restore a database from a time earlier, you won't be capturing data that has changed since the backup. (but I'm assuming you mean a backup directly followed by a restore)

If you're talking about taking a full backup and then immediately a restore, then you will persist the fragmentation in the restored database as it was when it was backed up.

So either way I will say no that is not advisable. Prudent index rebuilds/reorganization based on the fragmentation is best practice.

See this blog post that on database restoration and index fragmentation that debunks that myth. Restoring a database also restores the fragmentation at the time of the backup.

share|improve this answer
add comment

In general no, the restore, at least in "modern" sql servers, is optimized to read and write data quickly. The indexes are left as they were at the time of backup.

share|improve this answer
add comment

The DB restore may de-fragment (physically) at the disk level, but there will still be the same internal fragmentation at the page level. Hence you will still need to do your index maintenance.

share|improve this answer
    
I don't think I can agree with this answer. Can you show some references or documentation defending your stance? –  Thomas Stringer Jul 13 '12 at 13:58
add comment

No, it doesn't work that way.

There are two layers at work here that you should be concerned with (there are more if you consider the underlying storage subsystem, but I'll ignore this here):

  1. Internal database data file layout: this is how data pages are located within each data file. When SQL Server needs to allocate some space, it does so by grabbing an available space within the data file. This can causes fragmentation within the data file, as these new allocations may be physically out of order to the logical structure of which they're a part. When SQL Server restores a backup, it reconstitutes the exact layout of pages within the data file as when they were backed up: therefore, this does not eliminate index fragmentation.

  2. Physical storage layout: this is how parts of the database data files are located on the storage device. SQL Server assumes that database data files themselves are perfectly defragmented on disk (because it would be ridiculous performance-wise to assume otherwise). Windows is free to make allocations wherever there is free space on disk. This is why it's recommended to not rely on file auto-growth: if you have many databases with auto-growth happening, the physical allocations can be all over the disk (i.e., fragmented).

Given these two factors, it's recommended to (a) run index maintenance procedures to eliminate the internal database data file layout fragmentation, and also (b) run Defrag at the Windows level to eliminate physical storage layout fragmentation*. Doing only one of the two does not eliminate the need for the storage device to do random I/Os.

Doing a restore as you suggest may eliminate the physical storage fragmentation, but it won't eliminate the internal data file fragmentation.

* Physical-level (and lower) defragmentation is not recommended for SSD-based storage.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.