Use C # To Restore database (SQL Server) backup files to the specified path

Recently, a data collection module has encountered a scenario where you need to restore the database backup file to a specified database instance and then collect data. There is no problem with local testing, but a big bug is found when you get the real environment test. All databases cannot be restored. Because I thought SQL Server would be restored to the default path, but it is not.

When I get the real database backup file, I first run Restore database restoredbname from disk = 'H: \ dbfolder \ db_back 'in the database. The execution result is as follows:

 

　　

So far, I still don't know what is going on, because it is good to restore the database I created. Why? In an accident, I found that the database I created was in the default path of the database instance, but the actual database backup file was not. Its path is on the client machine, and it must be different. To verify this discovery, I have done the following work.

Step 1: Create Database testdb.

Step 2: Back up the database.

Step 3: Restore the database directly and restore database restoredbname from disk = 'I: \ testdatabase'. The operation is successful.

Step 4: Restore the database. At this time, instead of restoring the database by default, I will restore the database to another path of the system. The Code is as follows:

restore database RestoreDbName from disk ='I:\TestDatabase'   with  replace, move 'TestDb' to 'H:\DBFolder\RestoreDbName_Data.mdf', move 'TestDb_log' to 'H:\DBFolder\RestoreDbName_Log.ldf'

Execution result:

Step 5: Back up the database restoredbname again. After the backup, we can use filelistonly to view the backup path of the current database backup file (Restore filelistonly from disk = 'I: \ restoredb'). The result is as follows:

Here we can see the real physical path of the backup file of the current database.

Step 6: When I run Restore database restoredbname from disk = 'I: \ restoredb' again, an error occurs. The error is the same as that in the real environment. As described above, when the database backup file is restored, if the default path of the current database instance does not match the database backup file, move is required, rather than simply restore.

 

So I re-wrote the methods for restoring database backup files, as shown below. This method can be used to restore database backup files.

View code

/// <Summary>
/// Restore the database file
/// </Summary>
/// <Param name = "basepath"> root directory of the electronic books </param>
/// <Param name = "FILENAME"> name of the database backup file </param>
/// <Param name = "databasename"> name of the database to be restored </param>
/// <Param name = "conn"> database connection </param>
Private bool restoredatabase (string basepath, string filename, string databasename, sqlconnection conn)
{
Sqlcommand command = NULL;
Try
{
String restorestr = string. empty;
String getlogicfilename = string. Format ("Restore filelistonly from disk = '{0}'", path. Combine (basepath, filename ));
Dataset DS = new dataset ();
Sqldataadapter da = new sqldataadapter (getlogicfilename, Conn );
Da. Fill (DS );
If (Ds. Tables = NULL | Ds. Tables [0]. Rows. Count = 0) return false;

Foreach (datarow DR in DS. Tables [0]. Rows)
{
// Search for the database logical file name
If ("D". Equals (Dr ["type"]. tostring ()))
Restorestr + = string. format ("Move '{0}' to '{1}',", Dr ["logicalname"]. tostring (), path. combine (basepath, databasename) + "_ data. MDF ");
// Search for the Database Log File Name
Else if ("L". Equals (Dr ["type"]. tostring ()))
Restorestr + = string. format ("Move '{0}' to '{1}',", Dr ["logicalname"]. tostring (), path. combine (basepath, databasename) + "_ log. LDF ");
}
If (string. isnullorempty (restorestr ))
Restorestr = string. Format ("Restore database {0} from disk = '{1}'", databasename, path. Combine (basepath, filename ));
Else
{
Restorestr = string. format ("Restore database {0} from disk = '{1}' with replace,", databasename, path. combine (basepath, filename) + restorestr. trimend (',');
}
Command = new sqlcommand (restorestr, Conn );
Conn. open ();
Command. executenonquery ();
Return true;
}
Catch (exception ex)
{
Return false;
}
Finally
{
Conn. Close ();
}
}