September 28, 2013 at 3:30 pm
kevaburg (9/28/2013)
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?
Ok, I see your point. But do you want, for example, to prevent the ql Server service user accessing c:\? Now, ideally, C:\ is os only. The SQL Server installation I on D, Data on E, Logs on F, Backups on G and if you've a requirement for it (and you certainly have for all but a trivial box IMO) H TempDB, I TempDB logs. We don't have that. And that approach is a significant risk IMO
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
September 29, 2013 at 1:26 am
andrew gothard (9/28/2013)
kevaburg (9/28/2013)
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?
Ok, I see your point. But do you want, for example, to prevent the ql Server service user accessing c:\? Now, ideally, C:\ is os only. The SQL Server installation I on D, Data on E, Logs on F, Backups on G and if you've a requirement for it (and you certainly have for all but a trivial box IMO) H TempDB, I TempDB logs. We don't have that. And that approach is a significant risk IMO
You could create a completely separate user who is only a member of the db_backupoperator.....
September 29, 2013 at 9:12 am
In addition to which drive backups are performed - I really need to be able to block someone from taking native backups on some of my systems.
I have and use Litespeed on all of my larger systems, and because I use Litespeed my backups are compressed allowing me to use less space for my backup drive.
I have had multiple instances where some vendor decided to do a native backup to the data drive, the log drive - even to the tempdb drive in a couple cases (because it is a temp drive...right).
Best case is they couldn't even run the backup because there wasn't enough space available. Worse case - there was just enough space available and they filled it with a non-compressed, native backup. I only get notified after the fact when the drive is full then have to figure out whether or not I can delete that backup safely.
I cannot prevent this because the vendors always have a sysadmin account. Tried to restrict that one time and found out that they also setup their application to use this sysadmin account also - even though they stated it was only for support to use when needed.
As long as they have sysadmin access - nothing I have been able to find to prevent them from doing whatever they want.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 29, 2013 at 9:34 am
Jeffrey Williams 3188 (9/29/2013)
In addition to which drive backups are performed - I really need to be able to block someone from taking native backups on some of my systems.I have and use Litespeed on all of my larger systems, and because I use Litespeed my backups are compressed allowing me to use less space for my backup drive.
I have had multiple instances where some vendor decided to do a native backup to the data drive, the log drive - even to the tempdb drive in a couple cases (because it is a temp drive...right).
Best case is they couldn't even run the backup because there wasn't enough space available. Worse case - there was just enough space available and they filled it with a non-compressed, native backup. I only get notified after the fact when the drive is full then have to figure out whether or not I can delete that backup safely.
I cannot prevent this because the vendors always have a sysadmin account. Tried to restrict that one time and found out that they also setup their application to use this sysadmin account also - even though they stated it was only for support to use when needed.
As long as they have sysadmin access - nothing I have been able to find to prevent them from doing whatever they want.
We won't even consider vendors that say they "need" sysadmin. They have programmed in a lazy fashion, don't know exactly what right and permissions their users need to perform its function and have increased the attack surface area on the server. SYSADMIN is for these vendors a get-out clause....
September 29, 2013 at 3:33 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...
Kill them?
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
September 29, 2013 at 3:39 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...
Kill them?
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
September 29, 2013 at 4:39 pm
andrew gothard (9/27/2013)
I'm a DBA.I'm not paid to solve problems. I'm paid to prevent them.
I absolutely love that. Well said.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 29, 2013 at 4:43 pm
andrew gothard (9/29/2013)
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...
Kill them?
BWAAA-HAAA!!!!! You took the words right out of my mouth. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
September 30, 2013 at 10:17 am
Anyone tried to create a trigger on a system table?
Could we do Instead of (or other type of) trigger on [msdb].[dbo].[backupmediafamily] ? The backup location is inserted there into the field [physical_device_name] If the path in this file name contains C:\, then check the backup size, check available drive space on the correct backup drive and move the backup file to there. And send appropriate emails.
Microsoft does not recommend creating triggers on system tables with this cryptic note in Create Trigger statement article, but they don't say we cannot do that.
http://technet.microsoft.com/en-us/library/ms189799.aspx
"Because SQL Server does not support user-defined triggers on system tables, we recommend that you do not create user-defined triggers on system tables."
Have not tried that myself.
Regards,Yelena Varsha
October 1, 2013 at 2:11 am
assuming your c: drive is only used for the OS could you use disk quotas in windows to limit the amount of disk space visible such that attempting to take a backup will fail.
October 1, 2013 at 6:08 am
I think the resolution is more a function of control. As a DBA restrict access to those developers who don't play by the rules. Make them come to the DBA for any DBA work such as backups/restores. The justification is simply a matter of controlling a situation that is out of control and has the possibility of greater problems. It is a matter of taking ownership of the environment.
Once I started my new position I've taken over ALL DBA roles. I'll get handed off a machine from Operations and once I have it's is my responsibility. Operations and developers come to me if something needs to be done once I'm in control of the box.
Just saying
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
October 1, 2013 at 6:18 am
Kurt W. Zimmerman (10/1/2013)
I think the resolution is more a function of control. As a DBA restrict access to those developers who don't at by the rules. Make them come to the DBA for any DBA work such as backups/restores. The justification is simply a matter of controlling a situation that is out of control and has the possibity of greater problems. It is a matter of taking ownership of the environment.Once I started my new position I've taken over ALL DBA roles. I'll get handed off a machine from Operations and once I have itit is my responsibility. Operations and developers come to me if something needs to be done once I'm in control of the box.
Just saying
You have got it exactly right! And that unfortunately is also the reason why some (not all) developers think of DBAs as roadblocks to production!
October 1, 2013 at 7:12 am
kevaburg (10/1/2013)
Kurt W. Zimmerman (10/1/2013)
I think the resolution is more a function of control. As a DBA restrict access to those developers who don't at by the rules. Make them come to the DBA for any DBA work such as backups/restores. The justification is simply a matter of controlling a situation that is out of control and has the possibity of greater problems. It is a matter of taking ownership of the environment.Once I started my new position I've taken over ALL DBA roles. I'll get handed off a machine from Operations and once I have itit is my responsibility. Operations and developers come to me if something needs to be done once I'm in control of the box.
Just saying
You have got it exactly right! And that unfortunately is also the reason why some (not all) developers think of DBAs as roadblocks to production!
Small price to pay by being a good DBA... 😉
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
October 1, 2013 at 7:23 am
andrew gothard (9/27/2013)
--------------------------------------------------------------------------------
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
I absolutely love that. Well said.
I do too. Great saying to have somewhere to remind you of your real purpose.
October 1, 2013 at 7:45 am
Kurt W. Zimmerman (10/1/2013)
kevaburg (10/1/2013)
Kurt W. Zimmerman (10/1/2013)
I think the resolution is more a function of control. As a DBA restrict access to those developers who don't at by the rules. Make them come to the DBA for any DBA work such as backups/restores. The justification is simply a matter of controlling a situation that is out of control and has the possibity of greater problems. It is a matter of taking ownership of the environment.Once I started my new position I've taken over ALL DBA roles. I'll get handed off a machine from Operations and once I have itit is my responsibility. Operations and developers come to me if something needs to be done once I'm in control of the box.
Just saying
You have got it exactly right! And that unfortunately is also the reason why some (not all) developers think of DBAs as roadblocks to production!
Small price to pay by being a good DBA... 😉
+1000 to that.
I'll also add that good DBAs aren't actually roadblocks. They're enablers with "best practices" in mind to not only protect the data, but to protect the developers, as well. Good DBAs usually know how to get the {database} job done much faster, more accurately, more repeatably, and in a much safer fashion.
For example, blowing up a C: drive on a machine is more of a roadblock than any DBA would ever be. That would be a very bad day for the happless developer that did it. If there's something like a backup that the developers needed to do at the drop of a hat, I'd build a proc or job or combination of the two that would allow the developers to do the backup but in a tested, controlled, and properly configured fashion. In other words, I'd enable them to do it easily and correctly instead of wasting my time trying to figure out how to prevent making backups to the C drive. Then, I'd teach them how to use it.
Developers and DBAs need to understand that they're on the same team. The DBA needs to help the devs to their jobs and protect them in the process. The only way that works is if everyone knows that and works together to achieve it. And management buy in is essential.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply