How to backup SQL Server database (mdf) programmatically to later import it rowwise?

Question

My aim is to backup a database (.mdf) as one file with my web application project written in C#. The backup should later on be loaded on a "restore"-page, where the data in the backed-up tables could be appended to the original database row by row. What would be a good practice to implement this? I thought of just copying the mdf file, but then I read about attaching and detaching of the database. Furthermore I don't know what to do with the _log.ldf file.

I'm looking forward to your hints. Thank you in advance for your help!

EDIT: I can only use the free SQL Server Express for this, because I want to distribute my program to other people.

Answer 1

pls go through this link on how to backup data and restore using c# and sql server .In addition to you have to add these names spaces

Microsoft.SqlServer.Management.Smo;
Microsoft.SqlServer.Management.Common;

Answer 2

If you are only interested in appending the data rowwise afterwards, perhaps it is easier to export each table to CSV and import it afterwards (so you have rowwwise control in C#).

If you insist ine one file, just add all the CSV's to a zip.

You can use the FileHelpers library for this (http://www.filehelpers.com/) and you will have it up&running in no time.

Answer 3

Apparently there is a Backup class in the SQL Server Management Objects library.

You might want to check that out first, as it doesn't look overly complicated:

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.backup.aspx

It would require you to have two or three libraries installed on the server you run it on, though they are fairly small - not Windows SDK sized...

Answer 4

Probably, you refer to the Backup and Restore using C# for Sql Server to get a complete idea about writting a code in C#, which has helped me a lot when I was using it.

By the use of Backup class in C#, you can get all the facilities to backup as well as restore.