static void completarDataSet(string server, string database, string datetime, string size, string path, string estado, DataSetBackup ds)
{
DataRow fila;
try
{
fila = ds.Tables["DataTableBackupDetalle"].NewRow();
fila["Server"] = server;
fila["Database"] = database;
fila["Date"] = datetime;
fila["Size"] = size;
fila["Path"] = path;
fila["Estado"] = estado;
ds.Tables["DataTableBackupDetalle"].Rows.Add(fila);
}
catch (Exception ex)
{
sendMail(System.Environment.MachineName + "@bancochubut.com.ar", "*****@*****.**", "Error en BackupDetalleToPDF", ex.Message, false);
throw;
}
}
static void Main(string[] args)
{
SqlConnection SqlConnection;
SqlCommand SqlCommand = new SqlCommand();
SqlDataReader SqlDataReader;
DataSetBackup ds = new DataSetBackup();
int year = DateTime.Now.Year;
int month = DateTime.Now.Month;
string day;
string username = System.Security.Principal.WindowsIdentity.GetCurrent().Name.ToString();
Dictionary <int, string> meses = new Dictionary <int, string>();
meses[1] = "01 - Enero";
meses[2] = "02 - Febrero";
meses[3] = "03 - Marzo";
meses[4] = "04 - Abril";
meses[5] = "05 - Mayo";
meses[6] = "06 - Junio";
meses[7] = "07 - Julio";
meses[8] = "08 - Agosto";
meses[9] = "09 - Septiembre";
meses[10] = "10 - Octubre";
meses[11] = "11 - Noviembre";
meses[12] = "12 - Diciembre";
if (DateTime.Now.Day < 10)
{
day = "0" + DateTime.Now.Day.ToString();
}
else
{
day = DateTime.Now.Day.ToString();
}
string ruta = "\\\\hostst\\SectorNTyABD\\Administracion Base de Datos\\Mantenimiento\\Informes Diarios\\" + year.ToString() + "\\" + meses[month] + "\\" + day.ToString();
if (!Directory.Exists(ruta))
{
Directory.CreateDirectory(ruta);
}
ArrayList servidores = getServidores();
try
{
foreach (string nombreHost in servidores)
{
try
{
SqlConnection = new SqlConnection("data source=" + nombreHost + "; initial catalog=msdb; Integrated Security=false; user id=admin; password=**********; MultipleActiveResultSets=True");
SqlConnection.Open();
ArrayList databases = getDatabases(nombreHost);
foreach (string databaseName in databases)
{
if (!inExceptionList(nombreHost, databaseName))
{
SqlCommand.CommandText = "SELECT " +
"A.[Server], " +
"B.database_name, " +
"(select Convert(nvarchar(50),convert(varchar, A.last_db_backup_date, 103)) + ' ' + (select Convert(nvarchar(50),convert(varchar, A.last_db_backup_date, 108)))) as last_db_backup_date, " +
"cast((B.backup_size / 1024 / 1024) as decimal(19,2)) as backup_size, " +
"B.physical_device_name " +
"FROM " +
"( " +
"SELECT " +
"CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, " +
"msdb.dbo.backupset.database_name, " +
"MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date " +
"FROM msdb.dbo.backupmediafamily " +
"INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id " +
"WHERE msdb..backupset.type = 'D' " +
"GROUP BY " +
"msdb.dbo.backupset.database_name " +
") AS A " +
"LEFT JOIN " +
"( " +
"SELECT " +
"CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, " +
"msdb.dbo.backupset.database_name, " +
"msdb.dbo.backupset.backup_start_date, " +
"msdb.dbo.backupset.backup_finish_date, " +
"msdb.dbo.backupset.expiration_date, " +
"msdb.dbo.backupset.backup_size, " +
"msdb.dbo.backupmediafamily.logical_device_name, " +
"msdb.dbo.backupmediafamily.physical_device_name, " +
"msdb.dbo.backupset.name AS backupset_name, " +
"msdb.dbo.backupset.description " +
"FROM msdb.dbo.backupmediafamily " +
"INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id " +
"WHERE msdb..backupset.type = 'D' " +
") AS B " +
"ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date] " +
"WHERE (SELECT DATABASEPROPERTYEX(B.database_name, 'Status')) = 'ONLINE' " +
"AND B.database_name <> 'master' " +
"AND B.database_name <> 'model' " +
"AND B.database_name <> 'msdb' " +
"AND B.database_name <> 'tempdb' " +
"AND B.database_name = '" + databaseName + "' " +
"AND B.database_name <> 'distribution' " +
"AND A.last_db_backup_date > CONVERT(datetime, '09/01/2015', 101) " +
"ORDER BY " +
"A.database_name ";
SqlCommand.Connection = SqlConnection;
SqlDataReader = SqlCommand.ExecuteReader();
string server = nombreHost;
string database = databaseName;
if (SqlDataReader.HasRows)
{
while (SqlDataReader.Read())
{
string datetime = SqlDataReader.GetValue(2).ToString().Trim();
string size = SqlDataReader.GetValue(3).ToString().Trim();
string path = SqlDataReader.GetValue(4).ToString().Trim();
string estado;
string[] serverArr = server.Split('\\');
string servername = serverArr[0].Trim();
string remote_path = "";
if (servername == "SUC001" || servername == "SUC029")
{
remote_path = "\\\\" + serverArr[0].Trim() + @"\" + path.Replace(":", "");
}
else
{
remote_path = "\\\\" + serverArr[0].Trim() + @"\" + path.Replace(":", "$");
}
if (System.IO.File.Exists(remote_path))
{
estado = "OK";
}
else
{
estado = "Error";
sendMail(System.Environment.MachineName + "@bancochubut.com.ar", "*****@*****.**", server + " - Notificación BackupDetalleToPDF", "Server: " + server + " Database: " + database + " Estado: " + estado, false);
}
completarDataSet(server, database, datetime, size, path, estado, ds);
}
}
else
{
completarDataSet(server, database, "", "", "", "Error", ds);
}
SqlDataReader.Close();
}
}
SqlConnection.Close();
}
catch (Exception ex)
{
sendMail(System.Environment.MachineName + "@bancochubut.com.ar", "*****@*****.**", "Error en BackupDetalleToPDF al procesar " + nombreHost, ex.Message, false);
}
}
CrystalReportBackup objRpt = new CrystalReportBackup();
objRpt.SetDataSource(ds.Tables["DataTableBackupDetalle"]);
objRpt.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, ruta + "\\Backup Detalle.pdf");
}
catch (Exception ex)
{
sendMail(System.Environment.MachineName + "@bancochubut.com.ar", "*****@*****.**", "Error en BackupDetalleToPDF", ex.Message, false);
throw;
}
}