Hello fellow SQL Database Administrators,

I have some stored procedures here that you may use to backup and overwrite your database backup files and transaction log files.

This solution is appropriate for users who only want to keep one database backup on their server at a time. Every time a backup is run, the old file is instantly overwritten with the new one which can help you save space on your database server. These scripts have been successfully running in our production environment for several months. Note: a separate process from another application backs up our database server's file system to tape every night as well. So each day's tape backup has one unique backup for that day stored.

The procedure requires a parent folder parameter to hold all your backups and it will create the folder if it does not already exist. The procedure will also create subfolders for all your online databases.

The procedure backs up all online databases, checking to see if your database is not in SIMPLE backup mode. In such cases, the procedure performs a transaction log backup.

There is a database backup stored procedure and a transaction log backup stored procedure. Every time the database backup procedure runs, it deletes all the transaction logs since those transaction logs are only useful with a database backup file created previously. Database backup files and transaction log files are stored in their respective database's folder.

You can create a SQL Agent job and execute the database backup stored procedure on a nightly basis. And you can additionally create a job to execute the transaction log backup stored procedure more often. For example, you may want to backup transaction logs every 15 minutes, 4 hours, etc.

Perhaps you would like to only backup certain databases. There is a parameter where you can specify those. Perhaps you simply want to backup all databases except a few. There is also a parameter to exclude certain databases. Or, perhaps you want to place different databases backups in different locations. You can use the include and exclude parameters and create different backup sets in different locations on your server or shared drive.

Here are what sample Job Step properties would look like: