Backup script for SQL Server databases

Every now and then, a sysadmin has to deal with SQL Server backups. In this article, we'll set up, and hopefully forget, a simple but effective backup strategy for SQL Server databases, using only the best backup system for SQL Server: SQL Server itself!

The backup strategy ^

Let's have two sets for backup, SET-O and SET-E, used respectively in Odd Weeks and in Even Weeks. Every Sunday, the corresponding set will be overwritten by the full backup process. Every day, a differential backup process takes place.

For databases configured for the Full Recovery model, a transaction log backup process takes place every hour. Backup files are stored on a network share. For each database, let's have two backup files per set, one for databases and one for transaction logs, if the database is configured for the full recovery model.

The backup scripts ^

Configuration

To accomplish our task, we're using three scripts (or two, if you're running all your databases in the simple recovery model). The scripts have a common configuration section that has to be consistent.

We must set the backup path, folder names for odd and even weeks, and a list of excluded databases. Find this block and change the variable definitions relative to paths according to your setup.

Do this for every script you are using.

Next, you must create the target folders.

Assuming that backups will be stored under UNC path \\YOURSERVER\YOURSHARE\YOURFOLDER\, you'll have to create SET-ODD and SET-EVEN folders on that path prior to making the backups, since the scripts don't include logic for interacting with the file system.

Full backup script

Differential backup

This is the differential backup script:

Transactions log backup

For databases configured for the full recovery model, we have to make the transaction log backup to keep it small.

Scheduling the tasks ^

Depending on which version of SQL Server you are using, there are two methods of scheduling:

  1. Via the Windows Task Manager: The script is scheduled as a command. In order for it to work, you have to copy the SQL script files to the folder of your choice (e.g., C:\etc\sqlbackup), and then call the scripts in the schedule via sqlcmd:

You can also specify the SQL Server user if needed:

  1. Via the SQL Server Agent's Job Scheduler: The script is scheduled as a T-SQL script. Simply copy the scripts in the "command" field of
Simply copy:paste the script's contents in the command text area

Simply copy:paste the script's contents in the command text area

In both solutions, you must:

  • Set up a full backup on the first day of the week.
  • Set up a differential backup other days of the week, at least one per day.
  • If you have databases in the full recovery model, set up a daily scheduled transaction log backup. Depending on how your databases are hammered, you may consider scheduling an hourly backup for transactions logs.

Troubleshooting ^

If the backup process fails, it will record events in the application log and terminate the step, raising an error.

Differential schedule every day except the first day of week

Differential schedule every day except the first day of week

Remember that either the assigned system user in the scheduled task or the SQL agent user needs write permissions to back up target folders.

Final thoughts and further improvements ^

Now we have a reliable tool for backing up our SQL databases.

Since the scripts have a lot of code in common, it is also advisable to turn the common parts into a stored procedure and put some parameters in tables, such as databases excluded from backup. I may explain this in a future tutorial.

You can download the updated version of the backup scripts from my github repo.

3+
avataravatar

Poll: Does your organization plan to introduce Artifical Intelligence?

Read 4sysops without ads and for free by becoming a member!

1 Comment
  1. Hi Riccardo,

    an addition to the post - for years we have used a script from this guy - https://ola.hallengren.com/

    It includes the backup jobs, integrity check and index optimization.

    Cheers L

    1+
    avatar

Leave a reply

Your email address will not be published. Required fields are marked *

*

© 4sysops 2006 - 2020

CONTACT US

Please ask IT administration questions in the forums. Any other messages are welcome.

Sending

Log in with your credentials

or    

Forgot your details?

Create Account