The ability to restore databases from valid backups is a vital part of ensuring business continuity. In many instances, IT auditors merely confirm whether backups are being performed either to disk or to tape, without considering the integrity or viability of the backup media. The primary responsibility of the database administration team is to review all types of RDBMSs in the enterprise and to develop a comprehensive backup plan to conduct effective backup management by proactively monitoring backups, getting alerted for failed backups and rerunning these seamlessly, without loss of time. It is good practice to back up data to physical disk and to then archive the data to tape for disaster recovery purposes. Once an approach has been established, it is imperative to test data restoration periodically as part of the backup and restore strategy, and to review all options before executing the actual restoration/recovery. It is important to confirm that the DBA team is abreast of the latest backup and recovery tools and to ensure that the team has a clearly documented process in place with clear responsibilities. If DBAs maintain proper backups, monitor these proactively and can provide assurance of the recovery of data up to the point required by the business, they have done a major part of the job for which they were hired. IT auditors can assist data administration teams in strengthening their controls and data recovery processes by validating the DBA operations, including the testing of the recovery of data. This continuous, proactive and cooperative effort between internal audit and the DBA team can provide assurance to management that, in the event of a disaster, the business’s data can be recovered. Effective Backup Management After making a solid backup plan and completing initial work, the DBA should properly manage backups, keeping the following points in mind:

• Automating backups—In SQL Server, use Maintenance Plans or customized script for scheduling backups.

• Monitoring backups—Set up monitoring using appropriate tools so that the DBA gets an e-mail or alert through a pager or cell phone for any failed backups, which should be rerun as soon as possible.

• Backup logs and catalogs—Review backup logs and backup catalog information periodically for any issues.

For SQL Server, backup system databases, especially master and msdb.

• Database catalog maintenance—With SQL databases, use “delete obsolete” to remove backups that are outside the organization’s retention policy. If obsolete backups are not deleted, the catalog will continue to grow and performance will become an issue. Cross-checking (cross-check backup) will check that the catalog/control file matches the physical backups.

• Validating backups—validate and verify backups without doing actual restores.

• Setting up dependencies—When backing up to disk, archive these backups to tape as soon as backup to disk completes.

Note:-Set up a process so that disk backups get transferred to tape without loss of time. Script: To monitor the Backup Status for all databases and informed if any databases are missed USE [admin] GO /****** Object: Table [dbo].[MissDBbackup]   Script Date: 07/05/2010 15:39:40 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MissDBbackup]') AND type in (N'U')) DROP TABLE [dbo].[MissDBbackup] Go create table MissDBbackup ( DatabaseName VARCHAR(155) ); /*********************************************************************/ declare @numOfMinutes int; declare @dbname varchar(2000); declare @servername varchar(2000); DECLARE @serverproperty_servername    varchar(100); declare @profile_name nvarchar(2000) declare @Subject varchar(110)   -- Get the value returned by the SERVERPROPERTY system function select @serverproperty_servername = CONVERT(varchar(100), SERVERPROPERTY('ServerName')) -- Get the value returned by @@SERVERNAME global variable select @profile_name = CONVERT(varchar(100), @@SERVERNAME) select @Subject = 'Missed backup list for:'+ @serverproperty_servername select @numOfMinutes = 1440;--Number of Minutes Delete from Admin.dbo.MissDBbackup insert into MissDBbackup select name as Databasename from master.dbo.sysdatabases where name not in ('master','model','msdb','tempdb') and name not in (select distinct database_name from msdb.dbo.backupset where type ='D' group by database_name having datediff(minute, max(backup_start_date),getdate()) < @numOfMinutes) if @@rowcount >0 EXEC msdb.dbo.sp_send_dbmail @profile_name, @recipients = 'admin@SmartERP.com', @query = 'select * from Admin.dbo.MissDBbackup' , @subject=@subject , @attach_query_result_as_file = 1 ; Go