如何将Azure SQL Server导出为可重构PostgreSQL的.sql转储文件？

Hey there, since the Azure Portal's export tool is locked into Azure-native destinations, here are the easiest official methods to generate a PostgreSQL-compatible .sql dump from your Azure SQL Server:

This is Microsoft's official tool built specifically for migrating SQL Server databases to PostgreSQL, and it handles most syntax conversions automatically:

• Download and install SSMA for PostgreSQL (it's free and official).
• Launch SSMA, create a new project, set the target platform to PostgreSQL.
• Connect to your Azure SQL Server instance and select the database you want to export.
• Right-click the database in the SSMA explorer and select Convert Schema—this will turn SQL Server objects into PostgreSQL-compatible ones.
• After conversion, you can:
  1. Generate a PostgreSQL-compatible .sql script (right-click the converted database > Save As Script) containing both schema and data.
  2. Or directly migrate to a PostgreSQL instance, but since you want a dump file, the script option is what you need.
• SSMA takes care of common compatibility issues like converting IDENTITY columns to SERIAL/GENERATED AS IDENTITY, NVARCHAR to TEXT/VARCHAR, GETDATE() to CURRENT_TIMESTAMP, and removing SQL Server-specific keywords like GO.

If you prefer a lightweight, cross-platform tool, Azure Data Studio (Microsoft's official modern DB tool) works great with its migration extension:

• Install Azure Data Studio and the PostgreSQL Migration extension (from the extensions marketplace).
• Connect to your Azure SQL Server database in ADS.
• Open the Migration tab (left sidebar), then select Migrate to PostgreSQL.
• Follow the wizard:
  • Select your source Azure SQL database.
  • For the target, choose Generate script file instead of connecting to a live PostgreSQL instance.
  • Configure the output path for your .sql dump file.
• The wizard will automatically convert schema and data into PostgreSQL-compatible syntax, then save the complete dump file locally.

If you already use SQL Server Management Studio (SSMS), you can generate a SQL Server script first, then convert it:

1. In SSMS, connect to your Azure SQL Server, right-click the database > Tasks > Generate Scripts.
2. In the wizard:
   • Select the entire database or specific objects.
   • Go to Advanced settings, set Script Data to True (to include INSERT statements) and adjust other options like scripting indexes/triggers as needed.
   • Save the script to a local .sql file.
3. Convert the SQL Server script to PostgreSQL syntax:
   • Use SSMA (as in Method 1) to import the script and convert it, or
   • Manually fix common differences: replace square brackets [] with double quotes "", change TOP 10 to LIMIT 10, replace IDENTITY with PostgreSQL's identity syntax, remove GO statements, etc.

• Always test the generated .sql dump in a test PostgreSQL instance first to catch any edge-case compatibility issues (like stored procedures, which might need more manual adjustment).
• For large databases, consider using SSMA or Azure DMS (Azure Database Migration Service) to avoid performance issues with local tools.

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