Tell me more ×
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.
up vote 0 down vote favorite

I need to automate and Sync only indexes between two tables ( Primary and Stage_Table) within same database. Tried using SSIS SQL Server Objects Task, but looks like it works only when we sync between two different instances. Indexes on Primary table keep getting changed freq and i need the empty stage_table with all the updated indexes when i do the partition switch.

Thanks,

share|improve this question
1  
I'm sorry I don't understand what you're trying to do. "I need the empty stage_table with all the updated indexes" - what does this mean? Is staging empty or not? What data is it supposed to contain exactly? "partition switch" - What partition? From where? Switch with what and what for? It might be easier to understand your exact scenario if you posted example schema of your main & staging tables, and the process you're trying to get to work. – Mat May 1 at 7:10
So we have Table_A and Tabl_A_Stage within the same database. I need to automate and only sync indexes from Table_A to Table_A_Stage. The stage table is part of a partition mgmt job, and i have all aligned indexes on Table_A and need to have same indexes on Stage table to do the switch – Amam May 1 at 20:23
1  
please edit your question to clarify it. I still don't understand what you're doing from your previous comment (but I don't know SQL Server well some maybe I'm missing something obvious). As I said, show an example of exactly what you want is the best way to clarify. – Mat May 2 at 5:00

1 Answer

up vote 2 down vote

You can use opensource tool like sql-dbdiff or OpenDBDiff. Both are commandline, so can be used in automating scripts.

Also, if you want 3rd party licensed tool then Redgate's SQL Compare (if u want for data compare -- there is data compare as well) is very useful and I have used it extensively for automation.

Out of curiosity, why do you need Indexes on Staging table as a staging table is meant for temporary loading data and then after cleaning it, the data gets loaded in the primary table ?

EDIT :

Based on your need, best will be to use SQL Server Partition Management Tool from Codeplex.

This utility provides a command line interface to: 1. Remove all the data from one partition by switching it out to a staging table. It creates the required staging table.2. Create a staging table for loading data into a partition. The staging table can be created with or without indexes -- if created without indexes this utility provides a separate command to create appropriate indexes on the staging table, before SWITCHing it into the partitioned table. The commands can be invoked from other scripts for end-to-end sliding window scenarios. Using the utility allows you to avoid maintaining partition maintenance scripts that must remain synchronized with index or column changes in the permanent table, since necessary staging objects can be created on-demand.

share|improve this answer
Kin, I need to setup a partition mgmt job, to move data between one set of partition tables to their corresponding Stage tables and then drop indexes from the stage tables and recreate indexes on a diffrent filegroup and then plug in the stage tables to a differnt set of partition tables (which reside on differnt FG's) – Amam May 1 at 20:17
@Amam I have edited my answer as you want to move data between different partitions, the Partition Management tool will be best suited. – Kin May 1 at 20:27
At this point any third party tool is ruled-out, hence the need for a way to script it. – Amam May 1 at 21:54
@Amam if you see my edited answer, I have pointed out a link to SQL Server Partition Management Tool from Codeplex which will be the best fit for your scenario. – Kin May 1 at 23:24

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.