SQL Server Based Backup Application

November 30, 2000

Implementing proper backup strategy is the first thing DBA does once he/she starts at a new place. If something happens to the database, DBA is the only person responsible for successful data recovery no matter what had happened to the data at the first place.

The following is a small server based application designed to handle proper database backups. Application designed to work with SQL Server 7.0 but can be easily converted to work with SQL 6.5 and SQL 2000.

The rule of thumb is - backup EVERYTHING you have. It should not matter for you if the database you have on the server considered for training purposes only or is a production version. It must be included in the backup schedule and you must confirm that the backup was successful. I had a situation once when "Northwind" database (sample db which comes with the SQL Server installation) got corrupted and five minutes later I had received a call from CTO who requested to immediately restore it because one of the users used it to store his very important procedures.

In general, backup application should provide the following functionality in order to produce successful database backup:

• Identify new databases and prepare them for the backup
• Create full backup of all databases once a day
• Create differential database backups or transaction log backup (depends on the nature of the database) in order to keep track of the changes made to the data after the full database backup was taken.
• Inform DBA if there were any errors at any step.
• For production databases, it is also very useful to run some routines in order to check data consistency and maintain optimal database performance.

 The following application covers all of the above requirements.

It might be a good idea to create a separate database .DBA. (5 MG data, 1 MG log) and use it to store tables and procedures required for this application.

Create application tables and stored procedures by running script backup_application.sql.

Application will require scheduling 2 jobs in SQL Server Agent in order to run backup procedures.

Create job called "FULL DATABASE BACKUP". Schedule this job to execute once a day every day at 2:00 AM (time will depend on when network backup will start and how busy is your server).

In "STEP 1" set command type to Transact-SQL, select database ("DBA") and in command window type:  

EXECUTE dba..sp_Backup_Data
            GO

 On the tab "ADVANCED" select "Goto next step " for both successful and unsuccessful finishes. Set file name (e.g. .E:\MSSQL7\LOG\ FULL_DB_BACKUP.LOG.) to store output file and select "OVERWRITE".

 In "STEP 2" set command type to Transact-SQL, select database ("DBA") and in command window type:

             EXECUTE dba..sp_Backup_EMail_Result
            GO

 Script Schedule_1.sql will create this job to run procedures in database DBA at 2:00AM every day, using file "C:\MSSQL7\LOG\FULL_DB_BACKUP.LOG" for the output.

 Second job is will require to run procedure sp_Backup_Log every 2 hours from 4:00 AM to 11:59 PM in order to produce differential of transaction log backups.

 Script Schedule_2.sql will create this job to run procedures in database DBA every 2 hours every day, using file "C:\MSSQL7\LOG\HOURLY_LOG_BACKUP.LOG" for the output.

 Ask your Network Admin to backup database backup files produced by this application after "FULL DATABASE BACKUP" job is finished.

 Scheduled jobs will perform the following activities:

 Schedule 1. FULL DATABASE BACKUP

 For each database on the server it will: 

• Check if every database has backup device and if not then create one
• Run integrity checks for the database
• Rebuild indexes in the database
• Update statistics for the database
• Will create a new file (it will overwrite the old one) with the full database backup

 On the last step it will check the statuses in the ACTIVITY_LOG and if there were errors at any steps it will send an email to the assigned person informing about possible problems.

 Schedule 2. HOURLY DATABASE BACKUP 

For each database on the server it will:

• Check the database options
• If option "truncate log on checkpoint" is set to ON it will skip this database
• If option "select into/bulk copy" is set to ON it will truncate transaction log and create a differential database backup
• If none of the above options are set to ON it will create a sequential transaction log backup

On the last step it will check the statuses in the ACTIVITY_LOG and if there were errors at any steps it will send an email to the assigned person informing about possible problems.

Some parameters (like "backup device path", "email address", etc.) are hard coded. Please make sure you change them before execution:

• sp_Backup_Data
• set value for the parameter @backup_device_path
• sp_Backup_EMail_Result
• set value for parameters @recipients and @attachments in both statements
• sp_Backup_Log
• set value for the parameter @backup_device_path

Status of the backup can be found in table tbl_Activity_Log in database DBA. This table captures information from each step performed by application. Table tbl_Curr_DB_Status stores information from last database backup and repopulated every time backup is finished.

I hope you found this article informative. If you have specific questions, or would like to offer suggestions on how I can improve this procedure, please write at msmirnov@swynk.com 

Disclaimer

 The scripts provided were tested to work in SQL Server 7.0 environment. However, author does not guarantee, implicitly or explicitly, the accuracy of the information provided. DO NOT use any of the scripts or suggestions provided on production servers without first thoroughly testing them on non-production servers first.