[Prior Post] [Next Post]
Getting backups scheduled appropriately tends to be a slow process with a lot of keying and mouse clicks. The process can be simplified by the use of a couple of scripts that will backup every database on your SQL Server (including databases added to your SQL Server later! )
There are three types of backups:
The pattern that I coded below is to:
If this pattern is not satisfactory for you needs, feel free to leave a comment on what you need and I will provide it in a follow up post.
I am providing you the option of doing two types of backups:
Roll-over backups allow the latest version to be recovered only, with the advantage that you reuse disk space. Weekly-distinct backups have the disadvantage of consuming more and more disk space, but they allow you to recover to any point in the past , as long as you have a backup for it. The table below illustrates the backup files you will see for database “ISV”.
|ISV Backup.bak||ISV 20110430.bak|
The choice often depends on any potential need to rollback to a point of time. Typical needs driving weekly distinct backups are:
The scripts build on each other, so I will describe them from the bottom up.
The scripts are adaptive . If you add a database, or the server is down when a backup should happen, the scripts adapt to these conditions.
This script takes four parameters that you may wish to change the default values for:
There are many options available as shown in help . I tried to keep things simple but I decided to put compression on because it detects disk write errors.
When this executes, I print out the backup TSQL commands executed to facilitate debugging.
This script walks the databases and calls the above script for each one. I assumed @Overwrite is defaulted to the appropriate value above and retained only two of the parameters.
I would suggest changing the Catch block to send a notification out, for example an email.
The script below will add jobs to call the above script if they do not exist and replace jobs if they do exist.
In the SQLISV_ScheduleBackupAllDatabases code above, I specified @notify_level_eventlog=3. This setting means that successful and unsuccessful backups are recorded in the Event Log as shown below.
Some of the backups failed, so I needed to investigate why. I can manually execute the backup and attempt to identify what the problem is. An example of this process is shown below.
The code above tests for this condition and omits the log backups for databases with a SIMPLE recovery model. This was a contrived failure. If you encounter any failures, please comment below.
The following are some technical points that may be of some interest.
My experience is that getting backups configured is tedious work, prone to errors. This post shows you how you can automate the entire process by just downloading the TSQL script here and executing it once on each server. The backup pattern may not be perfect, but it is better than what I have often seen. The scripts above should be robust, and best of all, if you add a database to SQL Server it will be included in the backups automatically.
I had a discussion with Shaun Tinline-Jones of the SQL Server Customer Advisory Team - SQL Server Best Practices team(SQLCAT) about where to locate this procedure and other like it. Should it be in [msdb], [model],[master], an existing ISV related database or a new database. Our conclusion was a new database named [SQLTools] was best (instead of [SQLISV] or [SQLCAT]).
To create this database
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.