Backup and restore SQL database using system.data.sqlclient

Question

5.00/5 (1 vote)

See more:

I have a Windows Form project where I have created an SQL type local database. It is an MDF file. I'm using System.Data.SqlClient to open the database and read and write to it.

It's working fine except for being able to backup and restore.

My goal is to be able to backup the database (to an MDF file) and then be able to restore it, all from within my application.

******* UPDATE JUNE 19 **********************************************************

I'm making some progress, but still can't perform the restore. First, my connection string and code for connection is:

String connectString = "Data Source=(LocalDB)\\MSSQLLocalDB; AttachDbFilename = " + startup_dir + "\\TAS_Dbase\\MyDataBase.mdf; Integrated Security = True";
DB_Connection = new SqlConnection(connectString);
DB_Connection.Open();

The code for restore is:

private void button2_Click(object sender, EventArgs e)
{
    String dbName = getDBname();

    String buPath = "'" + db_path + "\\Backup.bak'";

    sqlString = "RESTORE DATABASE " + dbName + " FROM DISK = " + buPath;

    command = new SqlCommand(sqlString, DB_Connection);
    command.ExecuteNonQuery();
}

Execution of the command causes the following exception.

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: RESTORE cannot process database 'C:\USERS\VICTOR\DOCUMENTS\VISUAL STUDIO 2015\PROJECTS\DB_TEST\DB_TEST\BIN\DEBUG\TAS_DBASE\MYDATABASE.MDF' because it is in use by this session. It is recommended that the master database be used when performing this operation.

RESTORE DATABASE is terminating abnormally.

Having the database open is preventing the restore. Is there a way to connect to the SQL Server engine without opening the database (could that work) ? What do I have to do to avoid this problem?

What I have tried:

I have tried the following, where DBname is the name of the database. In this case it is -

"\"C:\\USERS\\VICTOR\\DOCUMENTS\\VISUAL STUDIO 2015\\PROJECTS\\DB_TEST\\DB_TEST\\BIN\\DEBUG\\TAS_DBASE\\MYDATABASE.MDF\""

sqlString = "BACKUP DATABASE " + DBname + " TO DISK = '" + path_to_backup;
 command = new SqlCommand(sqlString, DB_Connection);
 command.ExecuteNonQuery();

The backup seems to be performed, and the file "Backup.mdf" is created. However, if I try to connect to the backup file, I get the following exception:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: Cannot open database "C:\USERS\VICTOR\DOCUMENTS\VISUAL STUDIO 2015\PROJECTS\DB_TEST\DB_TEST\BIN\DEBUG\TAS_DBASE\BACKUP.MDF" requested by the login. The login failed.

Login failed for user 'Vics_PC\Victor'.

I believe the backup file created is not really an MDF type file. I noted that it is about 200kBytes smaller than the original MDF file.

Is there a way to achieve my goal?

Posted 18-Jun-19 6:45am

Member 11298827

Updated 20-Mar-21 11:08am

v2

Add a Solution

3 solutions

Solution 4

For you:
public void Backup()
{
try
{
SaveFileDialog sd = new SaveFileDialog();
sd.Filter = "SQL Server database backup files|*.bak";
sd.Title = "Create Database Backup";

if (sd.ShowDialog() == DialogResult.OK)
{

string sqlStmt = string.Format("backup database [" + System.Windows.Forms.Application.StartupPath + "\\Database1.mdf] to disk='{0}'", sd.FileName);
using (SqlCommand bu2 = new SqlCommand(sqlStmt, con))
{
con.Open();
bu2.ExecuteNonQuery();
con.Close();

MessageBox.Show("Backup Created Sucessfully");

}
}
}
catch (Exception)
{
MessageBox.Show("Backup Not Created");
}
}

public void restore()
{

OpenFileDialog openFileDialog1 = new OpenFileDialog
{
InitialDirectory = @"C:\",
Title = "Backup sql",

CheckFileExists = true,
CheckPathExists = true,

DefaultExt = "bak",
Filter = "backup files (*.bak)|*.bak",
FilterIndex = 2,
RestoreDirectory = true,

ReadOnlyChecked = true,
ShowReadOnly = true
};

if (openFileDialog1.ShowDialog() == DialogResult.OK)
{

try
{
connect();
string sqlStmt = string.Format("RESTORE DATABASE [" + System.Windows.Forms.Application.StartupPath + "\\Database1.mdf] from disk='{0}'", openFileDialog1.FileName);
using (SqlCommand bu2 = new SqlCommand(sqlStmt, con))

{
bu2.Connection = con;
//bu2.CommandText = sqlStmt;

bu2.ExecuteNonQuery();
}
con.Close();
MessageBox.Show("Restore done Sucessfully");
}
catch (Exception ex)
{
MessageBox.Show("restore erreur");
MessageBox.Show(ex.ToString());
}

}
}

