SQL Server: Database Export-Import Guide


This guide covers exporting data from Azure SQL to a local SQL Server instance, exporting to SQLite, and importing back.

Nb: Some scripts placed in GitHub/djaus2 repository: djaus2/SQLScripts_:

Table of Contents

  1. Prerequisites
  2. Azure SQL to Local SQL Server
  3. Local SQL Server to SQLite
  4. SQLite to Local SQL Server
  5. Connection String Configuration
  6. Troubleshooting

Prerequisites

Required Software

  • SQL Server (local instance named such as MYSQLSVR)
  • SQL Server Management Studio (SSMS) - for database management
  • SQL Server Data Tools (SSDT) - includes SqlPackage.exe (included with Visual Studio 2022)
  • sqlite3 command-line tool - for SQLite operations
  • PowerShell - for running automation scripts

Verify Installation

Check if SqlPackage.exe is installed:

Get-ChildItem "C:\Program Files\Microsoft Visual Studio" -Recurse -Filter "SqlPackage.exe"

Check if sqlite3 is installed:

sqlite3 --version

Azure SQL to Local SQL Server

Use the provided PowerShell script to automate the entire process.

File: BackupRemoteToLocal.ps1

Usage:

cd <To the scripts directory>
.\BackupRemoteToLocal.ps1

What the script does:

  1. Exports Azure SQL database to a .bacpac file
  2. Drops the existing local database (if exists)
  3. Imports the .bacpac to local SQL Server
  4. Cleans up temporary files

Script Configuration: The script uses these configuration variables (modify as needed):
Assuming same settings local and remote for simplicity, but you can change them as needed.

$remoteServer = "mydatabase.database.windows.net,1433"
$remoteDatabase = ""
$remoteUser = ""
$remotePassword = ""

$localServer = "127.0.0.1\MYSQLSVR"
$localDatabase = $remoteDatabase
$localUser = $remoteUser
$localPassword = $remotePassword
  • Change mydatabase to your Azure SQL server name.
  • Change MYSQLSVR to your local SQL Server instance name if different.

Method 2: Manual Export/Import

Export from Azure SQL to create .bacpac file

Using SqlPackage.exe:

