August 26, 2008 at 2:27 am
Hi,
I was wondering, I assume SQL is storing used pages during a full backup, including indexes. Shoudn't it be a handy option to exclude non-clustered indexes from this backup, but just the index definition? This will save some space and increase the backup time.
Yes, it will decrease your restore time, because SQL has to create the indexes, but your database can be online as soon as the data is restored (creating indexes in the background)
Maybe an idea for SQL 2010, or am I reinventing something?
Note: If you separate your data from indexes (by using different filegroups), you can do this trick by a filegroup backup. but I don't think this is a base for a full recovery.
Wilfred
The best things in life are the simple things
August 26, 2008 at 5:11 am
You're not the first to think of this. 🙂
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=331220
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=351043
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
August 26, 2008 at 5:45 am
Thanks for these URLs. Interesting discussions
Wilfred
The best things in life are the simple things
August 26, 2008 at 10:08 am
The storage engine team has commented on this to MVPs and they do not plan on doing this. The backup is optimized for speed and this would slow it down substantially. I'm not sure of the specific reason (it's been awhile), but from what I remember, they weren't thinking this would be a good idea.
Someone was trying this with filegroup backups with indexes moved to a new filegroup.
August 26, 2008 at 10:30 am
Out databases use a ton of Clustered Indices. It would be really handy to exclude the Indexes in favor of just holding onto the code that creates the Indices to save space.
Some of our databases are over 100 GB now, not huge, but certainly large enough to cause concern. Anything I can do to conserve space would be great. Adding more time to the recovery of a system is not an issue since we can just let the users know that it will take longer. At the most, creating all of the Indices on all of the Tables should not add much more than 45 mins to an hour in my guesstimation.
Regards, Irish
August 26, 2008 at 2:05 pm
Jeffrey Irish (8/26/2008)
Out databases use a ton of Clustered Indices. It would be really handy to exclude the Indexes in favor of just holding onto the code that creates the Indices to save space.
The suggestion was to not backup the nonclustered indexes because they can be recreated. Since the clustered index is the table, that kinda does need to be backed up.
Some of our databases are over 100 GB now, not huge, but certainly large enough to cause concern. Anything I can do to conserve space would be great. Adding more time to the recovery of a system is not an issue since we can just let the users know that it will take longer.
You have nice users. In the couple of cases where I've had to restore a production database, getting it recovered fast was critical because while the system is down, the business is losing money
Honestly, I'd prefer slightly slower backups and a faster restore time. Backup windows can be played with - differential backups, compressed backups, filegroup backups, but when the business is losing a million dollars an hour that the system's down, having the restore take an extra 30 min is not exactly ideal.
At the most, creating all of the Indices on all of the Tables should not add much more than 45 mins to an hour in my guesstimation.
Depends how big your database is.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply