0

Let me start off by saying I am not a SQL guru, I know just enough to get into trouble with it :) We just migrated a client over from SQL 2008 R2 to SQL 2016 on new hardware and we want to move the indexes over to a different partition that is on faster disk than the databases.
There are about 40 databases with a couple of dozen tables and indexes on each table Some indexes are clustered, others are non-clustered. Is there a way to move all the indexes in bulk? Something similar to this http://sqlsrvscrpt.codeplex.com/wikipage?title=DBScript%20Utility&referringTitle=Home to this, unfortunately this tool won't work with SQL 2016.

Thanks in advance!

msindle
  • 605
  • 8
  • 26

1 Answers1

0

Are you indexes already stored outside of the main mdf data file? Usually SQL Databases are divided up into a primary data file (.mdf), a log file (.ldf) and sometimes secondary data files (.ndf). The table data itself is also a type of index, so it sounds like you just want to move the data to a new disk. The easiest way to do that is to detach the database, move the data files wherever you want them, then reattach. This can be done via ssms by right clicking the database, then clicking Tasks->Detach, and then right clicking the Databases folder and clicking "Attach" to reattach it in its new location. You could also use the sp_detach_db and sp_attach_db stored procedures.

https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-detach-and-attach-sql-server

Jason
  • 58
  • 8
  • Right now the indexes are part of the main mdf file, and we are wanting to separate them to an ndf file on the faster disk – msindle Mar 29 '17 at 20:15
  • How would separation be helpful? Clustered indexes are the table data for any table with a clustered index, as the clustered index defines what order to store the actual rows in, otherwise the data is in a Heap type index for tables with only nonclustered indexes, or without indexes. Either way you're basically just moving all the table data out of the file. – Jason Mar 29 '17 at 20:28