Click here to Skip to main content
14,240,138 members
Rate this:
Please Sign up or sign in to 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
Updated 19-Jun-19 4:17am
v2
Rate this:
Please Sign up or sign in to vote.

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[^]
   
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
Rate this:
Please Sign up or sign in to vote.

Solution 2

Have a look here: Backing up an SQL Database in C#[^]
   
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.

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100