Best way to export SQL Server database to sqlite (or SQL Server Compact)

Question

Do someone know what would be the best way to transform a big SQL Server database to sqlite or SQL Server compact ?

SQL Server database should be around 50-70Gb.

There are some examples on internet (script schema) but I didn't find anything concluding about data.

Antebellum

You seriously want to store that kind of data into a single file and a single-user, file-based database system?? – Luscious

Max db size on SQL Compact is 4 GB.. And 50 GB with SQLite is simply not feasible – Illconsidered

It's going to be hard. Max size for a sql compact db is 4gb. #7524697 – Trishtrisha

My problem is : I must send an offline app who read a database this size. Atm, I use vmware portable with windows+sql server embedded on an external drive. I would like to remove vmware (because of windows+SQL), so I thought about sqlite/sql compact. If you have any other idea... – Antebellum

According to this message here, reading on a 50GB sqlite file is not a problem and that's exactly what I would like to do. – Antebellum

But the stated problem is 50-70GB - not exactly 50GB. – Echt

Answer 1

You can use my Exportsqlce tool to create .sql files in sqlite format from a SQL Server database. Then run them using sqlite3.exe

Download command line tools from https://github.com/ErikEJ/SqlCeToolbox/releases/tag/3.5.2

Export2SQLCE.exe "Data Source=(local);Initial Catalog=Northwind;Integrated Security=True" Northwind.sql sqlite

Good luck!

(I also have SSMS and VS extensions that allow you to do this from a GUI)

Illconsidered

I'm going to test this today.I'll mark my question as answered if it works. – Antebellum

After some tests. I've done it and reading from sqlite database is very fast. Thank you :) – Antebellum

This project moved to GitHub: github.com/ErikEJ/SqlCeToolbox probably should update answer – Freedom

Worked painless with the SSMS Extension. Great great work. – Torero

@jjxtra ude the GUI tool to do that – Illconsidered

Answer 2

I'm using this software from Code Project:

Convert SQL Server DB to SQLite DB

It connects with your SQL Server and opens Databases to select and select file destination

Lessard

Answer 3

I just did this too with SqlCeToolbox, actually through the SSMS (Sql Server Management Studio) gui — Export my SQL db to SQLite db in SSMS · Issue #327 · ErikEJ_SqlCeToolbox · GitHub. But it made over 100 .sql script files I had to pipe to sqlite3.

# powershell
dir *.sql | sort name | % { "$_"; cat $_ | sqlite3 newdatabase.db } 

It worked except for 3 parse errors, but I only lost 3 rows. Somehow directly making the sqlite db failed.

By the way, searching the script file dump is a create way to search every table. Search Sqlite Database - All Tables and Columns

sqlite3 database.db .dump | select-string whatever

INSERT INTO mytable VALUES(0,0,0,'whatever',0);

Provocation