April 25, 2011 at 12:04 pm
We have started using an online backup service which keeps 30 days of a file's versions. This "versioning" allows us to make nightly database backups and have 30 days of them backed up online without using any more than one backup's amount of space from the service's point of view.
The problem: In SQL Server 2005, the Maintenance Plan we're using insists on tacking on the date and time to each backup. Basically, I want a script to remove the day and time from the filename, leaving only the year and month. This way, we won't run out of our online backup space, and we'll still have nightly backups available.
In other words, what can I enter into a .bat file to do the following?
Original filename: MUY_db_201104232230.BAK
Rename to: MUY_db_201104.BAK
April 25, 2011 at 1:44 pm
The problem: In SQL Server 2005, the Maintenance Plan we're using insists on tacking on the date and time to each backup.
I am curious about this part. Why must that date be aprt of the backup name?
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 25, 2011 at 2:07 pm
Dan.Humphries (4/25/2011)
The problem: In SQL Server 2005, the Maintenance Plan we're using insists on tacking on the date and time to each backup.
I am curious about this part. Why must that date be aprt of the backup name?
SQL Server tags the system date/time onto the backup file name when it is created. You can see this by clicking on "View T-SQL" when you setup the task, you will notice the backup file name being passed has the current YYYYMMDDHHMMSS tagged to the filename.
That I know of there is no way of removing this within the SQL task. I believe a better option would be to simply create a T-SQL script to perform your backups and then you can control how the backup name is generated. This will keep your backup job simple and in one place.
You can do a search on SSC and there should be a ton of T-SQL backup scripts that have different variations of adding the YYYYMM to the backup filename.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
April 25, 2011 at 2:15 pm
Shawn Melton (4/25/2011)
Dan.Humphries (4/25/2011)
The problem: In SQL Server 2005, the Maintenance Plan we're using insists on tacking on the date and time to each backup.
I am curious about this part. Why must that date be aprt of the backup name?
SQL Server tags the system date/time onto the backup file name when it is created. You can see this by clicking on "View T-SQL" when you setup the task, you will notice the backup file name being passed has the current YYYYMMDDHHMMSS tagged to the filename.
That I know of there is no way of removing this within the SQL task. I believe a better option would be to simply create a T-SQL script to perform your backups and then you can control how the backup name is generated. This will keep your backup job simple and in one place.
You can do a search on SSC and there should be a ton of T-SQL backup scripts that have different variations of adding the YYYYMM to the backup filename.
I don't beleive I have ever had that show up but then I have always just called the full backup job from within the maintenance plan as that seemed like the simplest route to take.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 25, 2011 at 3:26 pm
Shawn Melton (4/25/2011)
Dan.Humphries (4/25/2011)
The problem: In SQL Server 2005, the Maintenance Plan we're using insists on tacking on the date and time to each backup.
I am curious about this part. Why must that date be aprt of the backup name?
SQL Server tags the system date/time onto the backup file name when it is created. You can see this by clicking on "View T-SQL" when you setup the task, you will notice the backup file name being passed has the current YYYYMMDDHHMMSS tagged to the filename.
That I know of there is no way of removing this within the SQL task. I believe a better option would be to simply create a T-SQL script to perform your backups and then you can control how the backup name is generated. This will keep your backup job simple and in one place.
You can do a search on SSC and there should be a ton of T-SQL backup scripts that have different variations of adding the YYYYMM to the backup filename.
Yes, I've looked into this solution. But it seems like an awfully complicated solution to a simple problem. I don't want to reinvent the wheel. I just need a batch file that renames files (which Windows Server can then run daily). Trouble is, I don't have the expertise to produce this "simple" script, which is why I was hoping somebody here could help.
April 25, 2011 at 3:29 pm
Dan.Humphries (4/25/2011)
Shawn Melton (4/25/2011)
Dan.Humphries (4/25/2011)
The problem: In SQL Server 2005, the Maintenance Plan we're using insists on tacking on the date and time to each backup.
I am curious about this part. Why must that date be aprt of the backup name?
SQL Server tags the system date/time onto the backup file name when it is created. You can see this by clicking on "View T-SQL" when you setup the task, you will notice the backup file name being passed has the current YYYYMMDDHHMMSS tagged to the filename.
That I know of there is no way of removing this within the SQL task. I believe a better option would be to simply create a T-SQL script to perform your backups and then you can control how the backup name is generated. This will keep your backup job simple and in one place.
You can do a search on SSC and there should be a ton of T-SQL backup scripts that have different variations of adding the YYYYMM to the backup filename.
I don't beleive I have ever had that show up but then I have always just called the full backup job from within the maintenance plan as that seemed like the simplest route to take.
Hmm, do you mean I can create a job in the "Backup" section which will leave out datetime stamping, then call up that job in the maintenance plan? Instead of using the backup tab included in the maintenance plan? If that works, that would solve my problem!
April 28, 2011 at 2:33 pm
OK, this is somewhat off-the-cuff, so TEST before using in production!
@echo off
Set Year=%date:~-4,4%
Set Month=%date:~0,2%
{Appropriate drive letter for backup files}:
cd{appropriate path to backup files}
Forfiles /D +0 /M *.bak /C "cmd /c ren MUY_db_*.bak MUY_db_%Year%%Month%.BAK"
Now for the crash course in what is happening:
The set year and month pull only the required bits from the %date% If your date format is something other than MM/DD/YYYY, you're going to need to change it...
The way it works, is it counts characters from the end of the date string, then the portion after the comma tells it how many to read (left-to-right) So if your date format is YYYY/MM/DD, you would use %date:~0,4% for the year, and %date:~-4,2% for the month (I think, test it at a command prompt)
Forfiles is cool, and dangerous. the command above will return all files created or modified that day, with an extension of BAK, then execute the command in quotes.
As I said, this was rather off-the-cuff and rough-and-ready, so be cautious before putting it into production.
Jason
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply