如何在SQL Server中复制分区表？跨库重建遇方案/函数获取难题

Got it, let's break down how to replicate that heavily partitioned table (1347 partitions across 35 filegroups) since SSMS is dropping the ball on grabbing the partition function and scheme in its default script. I've dealt with this exact scenario before—here's the step-by-step playbook:

Step 1: Extract Partition Function & Scheme Definitions #

SSMS's default table script often skips these critical partitioning components, so we'll query the system catalogs to pull their full, executable definitions.

• Get Partition Function Scripts:
  Run this query to generate the CREATE PARTITION FUNCTION statement for every function tied to your table:

  SELECT 
    'CREATE PARTITION FUNCTION ' + QUOTENAME(pf.name) + '(' + 
    tp.name + ')' +
    ' AS RANGE ' + CASE pf.boundary_value_on_right WHEN 1 THEN 'RIGHT' ELSE 'LEFT' END +
    ' FOR VALUES (' + 
    STUFF((SELECT ', ' + CONVERT(varchar(100), rv.value)
           FROM sys.partition_range_values rv
           WHERE rv.function_id = pf.function_id
           ORDER BY rv.boundary_id
           FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 2, '') +
    ');' AS PartitionFunctionScript
  FROM sys.partition_functions pf
  JOIN sys.partition_parameters pp ON pf.function_id = pp.function_id
  JOIN sys.types tp ON pp.system_type_id = tp.system_type_id
  JOIN sys.partition_schemes ps ON pf.function_id = ps.function_id
  JOIN sys.indexes i ON ps.data_space_id = i.data_space_id
  JOIN sys.tables t ON i.object_id = t.object_id
  WHERE t.name = 'YourTableName' -- Replace with your actual table name
  GROUP BY pf.name, tp.name, pf.boundary_value_on_right;
  

• Get Partition Scheme Scripts:
  This query generates the CREATE PARTITION SCHEME statement, which maps each partition to its correct filegroup:

  SELECT 
    'CREATE PARTITION SCHEME ' + QUOTENAME(ps.name) +
    ' AS PARTITION ' + QUOTENAME(pf.name) +
    ' TO (' + 
    STUFF((SELECT ', ' + QUOTENAME(fg.name)
           FROM sys.destination_data_spaces dds
           JOIN sys.filegroups fg ON dds.data_space_id = fg.data_space_id
           WHERE dds.partition_scheme_id = ps.data_space_id
           ORDER BY dds.destination_id
           FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 2, '') +
    ');' AS PartitionSchemeScript
  FROM sys.partition_schemes ps
  JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
  JOIN sys.indexes i ON ps.data_space_id = i.data_space_id
  JOIN sys.tables t ON i.object_id = t.object_id
  WHERE t.name = 'YourTableName' -- Replace with your actual table name
  GROUP BY ps.name, pf.name;
  

Step 2: Generate Table Structure with Partitioning #

Use SSMS to get the base table script, then tweak it to include partitioning:

1. Right-click your source table in SSMS > Script Table as > CREATE To > New Query Editor Window.
2. In the generated script, find the ON [PRIMARY] clause and replace it with your partition scheme, pointing to your partitioning column. For example:

   CREATE TABLE [dbo].[YourTableName]
   (
       -- Keep all your original column definitions here
       SaleDate DATE NOT NULL -- This is your partitioning column
   ) ON [YourPartitionScheme](SaleDate); -- Swap in your scheme and column
   

3. Don’t forget indexes! If your source indexes were partition-aligned, update their scripts to use ON [YourPartitionScheme](SaleDate) too (instead of ON [PRIMARY]).

Step 3: Copy Data to the New Partitioned Table #

For a table this large, efficiency is key. Here are your best options:

• Partition Switching (Fastest Option):
  If you can take the source table offline temporarily, this is the way to go. For each partition:

  1. Create a staging table that matches the source table’s structure exactly.
  2. Switch the source partition to the staging table:

     ALTER TABLE SourceDB.dbo.SourceTable SWITCH PARTITION 1 TO StagingTable;
     

  3. Switch the staging table to the target partition in the new database:

     ALTER TABLE StagingTable SWITCH TO TargetDB.dbo.TargetTable PARTITION 1;
     

  Note: This requires identical table structures, aligned partitions, and no active foreign keys pointing to the source table during the switch.

• Batch INSERT...SELECT:
  If you can’t use switching, break data into batches to avoid locking and transaction log bloat:

  DECLARE @BatchSize INT = 100000;
  DECLARE @MaxID INT = (SELECT MAX(TransactionID) FROM SourceDB.dbo.SourceTable);
  DECLARE @CurrentID INT = 0;
  
  WHILE @CurrentID < @MaxID
  BEGIN
      INSERT INTO TargetDB.dbo.TargetTable
      SELECT * FROM SourceDB.dbo.SourceTable
      WHERE TransactionID > @CurrentID AND TransactionID <= @CurrentID + @BatchSize;
  
      SET @CurrentID += @BatchSize;
      CHECKPOINT; -- Helps reduce transaction log growth
  END
  

• bcp/BULK INSERT:
  Export each partition’s data with bcp, then bulk insert into the target. Example export command:

  bcp "SELECT * FROM SourceDB.dbo.SourceTable WHERE SaleDate BETWEEN '2023-01-01' AND '2023-01-31'" queryout "Partition1_Data.bcp" -S YourServerName -T -n
  

  Then bulk insert into the target:

  BULK INSERT TargetDB.dbo.TargetTable
  FROM 'Partition1_Data.bcp'
  WITH (DATAFILETYPE = 'native');
  

Step 4: Verify Everything’s Correct #

Don’t skip this—confirm partitions are mapped right and data is intact:

• Check partition counts and filegroup mappings:

  SELECT 
    p.partition_number,
    fg.name AS FileGroupName,
    COUNT(*) AS RowCount
  FROM TargetDB.dbo.TargetTable t
  JOIN sys.partitions p ON t.object_id = p.object_id
  JOIN sys.destination_data_spaces dds ON p.partition_number = dds.destination_id
  JOIN sys.filegroups fg ON dds.data_space_id = fg.data_space_id
  GROUP BY p.partition_number, fg.name
  ORDER BY p.partition_number;
  

• Compare row counts between source and target:

  SELECT 
    (SELECT COUNT(*) FROM SourceDB.dbo.SourceTable) AS SourceRowCount,
    (SELECT COUNT(*) FROM TargetDB.dbo.TargetTable) AS TargetRowCount;
  

内容的提问来源于stack exchange，提问作者Elsimer