CODEWITHSUNDEEP
c#.netsql-serverbackup
Sudsy1002
Sudsy1002

Reputation: 598

How can I restore a .bak file to a new server without using the .mdf or .ldf files, programmatically with C#?

Some info to get started:

  1. I am using SQL Server Express 2014
  2. I am using C#
  3. I can't use SMO functions to accomplish the following task

I am trying to restore a .bak file that I got from computer A, onto computer B. I want to do this programmatically using C#, but not using SMO functions.

In SSMS (SQL Server Management Studio), I can manually restore a database with nothing more than a .bak file. I would like to be able to do the same running a program with C#.

I am able to restore a .bak file as long as I have the .mdf and .ldf files, but not without them. Manually doing this in SSMS seems to work so I assume that there must be a way to do this with C# as well. Here is what I have:

DBConnection connect1 = new DBConnection();
connect1.connectionString = "Data Source=" + textBox1.Text + "; Integrated Security=SSPI"; // textbox1 contains a user entered server name.

// Create a new database.
using (SqlConnection myConnection = new SqlConnection(connect1.connectionString))// set the connection to sql.
{
    SqlCommand createDB = new SqlCommand("Create Database [" + dbName + "]", myConnection);
    createDB.Connection.Open();
    createDB.ExecuteNonQuery();
}

// Restore the data from the backup into the new database.
connect1 = new DBConnection();
connect1.connectionString = "Data Source=" + textBox1.Text + "; Initial Catalog=Master; Integrated Security=SSPI";
using (SqlConnection myConnection = new SqlConnection(connect1.connectionString))// set the connection to sql.
{
    //SqlCommand createDB = new SqlCommand("Restore Database [" + dbName + "] FROM DISK = " + bakDirctory + " "
    //    + "WITH MOVE '" + oldDBName + "' TO '" + mdfDirectory + "', "
    //    + "MOVE '" + oldDBName + "_log' TO '" + ldfDirectory + "', "
    //    + "REPLACE", myConnection);
    // oldDBName contains what the name of the database was when the .bak file was created.

    SqlCommand createDB = new SqlCommand("Restore Database [" + dbName + "] FROM DISK = " + bakDirectory, myConnection);
    createDB.Connection.Open();
    createDB.ExecuteNonQuery();
}

The commented part of the code snippet works, but requires the use of the .mdf and .ldf files. The part just below only uses the .bak file but I get an unhandled exception when attempting to run that code:

The backup set holds a backup of a database other than the existing 'dbname' database

I believe this is because the database structure of the newly created database doesn't match the structure of the database in the .bak file. (please correct me if I have that wrong). Is there a way for me to restore the backup using only the .bak file? Thank you in advance for taking the time to look at this.

Upvotes: 1

Views: 1630

Answers (1)

Sudsy1002
Sudsy1002

Reputation: 598

As @DanGuzman stated, there was no need to create a new database beforehand.

Following (@ScottChamberlain)'s instructions in the comments, I was able to find the script needed to restore the database without using the .mdf or .ldf files. The exact script was:

SqlCommand createDB = new SqlCommand("RESTORE DATABASE [" + dbName + "] FROM  DISK = N'" + bakDirectory + "' " +
"WITH  FILE = 2,  MOVE N'" + oldDBName + "' TO N'C:\\Program Files\\Microsoft SQL Server\\MSSQL12.ANTSQLSERVER\\MSSQL\\DATA\\" + dbName + ".mdf',  " +
"MOVE N'" + oldDBName + "_log' TO N'C:\\Program Files\\Microsoft SQL Server\\MSSQL12.ANTSQLSERVER\\MSSQL\\DATA\\" + dbName + "_log.ldf',  NOUNLOAD,  STATS = 5", myConnection);

The (C:\Program Files\Microsoft SQL Server\MSSQL12.ANTSQLSERVER\MSSQL\DATA\) path was the default location where .mdf and .ldf files are created.

Upvotes: 2

Related Questions