Posted 20-Mar-21 11:08am

Member 14315647

Comments

Richard Deeming 23-Mar-21 6:31am

An unformatted, unexplained code-dump is not a solution to this already-solved question.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

MadMyche · Accepted Answer · 2019-06-18T07:12:00

Solution 1

The Backup is used to create a backup of the database; not a copy of it, and the resulting file should use the BAK file extension.

A backup of the database needs to have the Restore command used on the desired file to mount the database.
RESTORE (Transact-SQL) - SQL Server | Microsoft Docs[^]

Posted 18-Jun-19 7:12am

MadMyche

Comments

Member 11298827 18-Jun-19 13:49pm

MadMyche, Thanks so much for your answer. It makes sense, and I'll try it and get back to you.

MadMyche 18-Jun-19 14:42pm

You're welcome

OriginalGriff · Accepted Answer · 2019-06-18T08:43:00

Solution 2

Have a look here: Backing up an SQL Database in C#[^]

Posted 18-Jun-19 8:43am

OriginalGriff

Comments

Member 11298827 18-Jun-19 18:56pm

OriginalGriff, Thanks for posting this. Question: does this code require Microsoft SQL Express be installed? As far as I know (but I could be completely wrong) the code I'm using so far does not need SQL Express, it just uses the .Net framework. I really don't know because I'm very new to this. I would like this application to be as portable as possible, so if I can do it without SQL Express that would be the best.

OriginalGriff 19-Jun-19 2:03am

Not necessarily the Express version, but ... you need SQL Server installed somewhere in order to use an SQL database at all, so I'd guess you have access to it anyway!

Member 11298827 19-Jun-19 10:09am

O'Griff,

Thanks again for the reply. Yes I do have SQL Server installed, but I didn't know if it was being used by the application. I need to be able to install it on other machines, but at this time I don't have access to any other PC to try it.
Reading through your article, I'm a little daunted about trying it. Seems like there are a lot of stumbling blocks. My alternate plan is to just copy the .mdf file to a subdirectory (actually a different drive would be safer). I can to this before I connect to the database, but while connected, the mdf file is locked, and can't be copied. Also it seems the mdf file is not updated until the application closes. Is there a way to get SQL Server to update and release the file after connecting to it? For instance can I disconnect from the database to release the file for copying, and then reconnect to it? By the way, the database is very small and should never grow to more than 10MB or so. I sure appreciate your help.

OriginalGriff 19-Jun-19 10:20am

Seriously, just paste the Backup code into an app, change the names to match your system, and try it - it's a lot easier than trying to get SQL server to "let go" of a DB and then "take it back" when you have copied it.

Member 11298827 19-Jun-19 16:05pm

O'Griff,

I took your advice and tried your code. It seems to be working!!! Have more testing to do, and maybe more questions for you.

Member 11298827 19-Jun-19 20:01pm

O'Griff,

So far so good. Question: Your code declares and assigns value to ConnectionString, but I can't see where it is consumed. I commented out the line and it still works (at least the restore function, have not tried backup yet since it was already working in my code). So is this variable needed?

public static readonly string ConnectionString = @"Data Source=GRIFFPC\SQLEXPRESS;Initial Catalog=AudioMaster;Integrated Security=True";

OriginalGriff 20-Jun-19 2:03am

"Is this variable needed?"
Nope - it's a "hangover" from the code that was abstracted from and I forgot to delete it. :blush:

Member 11298827 20-Jun-19 13:31pm

O'Griff, thanks for letting me know that! All testing is complete and it's working well. One thing I want to mention -

It seems the database name is just the complete path to the .mdf file. In your code you hardcode the database name like -

source.Database = "MyDataBaseName";

That doesn't work in my case because the database name (pathname to .mdf) would change depending on the PC the application was running on. So, I changed the RestoreDatabase and BackupDatabase methods to pass in a string holding the pathname. Hope that makes sense, don't know what else I could have done.

Thanks again for all your help. I'm marking your solution as accepted.