regarding automatic backup job

Question

regarding automatic backup job

p.avinash689

SSC-Addicted

Points: 435
More actions
December 1, 2013 at 10:38 pm

#286883

Hi all,
I want to take automatic backup job for one of my database which is having 25gb data.
i want to schedule like below
full backup--- weekly 2 times.
differential backup--- dialy once.
transactional backup--- evry 3 hrs.
could you please any one tell me the procedure to do this.
Thanks & Regards
Avinash

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

One way is to setup a maintenance plan for each of the 3 backup types. If you don't know what a "maintenance plan" is, get into SSMS and press the {f1} key to get into Books Online where you can look up how to do such a thing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

PravB4u Default port Points: 1401 More actions · Answer 2

i would suggest you to make use of maintenance plans, its not only help you to take different kind of backups also you can control backup retention period through maintenance cleanup task.

Praveen D'sa
MCITP - Database Administrator 2008
http://sqlerrors.wordpress.com

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

For the record, I don't actually use Maintenance Plans to do backups. I've developed my own stored procedures that are scheduled as jobs. Maintenance Plans just don't fit my requirements but will do fine for someone in a pinch to get backups rolling.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Kurt W. Zimmerman SSCrazy Eights Points: 8676 More actions · Answer 4

Jeff Moden (12/2/2013)
For the record, I don't actually use Maintenance Plans to do backups. I've developed my own stored procedures that are scheduled as jobs. Maintenance Plans just don't fit my requirements but will do fine for someone in a pinch to get backups rolling.

I use a composite approach here. Most of the time the Maintenance Plan will do what I need. There are several occasions where I need to roll my own. I may opt to using a Maintenance Plan and simply execute the code from an Execute T-SQL Statement Task.

I view this as a matter of managing all maintenance jobs in a single place.

Kurt

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman

Johan Bijnens SSC Guru Points: 135258 More actions · Answer 5

We use scheduled sqlagent jobs to create the wanted types of backups at the desired time intervals.

So we use TSQL steps to actually create the backups.

We also use backup devices, so use the same actual file name every time because:

- or we copy the bak files to safe zone with time indication

- or make incremental backups and copy those to an alternate safe zone.

Just keep in mind to use backup compression as it saves quite a number of space.

( this can be enabled at instance level using sp_configure )

USE master;

EXEC sp_configure ‘backup compression default’, '1';

RECONFIGURE;

or you can provide the compression parameter with your backup statement ( with COMPRESSION )

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

andreas.kreuzberg SSCertifiable Points: 6183 More actions · Answer 6

Hello,

have you looked this page from Ola Hallengren:

http://ola.hallengren.com/

There a ready jobs for backups, full, diff and log. You only had to schedule the jobs.