SQL Server 2008: Backing up database before running stored procedure

Question

I have a stored procedure that runs once a week that's initiated by the user through the click of a button on our internal website. I'd like to automatically backup the database before the code in the procedure actually runs.

So I've created a separate stored procedure with the following code:

DECLARE @Path varchar(50)
SET @Path = '\\1.1.1.1\SQLBackup\DBName' + convert(varchar, getdate(), 10) +'.bak' 

BACKUP DATABASE [DBName] TO  DISK = @Path WITH NOFORMAT, NOINIT,  NAME = N'DBname-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

I then run the above from my other stored procedure by using

EXEC GenerateRenewalBackup

The problem is that the application times out on the database backup before it even runs the code in the second stored procedure.

Am I going about this the right way? The database is only 38MB in size. Would it be better to configure the database as a job? Can you run a job from a stored procedure? Any guidance would be very helpful. Thank you.

Answer 1

Create a SQL Agent job:.

1. First step, Backup database.
2. Second Step, run your stored procedure
3. Define a schedule for job to be run.

Answer 2

Can you run a job from a stored procedure?

Yes you can, use sp_startjob to start a job or just schedule the job to run once a day

Answer 3

The SQL is probably fine, but it does not make sense for this to be a synchronous operation in the web app. Fire the the SP off asynchronously then do something like send the user an email to tell them their action has completed.

Answer 4

Run a full backup the night before (why not on such a small db) and then only a differential or transaction log backup before the proc runs.

Answer 5

First of all, add a WITH COPY_ONLY clause to your backup so that it does not interfere with a ordinary maintenance backup chain (If this is your backup chain, then is wrong anyway and you need a proper maintenance plan in place).

Next, you can either:

• Increase the CommandTimeout on your application so that it waits until the procedure finishes (default times out in 30 seconds).
• Make the button create/start an SQL Agent job right away instead of running the procedure.
• Use asynchronous procedure execution.

Which one is the right choice, depends on many factors only you know. My bet would be that this should be a regular scheduled job and there should be no user interaction, but without understanding the business process driving this is impossible to guess.