0

I've written quite a long C# program using Linq to Sql and my data are stored in an MDF file located near my program's EXE. A part of my program has a form for backing up the database files simply by copying the MDF and LDF files into a user-specified folder.

However if I query the database and then try to replace the original files with the backups, I'll get the file open in another process exception as expected! The problem is that I don't know how to close the MDF file in the SqlServer instance.

I'm pretty new to Linq to Sql and I let the Visual Studio's wizards to handle most of the job! So I'm sorry in advance if anything I'm trying to do sounds stupid! :D

Any help or suggestion for better programming methods for my case is greatly appreciated.

Improve this question
2
  • Is this a permanently-attached database using SQL Server, or a single-instance database using SQL Server Express? Commented Oct 29, 2010 at 20:40
  • It's a single instance database using SQL Server Express. The program is not supposed to be running all the time or on several computers at once. Commented Oct 30, 2010 at 10:47

2 Answers 2

Reset to default
1

This may not work for your needs, but you might want to consider switching your application to use Sql Server Compact Edition ("SqlCE") instead of Sql Server (proper). It sounds like you're using the database as essentially a backing file for your application, which isn't really the intended purpose of Sql Server. SqlCE is designed specifically for this kind of thing, and works great on the desktop. You can easily close your connection to SqlCE and manipulate the SDF file like any other file (what you're trying to do with the Sql Server MDF file, unsuccessfully).

Update: This looks like what you need:

USE master 
GO 
ALTER DATABASE YourDatabaseName 
SET OFFLINE WITH ROLLBACK IMMEDIATE 
GO

(from this answer)

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Nope! The database plays an important part in my application. The application is supposed to manage all the accounting and ordering needs for a fairly small company. That's why I couldn't think of anything better than SQL Server Express. SQLite or SQLCE were not an option for me.
It looks like from SQL Management Studio you can type alter database my_database_name set offline. I'm not sure how this could be done programmatically, however.
FYI, SqlCE can handle extremely large sets of data in a highly performant manner. It's not really thread-safe, however, so the only thing that would make it unsuitable for your purposes would be if you had multiple instances of your client EXE attempting to write to the same SDF file at the same time.
Thanks for the answer. I didn't know SqlCe can handle large ammount of data. Regarding the above query, how should I actually use it with my LinqToSql classes? Sorry if it seems a dumb question! Like I said, I'm new to L2S...
@M2X: Sorry, I don't know Linq at all. I think the statement could be directly executed using ADO.NET (with a SqlCommand object).
|
0

You need to stop the SQL server or take the database offline before copying the files.

Improve this answer

1 Comment

Thanks, but how am I going to do that? And how can I restart SQL Server after replacing the file?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.