Windows forms application and database back-up

[James Page]

Hi all - hope this is the right forum!

I have a simple vb.net windows forms application which uses data from an
underlying sql express database.

It is deployed in a single user environment and the sql express package is
installed in the default location.

I want to be able to allow the user to backup and restore the underlying
..mdf datafile.

Can anyone give me a couple of pointers on the best way to achieve this?


Many thanks

 

[Cor Ligthert[MVP]]

James,

You can always simple use a simple File.Copy for this, (and copy the LDF
file as well)

Set your original database on a for the user accessable place to make it
even easier.

http://msdn.microsoft.com/en-us/library/system.io.file.copy.aspx

Cor

 

[Norman Yuan]

If the local SQL Server Express is running (meaning *.mdf/*.ldf is attached
to the SQL Server instance), the files cannot be copied.

To the OP:

Depending on how the user logs in to the SQL Server instance, if he/she has
the permission to do the backup/restore, then you can have your app to run
BACK DATABASE..../RESTORE DATABASE... TSQL statement to do the backup. YOu
can also look into SMO object model to incorporate your app to do the
backup/restore.

The other option would be to use SQL Server Express' USER INSTANCE, to
which, the user do not have to have local admin rigth to attach database to
SQL Server Express instance. With USER INSTANCE, you can get the *.mdf file
easily attached to SQL Server Express at begining of your app and detached
and a backup copy created when your app is done.

However, be warned, USER INSTANCE is an advanced feature and only available
to SQL Server Express2005/8, and rumor has it that USER INSTANCE feature
will be dropped from next SQL Server version, because it causes more trouble
(due to the lack of understanding, IMO) than benefit.

 

[Cor Ligthert[MVP]]

Norman,

Why should it be running in a single user operation?

What you write is valid as it is running as multi user source, but as it is
single user like the OP wrote, than in my idea simple make copies has the
same effect.

I was also going for the backup/restore, but rejected that because it has in
my idea no advantages in a single user situation.

Cor

 

[James Page]

Thanks for that Cor.

The whole idea was that in case of a serious computer crash, or worse, the
user will have a copy of the database files to reinstate.

So will a simple file copy be the best approach or should I consider a
backup and restore option so that the user could manage the restoration of
lost data - say in the case of a os or application reinstall or the purchase
of a new pc?

I want to create an application that is as far as possible self managing -
i.e. won't require me to get involved if the user chooses to change his pc -
just merely reinstalls the application and 'restores' the backed up database
files from within the app.

Thanks in advance.

 

[Norman Yuan]

I may read the OP message wrong. But what I said is correct: the *.mdf
cannot be copied if it is attached to SQL Server/Express. If the OP want to
use "file copy" to back up the database, he has to detach the database from
SQL Server/Express first, regardless it is single user or not.

If the OP meant to get *.mdf from somewhere to the said coputer, of course
it is "File Copy".

 

[Cor Ligthert[MVP]]

James,

I can not decide that for you. With the information you have given me, I
would probably only tell to the user that he has to made backups using the
normal OS functions from the data directory (and give him that) on regular
base.

However, I don't by instance not know the law in your situation, so it is
completely up to you.

Cor

 

[Miro]

Just a thought - from a newbie.

What if you created a function that created a "Select * from Table" for all
tables and wrote that to multiple XML files.
Those XML files is what would be backed up...
Those XML files would be what the menu option would be used to "restore".

Be careful of security though - as to who has access to the folder where
these xml files are created.

I also do not know how big your datafile is / how many records you have.

But that could be a solution for you if you use an .mdf file without
detaching the .mdf file from the server to copy it.

One other thought would be to get something like a 'hosting site' like
www.discountasp.net ( that only comes to mind cause i use that for my dummy
apps )... and keep the data on a hosted site.
A computer failes, install your app on another machine and link to your data
on the hosting site.

Better yet, what if you had a site, with a blank copy of their database
there.
When they want to backup - it pushes the data online to your hosting site
( which is secure ).
-This could be a fee for use / kb or whatever.
When they requrie it, they 'pull' from your server.

The cost would be pretty small ( even at discountasp.net its 10 bux for a
server a month and 10 bux for the hosting = 20 / month ).
Charge 40 and everyone is happy

Miro

 

[Cor Ligthert[MVP]]

Miro,

The problem in you suggestion that creating the XML file is normally easy.

But setting it back to the database needs all kind of creation (Make),
insert and maybe relation settings.

Cor

 

[James Page]

Thanks everyone for your input.

To clarify here is the full picture:

The client buys and sells used vans here in the UK and wants keep a record
of how much he spends on a vehicle and how much he sells them for. Plus he
wants to keep a historical record of all the vehicles that pass through his
business.

Designing the forms application, and the underlying database, is fairly
straightforward, but in the event of a computer failure or change of pc I
wanted a simple way for him to press a button from within the app so that the
underlying database is copied to a location of his choice (usb stick, cd etc).

Once the original application is reinstalled he should be able to click
another button that overwrites the empty database file with the backup that
he created before.
Ideally this needs to be achieved without my intervention.

The design of the application is with VS2008 and is deployed with sql
express, installer 3.1 etc.
It is to be used on a single stand alone pc.

Am I right in thinking that a basic ‘file copy’ / ‘file write’ scenario
cannot be achieved once the application has been deployed or does the
database file only get attached whilst the application is running?
If the latter is true I should be able backup & restore by writing a
standalone application that does not invoke the attachment of the *.mdf/
*.ldf files to sql express – or is my thinking wrong on this?

Looking forward to your thoughts...

Many thanks

 

[Cor Ligthert[MVP]]

James,

Why not simple try it, to make a sample about this takes less time then
creating the message as you did.

While as you have it ready it needs only a copy and paste.

Cor