public static int ApplySQLFilesToServer(string stringsrv, string filepath, string onedb = null)
{
int x = 0;
Server srv = Connections.ServerConnect(stringsrv);
string[] sqlfiles = Directory.GetFiles(filepath, "*.sql");
foreach (string fi in sqlfiles)
{
if (onedb == null)
{
foreach (Database db in srv.Databases)
{
if (db.IsSystemObject == false)
{
string dbName = db.Name;
try
{
SqlOperations.ExecuteCommand(stringsrv, dbName, Files.FileAsString(fi));
Console.WriteLine("Command succeeded on " + dbName + ".");
SqlOperations.ReturnConfirmation(stringsrv, dbName);
}
catch (Exception ex)
{
Console.WriteLine("Command failed on " + dbName + ". Exception: {0}.", ex);
x++;
}
}
}
}
else
{
try
{
SqlOperations.ExecuteCommand(stringsrv, onedb, Files.FileAsString(fi));
Console.WriteLine("Command succeeded on " + onedb + ".");
SqlOperations.ReturnConfirmation(stringsrv, onedb);
}
catch (Exception ex)
{
Console.WriteLine("Command failed on " + onedb + ". Exception: {0}.", ex);
x = 1;
}
}
}
return(x);
}
public static int IssueCheckpoints_CertainDBs(string stringsrv, List <String> dbs)
{
int x = 0;
foreach (string db in dbs)
{
try
{
SqlOperations.ExecuteCommand(stringsrv, db, "CHECKPOINT;");
Console.WriteLine("Checkpoint issued on " + db + ". Verify if this stops error log issues.");
}
catch (Exception ex)
{
Console.WriteLine("Checkpoint failed on " + db + ". Exception: {0}.", ex);
x++;
}
}
return(x);
}
public static int NonSystemDBsIssueCommand(string stringsrv, string cmdText)
{
int x = 0;
Server srv = Connections.ServerConnect(stringsrv);
foreach (Database db in srv.Databases)
{
if (db.IsSystemObject == false)
{
string dbName = db.Name;
try
{
SqlOperations.ExecuteCommand(stringsrv, dbName, cmdText);
Console.WriteLine("Command succeeded on " + dbName + ".");
}
catch (Exception ex)
{
Console.WriteLine("Command failed on " + dbName + ". Exception: {0}.", ex);
x++;
}
}
}
return(x);
}
public static string IssueCheckpoints(string stringsrv)
{
/*
* SELECT
* CASE
* WHEN [Checkpoint End] IS NULL THEN 'Checkpoint started: '
* ELSE 'Checkpoint finished: '
* END AS [Status]
* , CASE
* WHEN [Checkpoint End] IS NULL THEN [Checkpoint Begin]
* ELSE [Checkpoint End]
* END AS CheckpointTime
* FROM fn_dblog(NULL, NULL)
* WHERE Operation IN ('LOP_BEGIN_CKPT', 'LOP_END_CKPT')
*/
string message = "";
Server srv = Connections.ServerConnect(stringsrv);
foreach (Database db in srv.Databases)
{
if ((db.IsSystemObject == false) || (db.RecoveryModel.ToString() == "Simple"))
{
string dbName = db.Name.ToString();
try
{
SqlOperations.ExecuteCommand(stringsrv, dbName, "CHECKPOINT;");
message = "Checkpoint issued on " + dbName + ".";
}
catch (Exception ex)
{
message = "Checkpoint failed on " + dbName + "." + "\n" + "Exception: " + ex.ToString();
}
}
}
return(message);
}