Beispiel #1
0
        static void completarDataSet(string server, string database, string datetime, string size, string path, string estado, DataSetBackup ds)
        {
            DataRow fila;

            try
            {
                fila             = ds.Tables["DataTableBackupControl"].NewRow();
                fila["Server"]   = server;
                fila["Database"] = database;
                fila["Date"]     = datetime;
                fila["Size"]     = size;
                fila["Path"]     = path;
                fila["Estado"]   = estado;
                ds.Tables["DataTableBackupControl"].Rows.Add(fila);
            }
            catch (Exception ex)
            {
                sendMail(System.Environment.MachineName + "@bancochubut.com.ar", "*****@*****.**", "Error en BackupControlToPDF", ex.Message, false);
                throw;
            }
        }
Beispiel #2
0
        static void Main(string[] args)
        {
            SqlConnection SqlConnection;
            SqlCommand    SqlCommand = new SqlCommand();
            SqlDataReader SqlDataReader;
            Hashtable     ht    = new Hashtable();
            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();

                                if (SqlDataReader.HasRows)
                                {
                                    while (SqlDataReader.Read())
                                    {
                                        string server   = SqlDataReader.GetValue(0).ToString().Trim();
                                        string database = SqlDataReader.GetValue(1).ToString().Trim();
                                        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 remote_path = "\\\\" + serverArr[0].Trim() + @"\" + path.Replace(":", "$");

                                        if (System.IO.File.Exists(remote_path))
                                        {
                                            estado = "OK";
                                        }
                                        else
                                        {
                                            estado = "Error";
                                        }

                                        completarDataSet(server, database, datetime, size, path, estado, ds);
                                    }
                                }

                                SqlDataReader.Close();
                            }
                        }

                        SqlConnection.Close();
                    }
                    catch (Exception ex)
                    {
                        sendMail(System.Environment.MachineName + "@bancochubut.com.ar", "*****@*****.**", "Error en BackupControlToPDF al procesar " + nombreHost, ex.Message, false);
                    }
                }

                CrystalReportBackup objRpt = new CrystalReportBackup();
                objRpt.SetDataSource(ds.Tables["DataTableBackupControl"]);
                objRpt.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, ruta + "\\Backup Control.pdf");
            }
            catch (Exception ex)
            {
                sendMail(System.Environment.MachineName + "@bancochubut.com.ar", "*****@*****.**", "Error en BackupControlToPDF", ex.Message, false);
                throw;
            }

            System.Environment.Exit(0);
        }