带选择性表截断的SQL Server迁移自动化及测试技巧咨询

Hey there! I’ve helped a few teams with exactly this kind of Access-to-SQL Server migration setup, so let’s break down the steps and tricks to make your test migration and selective sync work smoothly.

First, get your initial test environment set up properly with SSMA:

• Create a baseline migration: Migrate all 5 Access databases to your test SQL Server first—this gives you a full reference environment to validate against. Double-check data type mappings (e.g., Access Memo → SQL Server varchar(max), Access Date/Time → datetime2) in SSMA’s project settings to avoid sync issues later.
• Save your SSMA project: Don’t skip this! Saving the project preserves your table mappings and configuration, so you won’t have to re-set everything up for subsequent syncs.
• Validate the baseline: Run quick checks like SELECT COUNT(*) FROM [TableName] on both Access and SQL Server tables to confirm row counts match, and spot-check key data values to ensure accuracy.

You don’t need to re-run a full migration every time—focus only on the tables that need daily updates:

Option 1: Use SSMA’s Custom Migration Scope #

• Open your saved SSMA project, then in the left-hand Access metadata pane, uncheck all tables except the specific ones from your daily-updated Access database.
• Go to Tools > Project Settings > Synchronization and change the default from "Append data" to "Drop and recreate tables" (this setting only applies to the tables you’ve selected).
• Click Migrate Data—SSMA will handle dropping the existing SQL tables, recreating them, and importing the latest data from Access automatically.

Option 2: Manual Script + Data Import (For More Control) #

If you prefer to avoid opening SSMA every time, build a reusable workflow:

1. Generate the Create Table script: In SSMA, right-click the target table and select Generate SQL Script—save this as a .sql file.
2. Add a Drop Table preamble: Prep a T-SQL script to safely drop the table if it exists:

   IF EXISTS (SELECT * FROM sys.tables WHERE name = 'YourTargetTable' AND schema_id = SCHEMA_ID('dbo'))
   DROP TABLE dbo.YourTargetTable;
   

   Combine this with the Create script from step 1 into a single sync script.
3. Import fresh data: Use the bcp command-line tool or SQL Server Import/Export Wizard to pull data from Access into the newly created table. For example, a bcp command might look like:

   bcp YourTestDB.dbo.YourTargetTable in "C:\Temp\DailyAccessTableExport.csv" -S YourSQLInstance -T -c -t,
   

   (You can automate the Access-to-CSV export with a VBA script in the Access database if needed.)

Since one database updates daily, automate this process to save time:

• SQL Server Agent Job:
  1. Create a new job in SQL Server Agent.
  2. Add a T-SQL step to run your Drop + Create script.
  3. Add a second step (either CmdExec for bcp or SSIS Package if you prefer visual data flows) to import the latest Access data.
  4. Schedule the job to run after the daily Access update (e.g., 2 AM when there’s minimal activity).
• PowerShell + Windows Task Scheduler:
  Write a PowerShell script that uses sqlcmd to run the Drop/Create script, then Write-SqlTableData to import data directly from Access. Use Windows Task Scheduler to trigger this script daily.

• Backup first: Always back up your test SQL database before running syncs—use this T-SQL command:

  BACKUP DATABASE YourTestDB TO DISK = 'C:\SQLBackups\YourTestDB_PreSync.bak' WITH INIT;
  

• Handle identity columns: If your tables have auto-incrementing IDs (Access AutoNumber → SQL Server IDENTITY), enable IDENTITY_INSERT before importing data:

  SET IDENTITY_INSERT dbo.YourTargetTable ON;
  -- Run your data import command here
  SET IDENTITY_INSERT dbo.YourTargetTable OFF;
  

• Lock tables during sync: Prevent test users from accessing tables mid-sync by wrapping the Drop/Create and import steps in a transaction, or setting table locks temporarily.

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