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.

animuson
  • 53,861
  • 28
  • 137
  • 147
Maghoumi
  • 3,295
  • 3
  • 33
  • 49
  • Is this a permanently-attached database using SQL Server, or a single-instance database using SQL Server Express? – Aaronaught Oct 29 '10 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. – Maghoumi Oct 30 '10 at 10:47

2 Answers2

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)

Community
  • 1
  • 1
MusiGenesis
  • 74,184
  • 40
  • 190
  • 334
  • 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. – Maghoumi Oct 30 '10 at 10:50
  • 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. – MusiGenesis Oct 30 '10 at 11:01
  • 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. – MusiGenesis Oct 30 '10 at 11:05
  • 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... – Maghoumi Oct 30 '10 at 12:10
  • @M2X: Sorry, I don't know Linq at all. I think the statement could be directly executed using ADO.NET (with a SqlCommand object). – MusiGenesis Oct 30 '10 at 20:21
  • Thanks, I was hoping to avoid using ADO.NET this time :D Will see what I can do. Thanks again – Maghoumi Oct 31 '10 at 09:23
0

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

Albin Sunnanbo
  • 46,430
  • 8
  • 69
  • 108