SqlPackage.exe /Action:Export `
    /SourceServerName:$remoteServer `
    /SourceDatabaseName:$remoteDatabase `
    /SourceUser:$remoteUser `
    /SourcePassword:$remotePassword `
    /TargetFile:$remoteDatabase.bacpac

Import to Local SQL Server

Using SqlPackage.exe:

SqlPackage.exe /Action:Import `
    /SourceFile:$remoteDatabase.bacpac `
    /TargetServerName:$localServer `
    /TargetDatabaseName:$localDatabase `
    /TargetUser:$localUser `
    /TargetPassword:$localPassword `
    /TargetConnectionString:"Data Source=$localServer;Initial Catalog=$localDatabase;User ID=$localUser;Password=$localPassword;TrustServerCertificate=True;"

Local SQL Server to SQLite

Use the provided PowerShell script to generate a SQL script file.

File: ExportToSqliteSimple.ps1

Usage:

cd <to scripts folder>
.\ExportToSqliteSimple.ps1

What the script does:

  1. Connects to local SQL Server
  2. Exports all table schemas
  3. Converts SQL Server data types to SQLite equivalents
  4. Generates INSERT statements for all data
  5. Creates a .sql file with all statements

Output: A timestamped .sql file (e.g., MyDatabase_20260715_184041.sql)

Import to SQLite

Using PowerShell:

Get-Content MyDatabase_20260715_184041.sql | sqlite3 output.db

Using Command Prompt:

sqlite3 output.db < MyDatabase_20260715_184041.sql

Using DB Browser for SQLite (GUI):

  1. Download from: https://sqlitebrowser.org/
  2. Open DB Browser for SQLite
  3. File → Open Database → Create new database
  4. Database → Execute SQL → Select the .sql file
  5. Click Execute

Method 2: Direct .NET Export (Advanced)

For more control, use the ExportToSqlite.ps1 script which requires System.Data.SQLite.

Prerequisites:

  • Install System.Data.SQLite from: https://system.data.sqlite.org/

Usage:

.\ExportToSqlite.ps1

This creates a direct .sqlite file without needing to import via sqlite3.

SQLite to Local SQL Server

Method 1: Using DB Browser for SQLite

  1. Export from SQLite to SQL:
    • Open SQLite database in DB Browser
    • File → Export → Database to SQL file
    • Save as .sql file
  2. Modify SQL for SQL Server:
    • Open the exported .sql file in a text editor
    • Replace SQLite-specific syntax with SQL Server syntax:
      • INTEGER PRIMARY KEY AUTOINCREMENT → INT IDENTITY(1,1) PRIMARY KEY
      • TEXT → NVARCHAR(MAX) or appropriate data type
      • REAL → DECIMAL or FLOAT
      • Remove SQLite-specific pragmas
  3. Import to SQL Server:
    • Open SSMS
    • Connect to local SQL Server
    • File → Open → Open the modified .sql file
    • Execute the script

Method 2: Manual Data Transfer

For small datasets, you can manually transfer data using SSMS:

  1. Generate CREATE TABLE scripts from SQLite: 
    SELECT sql FROM sqlite_master WHERE type='table' AND name='YourTableName';

  2. Convert to SQL Server syntax and create tables

  3. Export data from SQLite to CSV: 
    sqlite3 yourdb.db ".headers on" ".mode csv" ".output data.csv" "SELECT * FROM YourTableName;" ".quit"
  4. Import CSV to SQL Server: 
    BULK INSERT YourTableName
FROM 'C:\path\to\data.csv'
WITH (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    FIRSTROW = 2
);

Connection String Configuration

appsettings.json

The project uses two connection strings for flexibility:

{
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=127.0.0.1\\MYSQLSVR;Initial Catalog=<local db name>;User ID=<local db user>;Password=<local db pwd>;TrustServerCertificate=True;",
    "ConnectionStringRemote": "Data Source=tcp:<remote db svr name>.database.windows.net,1433;Initial Catalog=<remote db name>;User ID=<remote db user name>;Password=<remote db pwd>;"
  }
}

Switching Between Local and Remote

For Local Development:

  • Use DefaultConnection (points to local SQL Server)

For Production/Remote:

  • Update code to use ConnectionStringRemote
  • Or swap the connection string values in appsettings.json

Connection String Parameters

Local SQL Server:

  • Data Source=127.0.0.1\MYSQLSVR2 - Server address and instance name
  • Initial Catalog=HelperLog200 - Database name
  • User ID=djaus - SQL Server authentication username
  • Password=Amyliz2o24sql - SQL Server authentication password
  • TrustServerCertificate=True - Bypass SSL certificate validation (for development)

Azure SQL:

  • Data Source=tcp:sportronics.database.windows.net,1433 - Azure SQL server with TCP protocol
  • Initial Catalog=HelperLog200 - Database name
  • User ID=djaus - Azure SQL username
  • Password=Amyliz2o24sql - Azure SQL password

Troubleshooting

SQL Server Connection Issues

Error: “A network-related or instance-specific error occurred”

Solutions:

  1. Ensure SQL Server service is running: 
    Get-Service 'MSSQL$MYSQLSVR2'
  2. Ensure TCP/IP is enabled (requires SQL Server Configuration Manager):
    • Open SQL Server Configuration Manager
    • SQL Server Network Configuration → Protocols for MYSQLSVR2
    • Enable TCP/IP
    • Restart SQL Server service
  3. Ensure SQL Server Browser service is running: 
    Set-Service SQLBrowser -StartupType Automatic
Start-Service SQLBrowser

Error: “The certificate chain was issued by an authority that is not trusted”

Solution: Add TrustServerCertificate=True to connection string.

SqlPackage Issues

Error: “SqlPackage.exe not found”

Solution: The script searches multiple paths. If not found:

  1. Install SQL Server Data Tools (SSDT)
  2. Or update the $sqlPackagePaths array in the script with your installation path

Error: “‘TrustServerCertificate’ is not a valid argument for the ‘Import’ action”

Solution: Use /TargetConnectionString instead of individual parameters:

/TargetConnectionString:"Data Source=server;Initial Catalog=db;User ID=user;Password=pass;TrustServerCertificate=True;"

SQLite Import Issues

Error: “ParserError” when using < redirection in PowerShell

Solution: Use PowerShell syntax:

Get-Content file.sql | sqlite3 output.db

Or use cmd.exe:

cmd /c "sqlite3 output.db < file.sql"

Error: Data type conversion issues

Solution: The script automatically converts SQL Server types to SQLite equivalents. If you encounter issues:

  • Check the type mapping in ExportToSqliteSimple.ps1
  • Modify the mapping as needed for your specific data types

Database Already Exists

When importing to local SQL Server, if the database already exists:

The BackupRemoteToLocal.ps1 script automatically drops the existing database before import. If you need to preserve data:

  1. Rename the existing database in SSMS
  2. Or modify the script to skip the drop step
  3. Or manually drop the database before running the script

Automation Scripts Reference

BackupRemoteToLocal.ps1

Purpose: Export Azure SQL database to local SQL Server

Location: Project root directory

Key Features:

  • Automatic SqlPackage.exe detection
  • SSL certificate handling
  • Automatic database cleanup
  • Progress reporting

ExportToSqliteSimple.ps1

Purpose: Export local SQL Server to SQL script for SQLite

Location: Project root directory

Key Features:

  • No additional dependencies required
  • Automatic type conversion
  • Batch writing for large datasets
  • Handles NULL values and string escaping

ExportToSqlite.ps1

Purpose: Direct export to .sqlite file (requires System.Data.SQLite)

Location: Project root directory

Key Features:

  • Creates SQLite database directly
  • No intermediate SQL file
  • Requires System.Data.SQLite installation

Best Practices

  1. Backup Before Operations: Always backup your databases before export/import operations
  2. Test in Development: Test scripts on non-production databases first
  3. Verify Data Integrity: After import, verify row counts and key data
  4. Secure Credentials: Store passwords securely (consider using environment variables or Azure Key Vault for production)
  5. Document Changes: Keep track of schema changes between exports
  6. Schedule Regular Backups: Set up automated backups for critical data

Additional Resources

Quick Reference

Export Azure to Local:

.\BackupRemoteToLocal.ps1

Export Local to SQLite:

.\ExportToSqliteSimple.ps1
Get-Content output.sql | sqlite3 output.db

Check SQL Server Service:

Get-Service 'MSSQL$MYSQLSVR2'

Restart SQL Server Service:

Restart-Service 'MSSQL$MYSQLSVR2'

Check SQL Browser Service:

Get-Service SQLBrowser

Start SQL Browser Service:

Start-Service SQLBrowser
 TopicSubtopic
<  Prev:   IoT Hub
   
 This Category Links 
Category:Database Index:Database