September 27, 2013 at 6:39 am
Can anyone - off the top of their heads - think of a way of preventing backups being taken to the C:\ drive?
I've run a very quick google and didn't spot anything obvious. Nothing foolproof that doesn't risk interfering with other operations strikes me. This may be a slight case of FridayAfternoonHead which will leave me with egg on my face for asking, but I'll take that If you've got something, you can point and laugh, it's OK. 🙂
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
September 27, 2013 at 6:43 am
andrew gothard (9/27/2013)
Can anyone - off the top of their heads - think of a way of preventing backups being taken to the C:\ drive?I've run a very quick google and didn't spot anything obvious. Nothing foolproof that doesn't risk interfering with other operations strikes me. This may be a slight case of FridayAfternoonHead which will leave me with egg on my face for asking, but I'll take that If you've got something, you can point and laugh, it's OK. 🙂
Only thing that I can think of is to ensure that the backup user has no access to the C Drive.
September 27, 2013 at 8:28 am
Yeah, I think the best mechanism is through security. You can't even set up a trigger on something like this.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 27, 2013 at 11:25 am
I find this question somewhat puzzling. All of my backups go to a designated backups folder. I have never created a backup in an unwanted location. So how do backups get put in the wrong place in the first place?
Just saying....
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
September 27, 2013 at 3:34 pm
Just curious what SQL Server version you are using?
2008 and 2012 have Policy Management under Management. I am sure you can find something there to limit Backup statement in stored procedures for example and to audit backup devices. There is also polcy based Audit feature under Security. You can write custom facets for Policies.
In addition I would run a Profiler trace to log all infornmation about the Backup Database statement to find out who has done it.
Limiting the access to the C drive will not help with everything because if your SQL Server is installed on the C drive, SQL Server startup account has to have access to SQL folders so anyone who has a sysadmin privilege will be able to access these folders as well as some system temp folders. People will find a way.
I think a combination of efforts with C drive security, policies, audits and process-based restrictions you can contain the problem.
Yelena
Regards,Yelena Varsha
September 27, 2013 at 4:01 pm
Kurt W. Zimmerman (9/27/2013)
I find this question somewhat puzzling. All of my backups go to a designated backups folder. I have never created a backup in an unwanted location. So how do backups get put in the wrong place in the first place?Just saying....
Kurt
That is a very good point <He Growls>. I'm currently in the process of bringing some order to a somewhat organic SQL Server estate that's grown up with no DBA over the past 14 years or so.
There are many things that are going to be controlled - this is one. Sometimes it was Developers taking copies for Dev work. This no longer happens. Sometimes it's third party vendors with typical clue level, who have had free reign - which is being addressed - who do this. Sometimes because this is hardcoded into the mess they are pleased to call software.
I could set the default backup path to - say- Z on all servers using POSH, but there is no standard structure for server setup for old servers. Hell, some only have a C:\ drive - Z may actually mean something elsewhere.
BUT, if Captain Numbnuts has hardcoded C:\ as the backup directory (no, really, I know, but yes, this is something done in more than one package) changing the default has no effect. So, if <Cluless Inc> decide not to notify me of an upgrade or put in a change control, and backup beforehand - to C:\ - I want to to fail and fail big time, and fail badly, so they have to talk to me, and I can unwrap the Lart enhanced ClueBat. And tell them that my wrath and vengeance shall be upon them, yea until the n'th generation. Where n really needs to be a BIGINT.
Seriously, I'm not clueless - this is one part of a whipping into shape project.
:angry:
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
September 27, 2013 at 4:04 pm
Grant Fritchey (9/27/2013)
Yeah, I think the best mechanism is through security. You can't even set up a trigger on something like this.
I thought about setting backup operator permissions to disallow access to C:\. There may be a way, I can't find it. But many servers are 2000 anyway.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
September 27, 2013 at 4:12 pm
Andrew,
SQL Server 2000 does have server traces. "Catch them all..." with setting a trace on Backup Database statement - text data like 'Backup Database". Then charge a chocolate cake per each backup to the C drive.
The good thing about the hardcoded SW is that it writes to the same hardcoded place. You can move the backup files right away after that with VBscript for example.
Yelena
Regards,Yelena Varsha
September 27, 2013 at 4:14 pm
Yelena Varshal (9/27/2013)
Just curious what SQL Server version you are using?2008 and 2012 have Policy Management under Management. I am sure you can find something there to limit Backup statement in stored procedures for example and to audit backup devices. There is also polcy based Audit feature under Security. You can write custom facets for Policies.
In addition I would run a Profiler trace to log all infornmation about the Backup Database statement to find out who has done it.
Limiting the access to the C drive will not help with everything because if your SQL Server is installed on the C drive, SQL Server startup account has to have access to SQL folders so anyone who has a sysadmin privilege will be able to access these folders as well as some system temp folders. People will find a way.
I think a combination of efforts with C drive security, policies, audits and process-based restrictions you can contain the problem.
Yelena
"Limiting the access to the C drive will not help with everything because if your SQL Server is installed on the C drive, SQL Server startup account has to have access to SQL folders " - absolutely, that's my main problem with preventing this. btw, a load od 2000, quit a few 2005, some 2008 / R2 some enterprise, some standard, some express, some dte, one Personal Edition 2000. Oh. And two v7. I mean, I agree with audits, but I don't like blaming. in fact I bloody hate it. I would rather prevent people from doing stupid shit, full stop. That way, I avoid prison time. :w00t:
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
September 27, 2013 at 4:24 pm
Yelena Varshal (9/27/2013)
Andrew,SQL Server 2000 does have server traces. "Catch them all..." with setting a trace on Backup Database statement - text data like 'Backup Database". Then charge a chocolate cake per each backup to the C drive.
The good thing about the hardcoded SW is that it writes to the same hardcoded place. You can move the backup files right away after that with VBscript for example.
Yelena
" charge a chocolate cake " - I'm diabetic. And the only good thing about being diabetic is it gives you an excuse to turn down cake when, basically, you hate cake. And, no, I'm just not going to get into any kind of conversation on hating cake, ever, at all. Been through enough of that shit, you may as well start a holy war as tell some people that. And /I totally take your point regarding auditing. We can find out who did it, np. I don't like finger pointing anymore than I like cake though.
I want to stop it happening.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
September 27, 2013 at 4:42 pm
Well, if you are not up to chocolate cake and you are paid to prevent problems:
- send me the name of the company where they do that
- set up an SQL Server job to send daily email reminders to whoever you know issue Backup Database statements with the subject "This is to remind you to direct your backups somewhere else...."
Maybe they will listen?
P.S. - finishing up for today. Have a good weekend!
Yelena
Regards,Yelena Varsha
September 27, 2013 at 5:24 pm
Yelena Varshal (9/27/2013)
Well, if you are not up to chocolate cake and you are paid to prevent problems:- send me the name of the company where they do that
- set up an SQL Server job to send daily email reminders to whoever you know issue Backup Database statements with the subject "This is to remind you to direct your backups somewhere else...."
Maybe they will listen?
P.S. - finishing up for today. Have a good weekend!
Yelena
Thanks for your input, and I hope you enjoy your weekend (actually, that goes out to all of you - thanks. I'll research and see if I can find an alternative to eyeballing and growling during onsite meetings). If I can suss something out, I'll see if Steve wants an article, I'm sure I'm not the only one who has wondered about this. NYTOL
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
September 28, 2013 at 11:40 am
Kurt W. Zimmerman (9/27/2013)
I find this question somewhat puzzling. All of my backups go to a designated backups folder. I have never created a backup in an unwanted location. So how do backups get put in the wrong place in the first place?Just saying....
Kurt
I am interested in the answer to this myself. We have developers that do their own backups before a code change, ignore the devices I have set up or the areas specified for backups and pop them where they want. And normally that is on the system partition...
September 28, 2013 at 11:44 am
The only thing I can think of off the top of my head this:
There has to be a user executing the backup job. You could start by preventing this user from writing to the C: drive. A good job for NTFS but have a chat with your Windows admins first.
Alternatively, you could prevent the user from taking backups.....period. If this person won't learn then he is a risk to the running of your server. Which Windows groups and SQL Server roles does this user belong to?
September 28, 2013 at 3:17 pm
kevaburg (9/28/2013)
Kurt W. Zimmerman (9/27/2013)
I find this question somewhat puzzling. All of my backups go to a designated backups folder. I have never created a backup in an unwanted location. So how do backups get put in the wrong place in the first place?Just saying....
Kurt
I am interested in the answer to this myself. We have developers that do their own backups before a code change, ignore the devices I have set up or the areas specified for backups and pop them where they want. And normally that is on the system partition...
When it's a Dev, you have the LART available. When it's a third party where some moron hardcoded the app backing up to C:\ it gets trickier
I may post this on Slashdot and see how long it takes for some git to tell me I'm stopping the developer's from being agile ...
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
Viewing 15 posts - 1 through 15 (of 40 total)
You must be logged in to reply to this topic. Login to reply