SQLExpress, LINQ, Backup/Restore

[Borvik]

I'm developing an app, where I created the database within the VB2008 Express IDE using SQL Express. I've tied it to the application using the DBML visual designer and the application is working pretty well with queries being done using LINQ.

I'm running into a problem though. I want to be able to backup and restore the data. I know I could just copy the .mdf and .ldf files to the target computer, but that is clumsy.

I can't just have the program copy the files either, because of the way the ClickOnce installer locates the files. During debug the mdf and ldf files are located in the Debug directory, however after installing using ClickOnce they are no longer located in the same path as the executable, but rather some other Data directory located somewhere under the AppData folder. Not to mention trying to read the mdf file (even after I called DataContext.Connection.Close) caused it to through an exception showing that the file was in use by another process.

I was able to loop through a table and serialize data using an intermediary class (the generated classes are not serializable) and retrieve the serialized data - but though I could retrieve the data, re-inserting it posed a problem. The Identity columns were off which would break relationships between tables.

Does anybody know how to have your application backup and restore it's LINQed SQL Express database? Or possibly to INSERT Identity columns?

Thanks.

 

[RiverGuy]

You can easily backup a SQL Express database in T-SQL.

Here's a starting point.

http://msdn.microsoft.com/en-us/library/ms186865.aspx

 

[Borvik]

Unfortunately the DataContext that the visual DBML designer creates isn't so nice to call a regular SQL statement (or T-SQL) on - it's great though for using LINQ queries and tightly integrating into your app though.