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?