none
SQL server compact edition: Is there a way that we can export and import ~.sdf file?

    問題

  • our application needs to backup and restore feature for the SQLCE file (~.sdf) duruing the application running.

    See the following thread for more information.

    http://social.msdn.microsoft.com/Forums/en-US/sqlce/thread/522e1683-343d-4c5a-96c2-50930ff13dab

    So far we found out during File.Copy(xxx.sdf, backup.sdf, true) and try to access xxx.sdf will cause application crashes. It seems that SQLCe does not fully support online/hot backup (??)

    Our application request that backup and store is handled without user notice (backgound thread and no UI indication)

    Our application can handle not write data to the database (xxx.sdf) during backup. However, we can not handle read data from database (xxx.sdf) during backup. Becuse reading or loading data is base on UI application requrest. we do not want to show user that during backup, UI feature is not available.

    ErikEJ has suggested to use

    You could scrípt (dump) all schema and data using my ExportSqlCe command line utility http://exportsqlce.codeplex.com/ (or use the containing code). This can be done while the application is running.

    This only solve export data from xxx.sdf to xxx.sql.

    How can we base on export data xxx.sql import it (restore it) as yyy.sdf?

    we did follow the link: http://blogs.msdn.com/b/stevelasker/archive/2007/03/31/creating-your-sql-server-compact-edition-database-and-schema-in-code.aspx

    however, we can not directly run the script (xxx.sql) which is generated by exportsqlce.exe.

    We need to modify the script (xxx.sql) and add them in the resource files and in order to run script. This is not directly import from script.

    we have been searched, we did not find a good solution for it?

    If anyone know how to solve import, let us know.

    If there is no solution for directly importing, the second choice is during copying/backuping file, it allows to read data as well. This means we will open the file and read block and write block into another file. Does anyone see any problem with it. Is there are similiar example we can follow?

    thanks in advanced! 

     


    JaneC
    2011年2月7日 下午 08:23

解答

  • See this blog post: http://erikej.blogspot.com/2010/02/how-to-use-exportsqlce-to-migrate-from.html 

    (Use exportsqlce.exe instead of export2sqlce.exe, of course)


    Visit my SQL Compact blog - http://erikej.blogspot.com - Please mark as answer, if this was it.
    • 已標示為解答 JJChen 2011年2月12日 下午 09:25
    2011年2月8日 上午 10:05
  • If you really don't want to use the SQL files as backup there's a faster solution for your problem:

    Always have two databases - MainDB and ReplicateDB

    Every read command is done on MainDB and every write/update command is done on both.

    When it's time to do a backup simply:

    1. hold the write/update commands on the ReplicateDB
    2. do a file copy of ReplicateDB as BackupDB
    3. apply any write/update commands that have been on hold to ReplicateDB

    When it's time to restore you really should hold the ui completly as someone might attempt to introduce data that no longer complies with the state of the DB but if you don't want to do that then simply:

    1. hold write/update commands on the ReplicateDB and MainDB
    2. replace ReplicateDB with BackupDB
    3. apply any write/update commands that have been on hold to ReplicateDB (some could potentially fail - no way around this as data is surelly missing from previous state)
    4. make ReplicateDB your main database (all read commands executed on ReplicateDB)
    5. replace MainDB with BackupDB
    6. apply any write/update commands that have been on hold to MainDB (some could potentially fail - no way around this as data is surelly missing from previous state)
    7. make MainDB your main database (all read commands executed on MainDB as normal)

    Luís Rodrigues


    • 已編輯 537mfb 2012年5月18日 上午 11:04
    • 已標示為解答 JJChen 2012年5月18日 下午 03:29
    2012年5月18日 上午 11:00

所有回覆

  • See this blog post: http://erikej.blogspot.com/2010/02/how-to-use-exportsqlce-to-migrate-from.html 

    (Use exportsqlce.exe instead of export2sqlce.exe, of course)


    Visit my SQL Compact blog - http://erikej.blogspot.com - Please mark as answer, if this was it.
    • 已標示為解答 JJChen 2011年2月12日 下午 09:25
    2011年2月8日 上午 10:05
  • Hi ErikEJ,

    Thanks for replying our question. At least we know there is a way that we can do export into scrip and then import base on script to handle SQLCE file backup.

    Our application does not choose to do this way because we think it may take too long to export into script and then import script back.

    File.Copy for file backkup makes more sense in our application (it takes 2~10 ms take copy 200MB file).

    We found a solution that use "Shared read" for file mode in the connection string, it allows the application do hot copy and at the same time access the SQLCE database throught entity framework.

     


    JaneC
    2011年2月12日 下午 09:31
  • If you really don't want to use the SQL files as backup there's a faster solution for your problem:

    Always have two databases - MainDB and ReplicateDB

    Every read command is done on MainDB and every write/update command is done on both.

    When it's time to do a backup simply:

    1. hold the write/update commands on the ReplicateDB
    2. do a file copy of ReplicateDB as BackupDB
    3. apply any write/update commands that have been on hold to ReplicateDB

    When it's time to restore you really should hold the ui completly as someone might attempt to introduce data that no longer complies with the state of the DB but if you don't want to do that then simply:

    1. hold write/update commands on the ReplicateDB and MainDB
    2. replace ReplicateDB with BackupDB
    3. apply any write/update commands that have been on hold to ReplicateDB (some could potentially fail - no way around this as data is surelly missing from previous state)
    4. make ReplicateDB your main database (all read commands executed on ReplicateDB)
    5. replace MainDB with BackupDB
    6. apply any write/update commands that have been on hold to MainDB (some could potentially fail - no way around this as data is surelly missing from previous state)
    7. make MainDB your main database (all read commands executed on MainDB as normal)

    Luís Rodrigues


    • 已編輯 537mfb 2012年5月18日 上午 11:04
    • 已標示為解答 JJChen 2012年5月18日 下午 03:29
    2012年5月18日 上午 11:00