Features:

• Automatic discovery of all databases on the server.
• Automated full backups on a daily basis.
• Automated transaction log backups every 9 minutes.
• Automated monthly backups.
• Automated deletion of old files.
• Automated truncation of the MSDB database.
• Automated generation of restore script with STOPAT parameter. The restore script will include all needed transaction logs.

Version :

7.0 : Released February 8, 2008 for Beta testing. This version is running in production environment on Integrated Database's servers.

Architecture:

Concepts:

• Schedule Type: The system provides for two different schedule types as follows.
• Fixed Time: The job will be executed at the same time. The next execution date is based on the original execution date. This setting should be used for any job that needs to be executed at a fixed time each day. Use the constant integer 1 to specify this schedule type.
• Fixed Interval: The next execution time is based on the time that the previous job started. This setting is best when the actual time of execution is less important than how frequently the job should be executed. Use the constant integer 2 to specify this schedule type.
•  Job Type: Each job is of a given type as follows:
  1. Full Backup
  2. Log Backup
  3. Monthly Backup
  4. TSQL

TSQL jobs can be used to schedule execution of any stored procedure.

Settings:

• UseSubFolder: Helps determines the BackupPath setting for each database when new databases are discovered. When the setting is true, the BackupPath is such that the backups for each database are stored in their own folder. When false, the backups for all databases will be stored in the same folder.
• DefaultLogBackupScheduleUnit: In combination with the DefaultLogBackupScheduleValue setting, determines how frequently logs are backed up. These two fields use the TSQL DATEADD function. See this link: http://msdn2.microsoft.com/en-us/library/ms186819.aspx for valid values for this field. The default value for this field is ‘n’ for minute. The default for DefaultLogBackupScheduleUnit is 9. Together, these values indicate that the logs will be backed up every 9 minutes.
• DefaultLogBackupScheduleValue: See DefaultLogBackupScheduleUnit.
• DefaultLogBackupScheduleTypeIDF: Value must be either 1 for Fixed Time or 2 for Fixed Interval. See Schedule Type under concepts for more information.
• DefaultFullBackupScheduleUnit: In combination with the DefaultFullBackupScheduleUnit setting, determines how frequently full database backups occur. These two fields use the TSQL DATEADD function. See this link: http://msdn2.microsoft.com/en-us/library/ms186819.aspx for valid values for this field. The default value for this field is ‘d’ for day. The default for DefaultFullBackupScheduleValue is 1. Together, these values indicate that full backups will be occur every day.
• DefaultFullBackupScheduleValue: See DefaultFullBackupScheduleUnit.
• DefaultFullBackupScheduleTypeIDF: Value must be either 1 for Fixed Time or 2 for Fixed Interval. See Schedule Type under concepts for more information.
• BackupPath: This setting is only used when new databases are discovered on the server. Determines the default folder where backups are stored on the server.
• DaysRetain: This setting is only used when new databases are discovered on the server. Determines the default DaysRetain for the database. Days Retain indicates how long backups should be kept.

Installation Instructions:

1. Execute the TSQL scripts in this order
   1. IDBMaintenance2000_5.sql
   2. IDBMaintenance_dbo_20080205_6.sql
   3. IDBMaintenance_dbo_20080207_7.sql
   These scripts will create the IDBMaintenance database on your server. When upgrades are available, you’ll only need to run the files needed to upgrade.
   
2. Edit Configure.sql to ensure that this line of code:

   set backupPath = 'D:\Backups\Local\ProductionDatabase'
   

   will work with your server. Once the file is properly edited, run the script.
   

3. Review the records in tblJob. This table indicates all the jobs that will be run. Set Active = 0 for any jobs you don’t want to run. Add your own jobs as desired. To add a TSQL job, add the fully qualified stored procedure name (including the database name) to the cmdTSQL field in tblJob. For example, MyDB.dbo.spMyStoredProcedure
   
4. Create a windows account on the server with administrator rights that can be used to execute the jobs. Let’s call this the Maintenance User Account.
   
5. Store these two files in a fixed location on your server
   1. Backup.bat
   2. sp_BackupExecute.sql
      
6. Edit the Backup.Bat file so the server name and instance name of your SQL Server are at the very end of the line of code. In this example the name of the server is ‘LEO’ and the instance name of the SQL Server is ‘SQLEXPRESS’. The path to the sp_BackupExecute.sql is D:\Admin\DBBackup\

   osql -i D:\Admin\DBBackup\sp_BackupExecute.sql -E -SLEO\SQLEXPRESS

   Execute the Backup.Bat file manually to be sure it fires.

   Please note that the first time the file is executed that the code will reduce the size of the MSDB database and delete old backup records from this database. If your server has been in use for sometime and you have never before deleted records from the MSDB, the code may take many hours to run. This is not a bug. On subsequent runs, this job will run quickly.

7. Use the windows scheduler to execute the Backup.Bat file on a repeated, scheduled basis, like every minute.

Restore instructions:

Known Issues: