I'm migrating some homegrown SQL backup scripts from Win2K3 and SQL Server 2005 Standard to Win2K8 R2 with SQL Server 2012 Standard. I got the maintenance plans moved over after some debugging ("Why aren't transaction logs being dumped every 15 minutes? I set it to... oh, every 15 hours."

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:

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.

Edit: Though I have to say, this thread taught me something. I never knew the venerable cmd.exe shell had substring operators!

Only the NT-based command shell. You have sophisticated for's, and tab completion was enabled since XP (Win2K had it but you need to enable it yourself in the registry).

Yes, I know about the existence of tab completion; but the cmd.exe implementation has always seemed a little squirrely to me. When there are multiple possibilities it seems like it just picks one at random, instead of completing up to the point of ambiguity and allowing you to key in more characters to narrow it down?

Edit: Though I have to say, this thread taught me something. I never knew the venerable cmd.exe shell had substring operators!

Only the NT-based command shell. You have sophisticated for's, and tab completion was enabled since XP (Win2K had it but you need to enable it yourself in the registry).

Yes, I know about the existence of tab completion; but the cmd.exe implementation has always seemed a little squirrely to me. When there are multiple possibilities it seems like it just picks one at random, instead of completing up to the point of ambiguity and allowing you to key in more characters to narrow it down?

I think it is actually the reverse. It scrolls through alphabetically (?) from your last typed input. So if you have files/folders name "F1", "F2", "F3", and you have typed "F". Then tabbing will cycle through "1", "2", and "3".

The Model M is not for the faint of heart. You either like them or hate them.

I don't know powershell and the last time I tried to run a script I had to jump through a bunch of hoops to allow local execution. Like, you can't just double click a ps file or run it from the command line without turning off the default security. Oh, maybe it was because I didn't sign the code with a certificate. Something dumb. If I am going to jump through those hoops I will just install visual studio and learn .net or something.

ChronoReverse wrote:

Or even vbs.

That's Visual Basic scripting, right? I've looked into that and it's the same thing. If I want to go that route I will learn a proper programming language. I have programmed before but it was over a decade ago and I don't know the way to make stuff in a GUI. As in, I'd compile CLI stuff using cc (before gcc) and PASCAL. The time commitment in learning vbs or ps will be the same for me as .net or refreshing my c/c++.

Batch files and built in utilities work for just about everything I need to automate. Plus, I secretly wish we all still ran MS-DOS 6.22

I know I should care about Microsoft's whole bash/perl ripoff called powershell because everything in the GUI frontend now just runs ps commands in the background, but hell, I have yet to find a case I needed it besides some arcane crap to "fix" Exchange mailboxes in a manner you can't do from the management console. Though not sure if that fixed what ailed us at the time anyway.

I don't know powershell and the last time I tried to run a script I had to jump through a bunch of hoops to allow local execution. Like, you can't just double click a ps file or run it from the command line without turning off the default security. Oh, maybe it was because I didn't sign the code with a certificate. Something dumb. If I am going to jump through those hoops I will just install visual studio and learn .net or something.

Under a PowerShell command "shell" (yes, they tell you this is the new cmd window that IT admins should have opened all the time), type "Set-ExecutionPolicy -RemoteSigned" (or Unrestricted if you want semi-god mode).

Scrotos wrote:

ChronoReverse wrote:

Or even vbs.

That's Visual Basic scripting, right? I've looked into that and it's the same thing. If I want to go that route I will learn a proper programming language. I have programmed before but it was over a decade ago and I don't know the way to make stuff in a GUI. As in, I'd compile CLI stuff using cc (before gcc) and PASCAL. The time commitment in learning vbs or ps will be the same for me as .net or refreshing my c/c++.

VBScript is the proper name. Actually VBScript does not require compiling. Last I checked, unless you are touching the protected stuff under UAC, you shouldn't be prompted when launching a .VBS file? You can even do simple GUI MessageBox in VBScript with a one line call. The cscript command forces the script to be run in text mode as well (unless you use that GUI MessageBox thing).

Scrotos wrote:

