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!
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.
Create the Backup Job
This create a nightly 12:00 am backup of all databases
Now you have you full database backup jobs next step is to create transactional backup jobs
Create stored procedure to backup the transaction logs
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
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.
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