[GUIDE] Migrating database from SQL Compact Edition to SQL Server 2008/2005

[Anesthetic]

Well, since I don't know how to use the wiki syntax I'll be posting here the small guide for other users to have future references on how to migrate database after configuring SambaPOS with SQL Compact Edition.

Problems faced:
- I tried to open the CE database using SQL Server Management Studio but the problem is that SambaPOS uses CE 2.0 database which is rather old for the SQL Server Management Studio to work with and suggests the user to "update" the database.
- Using third-party applications to update the database and then tried to open it with SQL Server Management Studio. Still no-go for the database version, the program didn't work.

Solution:
Here's a step by step guide on how to migrate files from SQL Compact Edition to SQL Server 2005 or 2008 Express.


[Creating the migration file]
1. Locate the file SambaPOS2.sdf file in SambaPOS folder in My Documents.
2. Copy it and paste it on your C:\ drive. The file should be located here "C:\SambaPOS2.sdf" (This is for easier migration)
3. Navigate to the SambaPOS installation folder (C:\Program Files\SambaPOS2) and locate the file ExportSqlCE40.exe inside the "Training" folder.
4. Copy the ExportSqlCE40.exe file to the same destination of your SambaPOS2.sdf file. (C:\ExportSqlCE40.exe)
5. Open a command prompt window (Windows key + R and type in CMD) and make sure you navigate to the folder where both files you copied in step 2 and 4 are pasted. (Type in "cd c:\" without quotations)
6. By this step you should see the command prompt with C:\
7. Next type this ExportSqlCE40.exe "Data Source=c:\SambaPOS2.sdf" migrate.sql
8. Let the program do it's job, after completion head to your C:\ drive and you should see a file named migrate.sql

[Restoring the database to SQL Server]
1. Open up SQL Server Management Studio Express and connect to your server.
2. Open up Databases and create a new database called "SambaData2" (If you already have it please DELETE it and create it again!)
3. Close SQL Server Management Studio and open the file "migrate.sql" created earlier and connect to the server.
4. IMPORTANT: MAKE SURE YOU SELECT ON THE DROPDOWN MENU "SAMBADATA2" LOCATED NEXT TO THE EXECUTE BUTTON!!!!!
5. Click Execute and your database should be restored.

If you have any questions feel free to ask them and I'll help as much as I can

Have a nice day!

[cnttech]

hi,
i tried but
drop down menu does not open, any idea why?
4. IMPORTANT: MAKE SURE YOU SELECT ON THE DROPDOWN MENU "SAMBADATA2" LOCATED NEXT TO THE EXECUTE BUTTON!!!!!
(i am using sql server managment studio Express 2005)

maybe i am using wrong sql server,

can you give me link to download for my xp pro windows?

regards

[JohnS]

At step 3, don't close SQL Server Management Studio, just find the migrate.sql and open it, and it should open in the SQL Server Management Studio.
Then select the Database from the drop down menu and click execute.

[tonishala]

Hi,
Im trying to migrate from SQL CE to SQL server management studio 2008, but while im trying to create migrate file a following message is showing: Access to the database file is not allowed.

Please help

Well, since I don't know how to use the wiki syntax I'll be posting here the small guide for other users to have future references on how to migrate database after configuring SambaPOS with SQL Compact Edition.

Problems faced:
- I tried to open the CE database using SQL Server Management Studio but the problem is that SambaPOS uses CE 2.0 database which is rather old for the SQL Server Management Studio to work with and suggests the user to "update" the database.
- Using third-party applications to update the database and then tried to open it with SQL Server Management Studio. Still no-go for the database version, the program didn't work.

Solution:
Here's a step by step guide on how to migrate files from SQL Compact Edition to SQL Server 2005 or 2008 Express.


[Creating the migration file]
1. Locate the file SambaPOS2.sdf file in SambaPOS folder in My Documents.
2. Copy it and paste it on your C:\ drive. The file should be located here "C:\SambaPOS2.sdf" (This is for easier migration)
3. Navigate to the SambaPOS installation folder (C:\Program Files\SambaPOS2) and locate the file ExportSqlCE40.exe inside the "Training" folder.
4. Copy the ExportSqlCE40.exe file to the same destination of your SambaPOS2.sdf file. (C:\ExportSqlCE40.exe)
5. Open a command prompt window (Windows key + R and type in CMD) and make sure you navigate to the folder where both files you copied in step 2 and 4 are pasted. (Type in "cd c:\" without quotations)
6. By this step you should see the command prompt with C:\
7. Next type this ExportSqlCE40.exe "Data Source=c:\SambaPOS2.sdf" migrate.sql
8. Let the program do it's job, after completion head to your C:\ drive and you should see a file named migrate.sql

[Restoring the database to SQL Server]
1. Open up SQL Server Management Studio Express and connect to your server.
2. Open up Databases and create a new database called "SambaData2" (If you already have it please DELETE it and create it again!)
3. Close SQL Server Management Studio and open the file "migrate.sql" created earlier and connect to the server.
4. IMPORTANT: MAKE SURE YOU SELECT ON THE DROPDOWN MENU "SAMBADATA2" LOCATED NEXT TO THE EXECUTE BUTTON!!!!!
5. Click Execute and your database should be restored.

If you have any questions feel free to ask them and I'll help as much as I can

Have a nice day!

[emre]

"Access to the database file is not allowed."

It looks like another program is accessing database file (maybe SambaPOS is still open) or you are working with a limited user account. Try doing these steps with a windows user that have administrator permissions or start command prompt as administrator (create a shortcut for cmd on desktop and right click on shortcut for starting it as an admin).