Call SQL statements to back up and restore SqlServer

The SQL statement is called to restore the backup of SqlServer, including full backup and differential backup. The CommandTimeout parameter must be set because it takes some time to restore the backup.

/// <Summary>

/// Call the SQL statement to back up the database

/// </Summary>

/// <Param name = "strFileName"> Backup file name </param>

/// <Param name = "BackUpType"> 0 indicates full backup, and 1 indicates differential backup </param>

/// <Returns> </returns>

Public bool BackUPDB (string strFileName, int BackUpType)

{

// For differential backup, check whether the file exists. If the file does not exist, do not execute

If (BackUpType = 1 & File. Exists (strFileName) = false)

{

Return false;

}

Bool result = false;

Try

{

String [] strConnSqlArr = strConnSql. Split (';');

String DBName = strConnSqlArr [4]. ToString (). Split ('=') [1]. ToString (); // Database Name

String backUp_full = string. Format ("backup database {0} to disk = '{1}';", DBName, strFileName );

String backUp_Diff = string. Format ("backup database {0} to disk = '{1}' with differential;", DBName, strFileName );

WKK. DBUtility. DbHelperSQL. ExecuteSql (BackUpType = 0? BackUp_full: backUp_Diff, 600 );

Result = true;

}

Catch (Exception ex)

{

Common. Log. WriteLog (string. Format ("failed to back up {0} Database", BackUpType = 0? "Complete": "difference"), ex );

// System. Diagnostics. Debug. WriteLine (string. Format ("failed to back up {0} Database", BackUpType = 0? "Complete": "difference "));

Result = false;

}

Finally

{

If (result = true)

{

String str_InfoContent = string. Format ("database backed up {0} succeeded", BackUpType = 0? "Complete": "difference ");

// System. Diagnostics. Debug. WriteLine (str_InfoContent );

}

}

Return result;

}

/// <Summary>

/// Restore the database using SQL statements

/// </Summary>

/// <Param name = "strDbName"> database name </param>

/// <Param name = "strFileName"> Backup file name </param>

Public bool RestoreDB (string strDbName, string strFileName)

{

Bool result = false;

Try

{

String strConnSql = ConfigurationSettings. etettings ["ConnectionString"]. ToString ();

String [] strConnSqlArr = strConnSql. Split (';');

String DBName = strConnSqlArr [4]. ToString (). Split ('=') [1]. ToString (); // Database Name

# Region shut down all processes that access the database. Otherwise, the database will fail to be restored.

String plain text = String. Format ("EXEC sp_KillThread @ dbname = '{0}'", DBName );

WKK. DBUtility. DbHelperSQL. connectionString = strConnSql. Replace (DBName, "master ");

WKK. DBUtility. DbHelperSQL. ExecuteSql (plain text );

# Endregion

String Restore = string. Format ("restore database {0} from disk = '{1}' WITH replace", DBName, strFileName );

WKK. DBUtility. DbHelperSQL. ExecuteSql (Restore, 600 );

Result = true;

}

Catch (Exception ex)

{

MessageBox. Show ("failed to restore database \ r \ n" + ex. Message, "system prompt! ", MessageBoxButtons. OK, MessageBoxIcon. Warning );

Common. Log. WriteLog (string. Format ("failed to restore database -- {0}", DateTime. Now. ToString (), ex );

Result = false;

}

Finally

{

// Restart the program after the recovery is successful.

If (result)

{

//

}

}

Return result;

}

/// <Summary>

/// Execute an SQL statement

/// </Summary>

/// <Param name = "SQLStringList"> SQL statement </param>

/// <Param name = "SetTimeout"> wait for the connection to start (in seconds ). The default value is 15 seconds. </Param>

/// <Returns> </returns>

Public static int ExecuteSql (string SQLString, int setTimeOut)

{

Using (SqlConnection connection = new SqlConnection (connectionString ))

{

Using (SqlCommand cmd = new SqlCommand (SQLString, connection ))

{

Try

{

Connection. Open ();

Cmd. CommandTimeout = setTimeOut;

Int rows = cmd. ExecuteNonQuery ();

Connection. Close ();

Return rows;

}

Catch (System. Data. SqlClient. SqlException e)

{

Connection. Close ();

Throw e;

}

}

}

}