Backup of Indexes for a particular table.
-
Hi guys,
Can anyone help me how to take backup of indexes for a particular table.
Thanks for your help
Regards,
SAM
Regards,
SAM
***Share your knowledge.It’s a way to achieve immortality----Dalai Lama*** -
Initial response, not possible.
Follow up question, however, what do you mean by a backup of the index?
-
the data of the index itself cannot be backed up, because it basically is just an organized set of pointers to the actual data records.
now if you mean how do i backup the definition of the indexes, so they can be dropped and recreated, or referenced, that is a lot easier. SSMS has the ability to script all the objects in a database or for a specific subset, or even just a single table, and you can make sure the checkbox for scripting options says "script indexes"
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Actually, you can (sort of) backup an index. "All" you need to do is define the (nonclustered) index upon its own file within its own filegroup, and then (when needed) you can backup just that filegroup (and thus 'just' that index). Of course, there are additional concerns when performing partial database restores (assuming the edition of SQL Server supports partial database restores).
However, I think backing up just an index would be a very odd thing to do, for almost all situations I can imagine. For example, creating an index is performed in a transitionally consistent manner - it is ok to kill an index creation (no corruption should result), as long as you can deal with the rollback of the killed create index statement. And nowadays, with online index creation being another possibility (assuming the edition supports online indexing), even the rollback should not be all that painful.
The bigger question for me is: Why do you want to backup an index? Perhaps there is an easier answer...
-
billhol 40227 (8/10/2014)
Actually, you can (sort of) backup an index. "All" you need to do is define the (nonclustered) index upon its own file within its own filegroup, and then (when needed) you can backup just that filegroup (and thus 'just' that index). Of course, there are additional concerns when performing partial database restores (assuming the edition of SQL Server supports partial database restores).Ok, but to what point? You wouldn't be able to restore the index that way without bringing the entire DB to the same point in time, either by restoring the entire DB to the time of the backup of the index filegroup, or applying log backups to bring the restored index filegroup up to the point of the rest of the DB. If the OP's asking this so that he can drop the indexes, do some operations (like an import) and then restore the indexes, a partial backup won't help at all.
All editions of SQL support partial restores, the only distinction is that Enterprise allows for an online restore (the rest of the DB is online when restoring a filegroup), while the others are offline restores. There's also the requirement that the DB be in full recovery and log backups exist otherwise the backups can't be restored.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Exactly, hence:
The bigger question for me is: Why do you want to backup an index?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply