private SqlConnection SQLConnect(string stringConnection)
{
try
{
SqlConnection Con = new SqlConnection(stringConnection);
Con.Open();
return(Con);
}
catch (Exception e)
{
ExecutionErro.Add("Erro ao abrir conexão com o banco");
ExecutionErro.Add(e.Message.ToString());
return(null);
}
}
public bool Restorebackup()
{
string DatabaseName = ConnectionInfoDB.DbName;
string FileName = DatabaseName + ".bak";
string cnnScring = Util.GetConnectionString(ConnectionInfoDBtemp) + " ;Database=MASTER";
//Realiza o RollBack
//Restaura o ultimo banco de dados Full
string RestoreCommand1 = "DECLARE @LastBackup int;\r\n" +
"SET @LastBackup = (SELECT Top 1 position FROM msdb..backupset where database_name = '" + DatabaseName + "' And type = 'D' and is_snapshot = 0 " +
"order by backup_finish_date desc)\r\n" +
"Restore Database " + DatabaseName + " " +
"from Disk = '" + FileName + "' " +
"WITH FILE = @LastBackup ,NORECOVERY";
//Restaura o ultimo banco de dados Incremental
string RestoreCommand2 = "DECLARE @LastBackup int;\r\n" +
"SET @LastBackup = (SELECT Top 1 position FROM msdb..backupset where database_name = '" + DatabaseName + "' and is_snapshot = 0 " +
"order by backup_finish_date desc)\r\n" +
"Restore Database " + DatabaseName + " " +
"FROM Disk = '" + FileName + "' " +
"With FILE = @LastBackup ,Recovery, stats=10";
try
{
ExecuteCommand(cnnScring, "ALTER DATABASE " + DatabaseName + " SET SINGLE_USER WITH ROLLBACK IMMEDIATE");
ExecuteCommand(cnnScring, RestoreCommand1);
ExecuteCommand(cnnScring, RestoreCommand2);
ExecuteCommand(cnnScring, "EXEC sp_dboption '" + DatabaseName + "', 'single user', 'false'");
ExecutionErro.Add("Backup Restaurado.");
return(false);
}
catch (Exception Err)
{
ExecuteCommand(cnnScring, "EXEC sp_dboption '" + DatabaseName + "', 'single user', 'false'");
ExecutionErro.Add("Erro ao restaurar o Backup.\r\n\r\nError message: " + Err.Message.ToString());
return(true);
}
}
/* *******************************************************************************************
* Executa um determinado comando no SQL.
*********************************************************************************************/
public bool ExecuteCommand(string _ConnectionString, string queryString)
{
using (SqlConnection connection = new SqlConnection(_ConnectionString))
{
SqlCommand command = new SqlCommand(queryString, connection);
try
{
command.Connection.Open();
int Ret = command.ExecuteNonQuery();
command.Connection.Close();
command.Connection.Dispose();
return(false);
}
catch (Exception e)
{
command.Connection.Close();
command.Connection.Dispose();
ExecutionErro.Add(e.Message.ToString());
return(true);
}
}
}
/* *******************************************************************************************
* Roda o Script de Criação do Banco
*********************************************************************************************/
public void DBCreateByScript(string _ConnectionString, List <string> SQLScript)
{
string ExecuteLine = "";
try
{
SQLScript = SQLScript.Where(l => l.Trim().Length > 0 && (!l.StartsWith("/*") || !l.EndsWith("*/"))).ToList <string>();
switch (ConnectionInfoDB.ServerType)
{
case Util.DatabaseType.SQL:
SqlConnection Connect = SQLConnect(_ConnectionString);
SqlTransaction _SqlTransaction = Connect.BeginTransaction();
try
{
if (Connect != null)
{
//SqlTransaction _SqlTransaction = Connect.BeginTransaction();
ExecuteCommand(_ConnectionString + ";Initial Catalog=master", "if db_id('" + NewDatabase + "') is null CREATE DATABASE [" + NewDatabase + "]");
SqlCommand Command = new SqlCommand("USE [" + NewDatabase + "]", Connect, _SqlTransaction);
CleanDatabase(Util.GetConnectionString(ConnectionInfoDBtemp, true));
Command.ExecuteNonQuery();
foreach (string Line in SQLScript)
{
if (Line.Trim().ToUpper() != "GO")
{
ExecuteLine += Line + "\r\n";
}
else
{
//Command.CommandText = ExecuteLine;
//int RetVal = Command.ExecuteNonQuery();
Command = new SqlCommand(ExecuteLine, Connect, _SqlTransaction);
Command.ExecuteNonQuery();
ExecuteLine = "";
}
}
if (ExecuteLine.Length > 0)
{
Command = new SqlCommand(ExecuteLine, Connect, _SqlTransaction);
Command.ExecuteNonQuery();
ExecuteLine = "";
}
_SqlTransaction.Commit();
Connect.Close();
}
else
{
ExecutionErro.Add("Erro ao criar ao abrir Conexão.");
}
}
catch (Exception e)
{
_SqlTransaction.Rollback();
Connect.Close();
ExecutionErro.Add("Erro ao criar o banco de dados temporário");
ExecutionErro.Add(e.Message.ToString());
}
break;
case Util.DatabaseType.ORACLE:
break;
default:
break;
}
}
catch (Exception Ex)
{
}
}
