So the dbs get dumped to .bak files and the transaction logs get dumped to .trn files in c:\backup\[name of db]\[name of db][timestamp].[bak/trn]
I gots a batch file that is designed to keep a week of these in a sliding window so renames all of this into relative names corresponding with the day of the week (db) or just a generic time (trans log). Some of the variables used for reference:
set db1=[name of db]
One of the problems I ran into was that SQL Server 2005 names files like this:
[name of db]_backup_201311052100.trn
So I can do something like this:
copy /v /y /z "%sourcepath%\%db1%\%db1%_backup_%YYYY%%MM%%DD%%TIMENOW%.trn" "%destpath%\%db1%\%db1%_backup_%TIMENOW%.trn"
(and then ZIP it up)
However, SQL Server 2012 names files like this:
[name of db]_backup_2013_11_08_113001_3880055.trn
AHHH what is that junk on the end?!? Anyway, I had to come up with this workaround:
move "%sourcepath%\%db1%\%db1%_backup_%YYYY%_%MM%_%DD%_%TIMENOW%*.trn" "%sourcepath%\%db1%\%db1%_backup_%YYYY%_%MM%_%DD%_%TIMENOW%.trn"
copy /v /y /z "%sourcepath%\%db1%\%db1%_backup_%YYYY%_%MM%_%DD%_%TIMENOW%.trn" "%destpath%\%db1%\%db1%_backup_%TIMENOW%.trn"
Move lets me use a wildcard without prompting. Copy would just copy the name of the file into the destination. Xcopy wanted to know if the destination was a file or a directory and prompted me. So I jumped through those hoops. However, the "fun" part was figuring out task scheduler on Win2K8/Win2K8R2 as I really only used it on Win2K3 before. Looks like permissions are more of a pain on 2K8. Even though I was running the task as a domain admin, it couldn't do anything with the files. No renaming, no copying. I had some debug lines echoing out stuff that proved it ran, but I'd get empty ZIP files and the renames wouldn't work. I tried ye olde "the .bat should be just itself and use the path in the Start In field" and that didn't seem to help (reference: http://richardstk.com/2012/06/15/schedu ... atch-file/ ). I ended up giving Users full rights on both the sourcepath and destpath folders and that got my file operations working. Alternatively, I could have tried running the task as SYSTEM but that was my next attempt and I got it working before trying it. I admit I don't understand why I had to give Users full access since the scheduled task is running as a domain admin. Maybe SYSTEM is a better route. Ok, I just removed those permissions and ran as SYSTEM. Worked fine. I guess running batch files in scheduled tasks really should be run as SYSTEM and not some wimpy domain admin. Gah, it runs as a local admin to the server, too. Stupid domain admin, no rights for you!
I wanted to post this as a PSA because my searching mainly found people with similar issues who didn't have a solution found. And "helpful" people saying how the posters obviously didn't understand how permissions worked and how programs ran and on and on about anything except something helpful. Domain admins here should be GOD. I don't understand why they aren't.
Hopefully this helps someone else who's run into this.