Running a schdule backup automatically

rafiqahmeds

Hello there,

As i am new to sql server administration, i would like to hear out from you people regarding the possible solution to my query.

will explain in details below.

1)I would like to run a schedule backup every week on a particular day on a particular time.

after the backup happens i want to shrink the log file and again after the log file shrinks

I would like to re-run whole backup once again, all this process should happen on a weekly basis without any human interference. Kindly let me know if this is possible to do on a production server. is there any query to run this?thank you in advance.

Cheers

Chris.Allen

I believe that your requirements can be fulfilled using a combination of SQL backup and editing the SQL Server agent job.

The only thing I am not totally confident in is perfect sychronization of the second backup procedure exactly after the log shrink command. I will do some more research on this.

So, firstly, create a regular, scheduled backup job in the Red gate SQL backup wizard (I'm going to assume you're familiar with this step).

If you have a non-EXPRESS version of SQL Server this will have created a SQL server agent job which you can inspect in SSMS.

Open up the job and look at step 1. You should see the backup job here. Copy the T-SQL and we'll use this in the last step.

The next thins is- Insert a new step that will shrink the log:

DBCC SHRINKFILE (Transact-SQL)

Then create the last step by inserting and copying in the same T-SQL that is in the first step.

rafiqahmeds

Hi Chris,

Thank you for the update, in mean time i would like to know weather did you got time to check on my query?

Cheers!!!

cunhaefc

This was just what I was looking for, was having a hard time figuring out how to setup scheduling. Thanks! A quick question though (I'm a bit new to this, so bear with me), what is the purpose of re-running a backup right after having shrunk down the log file like rafiqahmeds was asking?

Chris.Allen

i would like to know weather did you got time to check on my query?

So sorry for delay- I have been away. I did verify the instructions I gave and found they work well because the agent ensure everything runs in the correct sequence.

what is the purpose of re-running a backup right after having shrunk down the log file like rafiqahmeds was asking?

I think the strategy here is:

1. Ensure you always have a back-up
2. Take extra step to create an 'efficient' back-up- one that has no redundant information in.

The extra step is not essential but if ever the backup needs to be transported- you've got the absolute minimal size of backup handy.

Its quite an unusual strategy since step 2 is not usually necessary but it can be very smart in the use case mentioned (transportation of file).



[/quote]

markso

We had this problem once.
I thought our back-up was sufficient, but everything had to be transported and we lost data.
That extra step would have helped.
We do this now as a default.