Calling SQL statements to implement backup and restore of SQL Server

Call the SQL statement to implement a backup restore of SQL Server, including full and differential backups, because it takes a certain amount of time to perform a backup restore, so you need to set the CommandTimeout parameter.

<summary>

BACKUP DATABASE Call SQL statement

</summary>

<param name= "strFileName" > backup file name </param>

<param name= "BackupType" >0 represents a full backup, representing a differential backup of 1 </param>

<returns></returns>

public bool BackupDB (string strfilename, int backuptype)

{

If it is a differential backup, it is to see if the file exists, if it 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 ("Backup {0} database Failed", BackupType = = 0?) "Complete": "Difference"), ex);

System.Diagnostics.Debug.WriteLine (String.Format ("Backup {0} database Failed", BackupType = = 0?) "Complete": "Difference");

result = false;

}

Finally

{

if (result = = True)

{

String str_infocontent = String. Format ("Backup {0} database succeeded", BackupType = 0?) "Integrity": "Difference");

System.Diagnostics.Debug.WriteLine (str_infocontent);

}

}

return result;

}

<summary>

Restoring a 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.appsettings["ConnectionString"]. ToString ();

string[] Strconnsqlarr = Strconnsql.split ('; ') ）；

String dbname = Strconnsqlarr[4]. ToString (). Split (' = ') [1]. ToString ();//Database name

#region shutting down all processes that access the database will cause the database restore to fail Shan 17:39 2014/3/19

String cmdtext = String.Format ("EXEC sp_killthread @dbname = ' {0} '", dbname);

Wkk. DBUtility.DbHelperSQL.connectionString = Strconnsql.replace (dbname, "Master");

Wkk. DBUTILITY.DBHELPERSQL.EXECUTESQL (Cmdtext);

#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 ("RESTORE Database failed RN" + ex.) Message, "System hint!" ", MessageBoxButtons.OK, messageboxicon.warning);

Common.Log.WriteLog (String.Format ("RESTORE Database Failed--{0}", DateTime.Now.ToString ()), ex);

result = false;

}

Finally

{

Restart program after successful recovery

if (result)

{

//

}

}

return result;

}

<summary>

Execute an SQL statement

</summary>

<param name= "sqlstringlist" >sql statement </param>

<param name= "settimeout" > Wait for the connection to open (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;

}

}

}

}