Running a schdule backup automatically

Compresses, encrypts, secures and monitors SQL Server backups.

Running a schdule backup automatically

Postby rafiqahmeds » Sun May 11, 2014 1:50 pm

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
rafiqahmeds
 
Posts: 2
Joined: Sun May 11, 2014 1:46 pm

Postby Chris.Allen » Mon May 12, 2014 2:32 pm

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.
Chris.Allen
 
Posts: 579
Joined: Thu Mar 12, 2009 4:17 pm

Postby rafiqahmeds » Sun May 18, 2014 7:53 am

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!!! :o
rafiqahmeds
 
Posts: 2
Joined: Sun May 11, 2014 1:46 pm

Postby cunhaefc » Sun May 25, 2014 1:08 am

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?
cunhaefc
 
Posts: 1
Joined: Sun May 25, 2014 1:05 am

Postby Chris.Allen » Thu Jun 12, 2014 2:10 pm

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]
Chris.Allen
 
Posts: 579
Joined: Thu Mar 12, 2009 4:17 pm

Postby markso » Sat Jul 26, 2014 9:40 pm

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.
markso
 
Posts: 1
Joined: Sat Jul 26, 2014 9:28 pm


Return to SQL Backup 7

Who is online

Users browsing this forum: No registered users and 0 guests