Public classBakdbhelper {/// <summary> ///Create a database backup/// </summary> Public stringCreateBackup (stringdbnamestringbackname) { stringres =""; //the location to back up//string dbfullname = Getdbpath () + string. Format ("{0}_{1}.bak", dbname, DateTime.Now.ToString ("Yyyymmddhhmmss")); stringDbfullname = Getdbpath () +Backname; //determine if a file exists if(File.exists (dbfullname)) {file.delete (dbfullname); //MessageBox.Show (dbfullname + "backup file already exists, please try again later"); } Try{SqlConnection con=NewSqlConnection (Getconn ()); SqlCommand cmd=con. CreateCommand (); Con. Open (); Try{Cmd.commandtext="Use Master"; Cmd. ExecuteNonQuery (); //1. Performing a backup operationStringBuilder sql =NewStringBuilder (); //SQL. Append ("Exec master.dbo.proc_Backup @dbName, @dbFullName");SQL. Append (@"DECLARE @kid varchar SET @kid = ' SELECT @[email protected]+ ' KILL ' +cast (spid as Varchar (10)) From Master. sysprocesses WHERE dbid=db_id (@dbName) PRINT @kid EXEC (@kid ); Backup Database"+ dbname +"to [email protected];"); sqlparameter[] Parameters=Newsqlparameter[]{NewSqlParameter ("@dbName", SqlDbType.NVarChar, $), NewSqlParameter ("@dbFullName", SqlDbType.NVarChar, $), }; parameters[0]. Value =dbname; parameters[1]. Value =Dbfullname; Cmd. Parameters.clear (); Cmd. Parameters.addrange (Parameters); Cmd.commandtext=SQL. ToString (); Cmd. ExecuteNonQuery (); Res="Backup Complete"; } finally{con. Close (); } } Catch(Exception ex) {res="Error Creating database backup:"+ex; } returnRes; } /// <summary> ///Restoring a database/// </summary> Public stringRestore (stringdbnamestringbackname) { stringres =""; //1. Get the Restore database and files stringDbfullname = Getdbpath () +Backname; Try { //2. Perform a restore operationSqlConnection con =NewSqlConnection (Getconn ()); SqlCommand cmd=con. CreateCommand (); Con. Open (); Try{Cmd.commandtext="Use Master"; Cmd. ExecuteNonQuery (); StringBuilder SQL=NewStringBuilder (); //SQL. Append ("Exec proc_restore @dbFullName, @dbName");SQL. Append (@"--1.1 modified to single mode exec (N ' ALTER DATABASE ' [email protected]+ ' SET single_user with ROLLBACK IMMEDIATE '); --1.2 End link Process DECLARE @kid varchar (max) SET @kid = ' ' SELECT @[email protected]+ ' KILL ' +cast (spid as Varchar) from master: sysprocesses WHERE dbid=db_id (@dbName); EXEC (@kid); --2. Performing a RESTORE statement restore database @dbName from [email protected] with replace-Overwrite existing database --3. Resetting the database for multi-user mode exec (N ' ALTER database ' [email protected]+ ' SET multi_user with ROLLBACK IMMEDIATE ');"); sqlparameter[] Parameters=Newsqlparameter[]{NewSqlParameter ("@dbName", SqlDbType.NVarChar, $), NewSqlParameter ("@dbFullName", SqlDbType.NVarChar, $), }; parameters[0]. Value =dbname; parameters[1]. Value =Dbfullname; Cmd.commandtext=SQL. ToString (); Cmd. Parameters.addrange (Parameters); Cmd. ExecuteNonQuery (); Res="Restore Database Completion"; } finally{con. Close (); } } Catch(Exception ex) {res="Error Restoring Database"+ex; } returnRes; } Private Static stringGetdbpath () {returnconfigurationmanager.appsettings["Bak_url"]. ToString (); } Private Static stringGetconn () {returnconfigurationmanager.appsettings["Bak_con"]. ToString (); } Public intExecsqlcommand (stringSQL) { intRes; Try { //2. Perform a restore operationSqlConnection con =NewSqlConnection (Getconn ()); SqlCommand cmd=con. CreateCommand (); Con. Open (); Try{Cmd.commandtext=SQL; Res=cmd. ExecuteNonQuery (); } finally{con. Close (); } } Catch(Exception) {return-1; } returnRes; } }
SQL Server Backup Restore Code C #