I know I should care about Microsoft's whole bash/perl ripoff called powershell because everything in the GUI frontend now just runs ps commands in the background, but hell, I have yet to find a case I needed it besides some arcane crap to "fix" Exchange mailboxes in a manner you can't do from the management console. Though not sure if that fixed what ailed us at the time anyway.

PowerShell is no bash/perl ripoff. While they call it a scripting language, it is really more and the more sophisticated scripts out there resemble more like a .NET program than a script. You have all the modularization constructs, and you can even dip in and call .NET objects (the syntax is rather clunky for construction, but after that it is fairly decent if you have a .NET background). If you have Microsoft's server products to manage (looks like you have Exchange already, and if you have any of the System Center stuff...), PowerShell is the way to go.

The Model M is not for the faint of heart. You either like them or hate them.

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.

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.

Domain Admins don't have rights to the folders where MS SQL is installed by default. There are quite a few areas on Win Server 2008R2 that the Domain Admin doesn't have rights to, but it will pop up a helpful dialog box asking if you want access to them. The reason is Windows is highly infectable, and people have a tendency to run with more rights then they really need. There are more then a couple people out there who have given their normal user Domain Admin rights then infected their PC. Can you see where that is going? God like power over the network plus a malware infection equals a giant mess.

In the past, Windows was insecure by default, and MS has been trying to change that. Part of that is shielding sensitive parts of programs and the operating system from people by only giving out minimal rights. This can all be bypassed by "Run as administrator" or disabling UAC.

There is a box on the General tab of Task Scheduler that says "Run with highest privileges". If you check that box, the task will run as "Administrator". That probably would have helped.

Scrotos wrote:

I don't know powershell and the last time I tried to run a script I had to jump through a bunch of hoops to allow local execution. Like, you can't just double click a ps file or run it from the command line without turning off the default security. Oh, maybe it was because I didn't sign the code with a certificate. Something dumb. If I am going to jump through those hoops I will just install visual studio and learn .net or something.

The security settings and it's overly verbose syntax is why I gave up on PowerShell. It's a great concept, an object oriented shell, but it's just not easy to work with. It really needs to be streamlined, so it's not quite so obtuse. I really wanted to like it, but it just did everything it could to make me hate it.

For me, shell is just something quick, dirty, and easy to fix a problem or automate some commands. Frequently, end users are running the scripts in some form or fashion, so emphasis on easy.

Flying Fox wrote:

PowerShell is no bash/perl ripoff.

It's more like a BASH and Python/Ruby hybrid. It's like they took the Python/Ruby interactive interpreter and made it a full shell like BASH. Then said, let's make the user write a novel in triplicate to get anything done.

Domain Admins don't have rights to the folders where MS SQL is installed by default. There are quite a few areas on Win Server 2008R2 that the Domain Admin doesn't have rights to, but it will pop up a helpful dialog box asking if you want access to them. The reason is Windows is highly infectable, and people have a tendency to run with more rights then they really need. There are more then a couple people out there who have given their normal user Domain Admin rights then infected their PC. Can you see where that is going? God like power over the network plus a malware infection equals a giant mess.

In the past, Windows was insecure by default, and MS has been trying to change that. Part of that is shielding sensitive parts of programs and the operating system from people by only giving out minimal rights. This can all be bypassed by "Run as administrator" or disabling UAC.

There is a box on the General tab of Task Scheduler that says "Run with highest privileges". If you check that box, the task will run as "Administrator". That probably would have helped.

Aye, that scared me off and I also figured, "I'm domain GOD, why would I care about that?" I guess ever since Vista, logging in as "root" in the Windows world just doesn't do what it used to.

Just to clarify, this stuff was being dumped in folders unrelated to the SQL install. That install is all in Program Files where it should be. I was just accessing two user-made folders where SQL maintenance plans dumped backups and I copied those backups into another folder.

Flatland_Spider wrote:

For me, shell is just something quick, dirty, and easy to fix a problem or automate some commands. Frequently, end users are running the scripts in some form or fashion, so emphasis on easy.

Aye, that's why I keep with .bat instead of learning ps or vbs. To Fox's point, I know you don't compile vbs but my contention is if I'm going to take the time to learn the syntax, I might as well just use the time to learn a real programming language.