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: The code is stored in one database named IDBMaintenance. The windows
scheduler executes a.bat file that uses OSQL to excute the splDBMaintain stored
procedure in the IDBMaintenance database. This stored procedure executes all jobs
that are due for execution.
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:
- Full Backup
- Log Backup
- Monthly Backup
- TSQL
TSQL jobs can be used to schedule execution of any stored
procedure.
Settings:
The following settings are available to customize the
configuration.
- 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:
- Execute the TSQL scripts in this order
- IDBMaintenance2000_5.sql
- IDBMaintenance_dbo_20080205_6.sql
- 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.
- 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.
- 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
- 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.
- Store these two files in a fixed location on your server
- Backup.bat
- sp_BackupExecute.sql
- 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.
- Use the windows scheduler to execute the Backup.Bat file on a repeated,
scheduled basis, like every minute.
Restore instructions:IDBMaintenance provides two stored procedures that can be
used to generate a restore script. Use spRestore if the full backups are not in
the same folder as the log backups. Simply call the procedures in Query Analyzer
or SQL Server Management Studio. The procedure will print the restore script in
the results window. You can then copy and paste the restore script to a new query.
Edit if needed, and execute to restore your database.
Known Issues: No database on your server should have the underscore character ‘_’
as part of the database name. The stored procedure dbo.spRestoreFromFolder will
not function proprely if you violate this rule. You can use spRestore in this case.
|