Options

Running a schdule backup automatically

rafiqahmedsrafiqahmeds Posts: 3
edited July 26, 2014 4:40PM in SQL Backup Previous Versions
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
· Share on Twitter

Comments

  • Options
    Chris.AllenChris.Allen Posts: 579
    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.
    · Share on Twitter
  • Options
    rafiqahmedsrafiqahmeds Posts: 3
    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
    · Share on Twitter
  • Options
    cunhaefccunhaefc Posts: 1
    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?
    Gold IRA Rollover
    · Share on Twitter
  • Options
    Chris.AllenChris.Allen Posts: 579
    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]
    · Share on Twitter
  • Options
    marksomarkso Posts: 1
    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.
    · Share on Twitter
Sign In or Register to comment.