Issues running a backup from a trigger?

Question

Issues running a backup from a trigger?

Keith Knox

Mr or Mrs. 500

Points: 588
More actions
October 30, 2007 at 3:00 pm

#376605

We have a trigger on a table and when the value of a field changes we run a xp_sqlmaint command to backup the database and reset the field to a different value. The is a 3rd party app by the way.
In 2000 this works fine, but in 2005 the user needs to be a sysadmin. I don't want to add this user to the sysadmin role, this the account they use to connect to the db from the app as well. And the password is visable in the trigger.
I tried using a backup database command but I get an error saying that you can't run the backup command within a transaction. I don't know what my options are at this point. Any ideas. I am new to this dba stuff. thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Two options.

1 - Have the trigger insert a row into a RequestedJobs table, and reset the status. Create a SQL Agent job that polls the table at enatever frequency is necessary. If it finds a row requesting a backup, delete the row and start the backup.

2 - Set up service broker. Have the trigger send a message and change the status. Have the message receipt automatically run a procedure which backs up the DB.

The second will have little to no delay between the status change and the backup beginning, but is more complex to set up.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 2

You could also create a sql agent job and use sp_start_job

Keith Knox Mr or Mrs. 500 Points: 588 More actions · Answer 3

Could you be more specific regarding the agent job and the sp_start_job? I'm a new dba and don't have a good grasp yet on tsql or the commands it can do and how they relate to other objects.

thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

For the option I suggested, you need a table (call it RequestedJobs). This is just a very rough example, and may need tweaking/enhancing for your environment.

CREATE TABLE RequestedJobs (

RequestTime DATETIME,

Request VARCHAR(20)

) -- I'm not putting pk or indexes, cause it should be empty 90% of the time.

In your trigger, have the following line

INSERT INTO RequestedJobs (RequestTime, Request)

Values (GetDATE(), 'Backup')

Create a T-SQL job with the following as the step.

IF EXISTS (SELECT 1 from RequestedJobs WHERE Request = 'Backup')

begin

delete from RequestedJobs where Request = 'Backup'

Backup database ....

end

You can schedule that job as often as you want. Just bear in mind that there will be a delay between the trigger firing and the backup starting. The less often the job runs, the longer the potential delay.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 734564 More actions · Answer 5

Keep in mind a couple facts. Jobs aren't re-entrant, so if two people do this and the job is running for the backup, the second person might not get their backup.

You might want to include some type of column that differentiates various jobs in the table. That way you can reuse this table for another purpose if needed.

Keith Knox Mr or Mrs. 500 Points: 588 More actions · Answer 6

What if I use an Execute As statement in the Trigger? Will that work? Or does the account logging in to the database need sysadmin rights in order to execute the sql_maint statement in the trigger?

Thanks for the replies so far!