Backup SQL Server database to a text file(like .csv) using c#

Question

I am creating a program that uses a simple database with only a few tables. I need to do a backup and restore of the database to and from a text file like a .csv. I have tried a couple of things but none seems to work. Then I came across the following line:

 sqlcmd = new SqlCommand("backup database  " + dbname + " to disk='" +
 destdir + "\backup.bak", sqlcon);

And I am wondering if I can name it backup.csv instead of .bak. I would also like to know what to include at the 'using' section. Any help would be appreciated.

Steve · Accepted Answer · 2013-09-07 15:39:42Z

5

You could name the output file whatever you like, but this will not make it a CSV file.
The SQL Backup script produces a backup in a binary file and could be restored only using the appropriate tools like SQL Server Management Studio or a RESTORE script

You don't have any particular needs for the using section. The System.Data.SqlClient is the namespace that contains the classes SqlConnection and SqlCommand needed to execute the script

A possible workaround to reach (at least partially) you requirements is to use the BCP utility.
Usually this utility is required when you need to import large amount of data from a storage medium to your Sql Server database. But the BCP utility could also be used to export data from your database in a text file on disk. However BCP requires the name of a single datatable or a SQL Query that returns a single set of data, so you probably need to call it more than one time.
Notice also that BCP is not a Sql command but a standalone utility installed with SQL Server.

As an example (for a better explanation of the parameters check the link above) you could write this code

ProcessStartInfo psi = new ProcessStartInfo();
psi.FileName = "bcp.exe";
psi.Arguments = @"TableOrQueryToExtract out d:\temp\myTableData.txt -T -d MyDataBaseName -w";
Process.Start(psi);

As you can see, you need to call this code for each table you need to export and save that data in separate files on disk (or write a complex program that capture the BCP output and joins everything together. At the end of the day, if you don't have a specific requirement to get a text file, I really suggest to opt for the simplicity of the BACKUP command

edited Sep 7, 2013 at 15:39

answered Sep 7, 2013 at 12:55

Steve

217k22 gold badges242 silver badges296 bronze badges

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

3 Comments

user2342875 Over a year ago

I would also like to use the BACKUP command, but cannot use it as the output needs to be a text base file.

user2342875 Over a year ago

I have just one small question. When I use the code above (modified) to do import, it adds the data instead of overwriting it. Any suggestion?

Steve Over a year ago

Usually I import the data in a separate table and join with the original one using a ad-how stored procedure. You can also issue a TRUNCATE TABLE 'tablename' before the import if it is the case

Rohit · Accepted Answer · 2013-09-07 12:56:43Z

0

You should take a look at Backup SQL using C# .It is very helpful ,I dont think there should be any naming issue

also you can do it from sql Server 2005 take a look at this

answered Sep 7, 2013 at 12:56

Rohit

10.3k8 gold badges53 silver badges86 bronze badges

1 Comment

user2342875 Over a year ago

I have seen it before, but doesn't it make a .bak file. I would love to use it but I need it to be of type text. If that particular .bak is a text file it would be fine.

Collectives™ on Stack Overflow

Backup SQL Server database to a text file(like .csv) using c#

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related