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.

You seriously want to store that kind of data into a single file and a single-user, file-based database system?? — marc_s, Sep 26 '14 at 10:53
Max db size on SQL Compact is 4 GB.. And 50 GB with SQLite is simply not feasible — ErikEJ, Sep 26 '14 at 10:56
It's going to be hard. Max size for a sql compact db is 4gb. http://stackoverflow.com/questions/7524197/does-sql-server-compact-still-limit-to-4gb — John Babb, Sep 26 '14 at 10:57
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... — Ronan Lamour, Sep 26 '14 at 11:43
According to this message [here](http://stackoverflow.com/questions/54998/how-scalable-is-sqlite#comment230095_66724), reading on a 50GB sqlite file is not a problem and that's exactly what I would like to do. — Ronan Lamour, Sep 26 '14 at 11:48

ErikEJ · Accepted Answer · 2016-12-27T13:37:14.307

29

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

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)

edited Dec 27 '16 at 13:37

answered Sep 26 '14 at 12:11

ErikEJ

I'm going to test this today.I'll mark my question as answered if it works. – Ronan Lamour Sep 29 '14 at 07:06
After some tests. I've done it and reading from sqlite database is very fast. Thank you :) – Ronan Lamour Oct 02 '14 at 14:30
2

This project moved to GitHub: https://github.com/ErikEJ/SqlCeToolbox probably should update answer – Greg Ennis Jul 03 '16 at 15:08
Worked painless with the SSMS Extension. Great great work. – VapoRizer Jan 17 '19 at 04:52
@jjxtra ude the GUI tool to do that – ErikEJ Jul 27 '19 at 20:09

score 3 · Answer 2 · answered Jun 02 '22 at 14:33

3

I'm using this software from Code Project:

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

answered Jun 02 '22 at 14:33

js2010 · Answer 3 · 2022-02-26T17:35:13.863

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);

3 Answers3