DBA Stuff

Disaster Recovery and Maintenance

The primary job of a database administrator are to make sure there is no disruption to business or loss of work. Backup and Maintenance are 2 of the most important things to accomplice this

Creating a Database Backup Job

The following scripts are code that I created at Keyspan Energy Services and I have use many times, these scripts will create a database backup job that will perform a nightly backup and a transaction log backup every hour. The backups full and transactional will be saved on the same device. This beats any of the blackbox Database Maintenance that comes with SQL Server because you can fully customize your backups to your needs and see the history in the jobs steps. I also have included a simple database maintenance script that runs a  dbcc dbreindex and re-compiles all stored procedures.

This code should be run in a test environment and tested. This code is as is and I do not provide any support and am not responsible from any problems that may be introduced by using this code! 

SQL Job for database backups
Create a backup procedure

First we need to enable xp_cmdshell so the procedure can do file management. There are some risks to allowing file access to SQL Server. See Stop SQL Injection Attacks Before They Stop You. If your server is compromised then they have control of you OS and file system. If you follow simple guidelines you will should not ever have to deal with this!

sp_configure 'xp_cmdshell', 1
GO
reconfigure
GO

Create the stored procedure to backup databases

This is used by the jobs we are going to create.

sp_backupDatabase

Create the Backup Job

This create a nightly 12:00 am backup of all databases

Create_Backup_Job

Now you have you full database backup jobs next step is to create transactional backup jobs

Create stored procedure to backup the transaction logs

sp_backupLog

Create a job that backs up your databases every 30 minutes

This creates a SQL Agent Job that backs up any database that are in the full recovery mode

Create_Logbackup_Job

Database Maintenance

This code creates a sql database maintenance job that runs nightly. Again you should test an customize so that the job does not overlap with your full backup job. This job does not run dbcc update statistics as that can introduce delays where the job runs into the daytime.

Create_Database_Maintenance_Job

At this point you have a sql server that backs all databases up to a single drive and run a transaction log backup every 30 minutes