troubleshooting Question

Create SQL Server Job to perform backups of all the databases in a server instance

Avatar of Dale Fye
Dale FyeFlag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2008
17 Comments1 Solution277 ViewsLast Modified:
SQL Server 2008

I would like to create 2 scheduled jobs on a clients SQL Server to conduct nightly backups.  I had this configured several years ago and it was working great, but this past weekend my client had a power outage that lasted longer than the 6 hour UPS and the UPS software failed to shut things down elegantly when they reached the 20% power.  This caused me to have to recreate the database as the system databases were corrupted.  At any rate, that process is complete but now I'm trying to recreate the stored procedures I had configured to loop through each of the databases (including system dbs) and:

1.  On a weekly basis conduct a full backup to a folder labeled:


2.  On a nightly basis conduct an incremental backup to the same folder.

The current file names look like "databasename_backup_yyyy_mm_dd_hhmmss_XXXXXXX.bak" where the X's represent a 7 digit number which I have no idea about.

The way this was configured was that we would retain two of the full weekly backups and all of the daily backups between those so we would have backup files like:

A_backup_2017-03-03_040000.bak   incremental
A_backup_2017-03-02_040000.bak   incremental
A_backup_2017-03-01_040000.bak   incremental
A_backup_2017-02-28_040000.bak   incremental
A_backup_2017-02-27_040000.bak   incremental
A_backup_2017-02-26_040000.bak   incremental
A_backup_2017-02-25_040000.bak   full
A_backup_2017-02-24_040000.bak   incremental
A_backup_2017-02-23_040000.bak   incremental
A_backup_2017-02-22_040000.bak   incremental
A_backup_2017-02-21_040000.bak   incremental
A_backup_2017-02-20_040000.bak   incremental
A_backup_2017-02-19_040000.bak   incremental
A_backup_2017-02-18_040000.bak   full

Then, when the full backup occurs on March 4th, the full backup from 2/18 and all of the daily backups from 2/19-2/24 would be deleted.

Thanks for your help.
Tom Cieslik
IT Superintendent
Join our community to see this answer!
Unlock 1 Answer and 17 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 17 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros