Comments
5 comments
-
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. -
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!!!
-
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?
-
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] -
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.
Add comment
Please sign in to leave a comment.
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