How to convert PostgreSQL database to SQL Server

Microsoft SQL is one of the world's most popular database management systems with user-friendly interface that is easy to learn and use. However, it has certain disadvantages such as high cost of ownership for large databases and strict licensing policy. These facts may lead some users to the need of shifting from SQL Server to another DBMS.

PostgreSQL to SQL Server Migration Steps

Here is the sequence of steps to migrate database from PostgreSQL to Microsoft SQL:

• export PostgreSQL table definitions using pg_dump -s ...
• convert the resuling statements into SQL Server format
• load the resulting statements to SQL Server using Import and Export Wizard or command line client tool sqlcmd.exe
• export PostgreSQL data into a script using pg_dump -a -b --inserts ...
• convert it into SQL Server format and load into the target database

The most important part of database migration is to handle properly all differences between PostgreSQL and SQL Server before importing scripts into the target database:

• take care about correct type mapping
• replace double quotes around database object names by square brackets
• replace default PostgreSQL schema "public" by SQL Server "dbo"
• insert the SQL Server statement terminator "GO" after each statement
• migrate PostgreSQL views into SQL Server format using this guide
• translate PostgreSQL triggers into SQL Server format

Automate PostgreSQL to SQL Server Migration

As you may see, database migration from PostgreSQL to Microsoft SQL is not a trivial task. Manual conversion requires handlng all differences between two DBMS that becomes extremely slow for large and complicated databases. It is reasonable to use special tools automating database migration from PostgreSQL to SQL Server with just a few clicks of mouse button. Intelligent Converters offer one of such solutions - PostgreSQL to SQL Server converter.