Why SQL Server sucked on Friday
-
Our application uses SQL Server 2005 Express for a couple small data bases. I was adding logic to our migration app (moves the product from one machine to another) to backup the data bases. The simplest way to do this was to spawn SQLCMD to execute a backup command:
BACKUP DATABASE CPT TO DISK='C:\...\FILE.BAK'
It worked fine at the command line, producing a nice backup file I could then include in the migration data. Spawning SQLCMD from my migration tool was another story however. The command created the backup file, which my app would then get an 'access denied' error. The file exists in the path I specified, and is the right size. Right-click, Properties, select the Security tab, and I get a message that I'm not allowed to view the security properties of the file unless I take ownership of it. What. The. Fuck. As closely as I can tell, when SQLCMD is run from a command window, it tells the SQL service to create the backup file with administrator access. If SQLCMD is run from another process, the SQL service creates the backup file under the account it's running under, usually Network Service. In other words, it creates a file for you that you can't use. After much cursing, Googling, a brief foray into the MSDN, and stifling the urge to e-mail a pocket nuke to Redmond, I discovered the solution. If you create the backup file yourself (with zero length), SQL Server will go ahead and 'append' to the existing file, leaving the file ownership and security settings alone. Adding two lines of code, a call to
CreateFile()and one toCloseHandle()fixed the problem. What pisses me off about this is that, since they clearly know how to set the file ownership properly when the command is run interactively, why not do it all the time? Isn't is harder to screw this up than it is to do it right? :mad:Software Zen:
delete this; -
Our application uses SQL Server 2005 Express for a couple small data bases. I was adding logic to our migration app (moves the product from one machine to another) to backup the data bases. The simplest way to do this was to spawn SQLCMD to execute a backup command:
BACKUP DATABASE CPT TO DISK='C:\...\FILE.BAK'
It worked fine at the command line, producing a nice backup file I could then include in the migration data. Spawning SQLCMD from my migration tool was another story however. The command created the backup file, which my app would then get an 'access denied' error. The file exists in the path I specified, and is the right size. Right-click, Properties, select the Security tab, and I get a message that I'm not allowed to view the security properties of the file unless I take ownership of it. What. The. Fuck. As closely as I can tell, when SQLCMD is run from a command window, it tells the SQL service to create the backup file with administrator access. If SQLCMD is run from another process, the SQL service creates the backup file under the account it's running under, usually Network Service. In other words, it creates a file for you that you can't use. After much cursing, Googling, a brief foray into the MSDN, and stifling the urge to e-mail a pocket nuke to Redmond, I discovered the solution. If you create the backup file yourself (with zero length), SQL Server will go ahead and 'append' to the existing file, leaving the file ownership and security settings alone. Adding two lines of code, a call to
CreateFile()and one toCloseHandle()fixed the problem. What pisses me off about this is that, since they clearly know how to set the file ownership properly when the command is run interactively, why not do it all the time? Isn't is harder to screw this up than it is to do it right? :mad:Software Zen:
delete this;You are forgetting something: MS is "customer focussed". As in, MS likes to "foc" it's customers in the "uss" as much as it can...
Ideological Purity is no substitute for being able to stick your thumb down a pipe to stop the water
-
You are forgetting something: MS is "customer focussed". As in, MS likes to "foc" it's customers in the "uss" as much as it can...
Ideological Purity is no substitute for being able to stick your thumb down a pipe to stop the water
The least they could do is take me out to dinner and a movie first.
Software Zen:
delete this; -
The least they could do is take me out to dinner and a movie first.
Software Zen:
delete this;
