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:
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.