SQL Server Based Backup Application

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:MSSQL7LOG 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:MSSQL7LOGFULL_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:MSSQL7LOGHOURLY_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 [email protected] 

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.

Download Scripts