Must have a backup to the specific location only

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Must have a backup to the specific location only

Author

Topic

devenoza
Starting Member

14 Posts

Posted - 2013-03-07 : 15:19:55

my company management has decided to have a backup to the nas share folder so if any users/DBAs are trying to take a backup to another location (i.e to thier local drive) they would not be able to do that, how can I achieve this task please? I was thinking of using policy based management but not sure how can I apply to many servers. I would appriciate your thoughts on this.
Thanks... Dev

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-03-07 : 16:30:49

It's not possible. Restrict the access to only qualified people who follow directions.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

robvolk
Most Valuable Yak

15732 Posts

Posted - 2013-03-07 : 16:49:54

I agree with Tara. Restrict backup permissions, and if possible, restrict backups to local drives only, then have a separate process copy/move them to the NAS.

I have a crazy idea (who, me?) that might be an option, but it's a lot of work and I don't have the skill at this point. Basically:

1. Create an event notification for AUDIT_BACKUP_RESTORE_EVENTs
2. Have an activation procedure on the queue for that notification
3. The procedure parses the event data (TextData and SPID nodes) to see if they are backing up to an unauthorized location
4. Kill the spid that's doing it

#3 is going to cause you a lot of grief since it's text parsing, and it's easily circumvented using the WITH MIRROR option. Plus the fact that anyone with access to the NAS can copy backups unless you lock that down, which is the key part of Tara's suggestion.

devenoza
Starting Member

14 Posts

Posted - 2013-03-08 : 00:14:06

Thanks Tara and Rob for your reply... if this is not possible or possible with crazy idea :-) then I would leave upto my seniors and will see if they come up with other solution... once again thank you...

mbourgon
Starting Member

6 Posts

Posted - 2013-03-28 : 14:27:54

Okay, so it's doable... with a catch. (well, several)
The catch will differ depending on what you want to do.

Catch 1:
You would think you could use a Trigger on the table. Not easily - "KILL command cannot be used inside user transactions."
The code you'd use, for your activated Stored Procedure, should use transactions. Which means you'd have to set your code to deal with the messages, THEN write to the table, which would fire the trigger.
However, without being transactional, it could lose messages.

Catch 2:
Modifying the activated stored procedure to kill the transaction.
The catch is that the stored procedure doesn't have rights to kill it ("User does not have permission to use the KILL statement.")
Fixing that... I'm not sure offhand, honestly. It may require granting a particular user the rights to do so, it may require certificates... heck, it might require TRUSTWORTHY. Ugh.

Catch 3: this is probably the one to go with, if 2 doesn't work. Note that I haven't tested the job - but I've already spent my lunch hour playing with this, so that's it for now, sorry.
The backup must take more than a minute to run. And this could kill other code...
IF SO...
Build the EN. Queue, Service, EN.
Set up an activated SP that will write to a table (I used Parse_EN_Messages, from my blog, thebakingdba dot blogspot dot com, the rest of the code is below).
Make sure the code in Parse_EN_Messages points at the new Queue (it does so in multiple places), and modify the enaudit_events table & parse_en_messages to add the TextData field.
Now, set up a job that runs as often as possible (once a minute; you can't easily do it continuously since you want to make sure you don't keep killing it after it's already died)
If there are, grab the SPID and KILL it. Since it's a job separate from the Queue, I don't see any problems with it.

Here's the code to build things... you'll have to do the Parse_EN_Message yourself - too long for here.

CREATE DATABASE BackupWatcher
GO
ALTER DATABASE BackupWatcher SET ENABLE_BROKER;
go
USE BackupWatcher
go
CREATE QUEUE BackupNotifyQueue
GO
create SERVICE BackupNotifyService
ON QUEUE BackupNotifyQueue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO
CREATE EVENT NOTIFICATION BackupNotifyEvent
ON SERVER
WITH FAN_IN
FOR AUDIT_BACKUP_RESTORE_EVENT
TO SERVICE 'BackupNotifyService', 'current database';
GO

BACKUP DATABASE BackupWatcher TO DISK='BackupWatcher3.bak'
go
--set up PARSE_EN_MESSAGES and necessary tables, changing the Queue Name in the SP to BackupNotifyQueue
ALTER QUEUE BackupNotifyQueue WITH STATUS = ON, RETENTION = OFF ,ACTIVATION(STATUS = ON, PROCEDURE_NAME = [dbo].[Parse_EN_Messages], MAX_QUEUE_READERS = 2, EXECUTE AS owner)

---and here's the code for your job
DECLARE @now SMALLDATETIME, @sql NVARCHAR(100), @min INT, @max INT
SET @now = GETDATE()
DECLARE @spids_to_kill TABLE (id int IDENTITY, spid NVARCHAR(10))

INSERT INTO @spids_to_kill
SELECT spid FROM enaudit_events
WHERE eventtype = 'AUDIT_BACKUP_RESTORE_EVENT'
AND TextData NOT LIKE '%\\youracceptedpathhere%' --or whatever location you want
AND insert_datetime > DATEADD(mi,-1,@now)

IF (SELECT COUNT(*) FROM [@spids_to_kill]) > 0
BEGIN
SELECT @min =MIN(id), @max = MAX(id) FROM @spids_to_kill
WHILE @min <= @max
BEGIN
SELECT @sql = 'KILL ' + spid FROM @spids_to_kill WHERE ID = @min
IF (SELECT COUNT(*) FROM sys.sysprocesses WHERE spid = @min AND command LIKE '%backup database%') >0
--we do that so it doesn't accidentally kill a SPID once the backup is done
BEGIN
EXEC sp_executesql @sql
END
SET @min = @min+1
END
END

devenoza
Starting Member

14 Posts

Posted - 2013-05-30 : 15:24:50

Thanks mbourgon! I will try this way and let you know how it goes :-)

d3goldnews
Starting Member

3 Posts

Posted - 2013-05-31 : 22:02:57

I think so. I think your article will give those people a good reminding. And they will express thanks to you later.

__________________
I'd rather be a happy fool than a sad sage.
unspammed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources