• ...

    Should I set the recovery model to FULL?

    If you want to be able to perform point in time restores, you should set it to full recovery mode.

    Should I continue to do a full backup every night?

    That depends on its size and modification load.

    How do I do a differential backup hourly?

    I wouldn't do a defferential backup, because a differential backup will need to read the whole datapages of your dabase !

    I would prefer making incremental LOG backups.

    Is there a way to back up the log and do I need to do that, and how to I get it to reset itself so that it is smaller again?

    Check out "Backup log" in books online.

    The scenario I install by default for a database is:

    1) every x hours an incremental logbackup. (x depends on the load and the SLA of the database)

    2) every x days a full backup

    The way it works:

    3 jobs:

    A) dbnameFULL following steps for each db:

    1) BACKUP LOG [Db] TO [DbLog] WITH NOINIT , NOUNLOAD , NAME = 'Db_Log', SKIP , STATS = 10, DESCRIPTION = 'Full Backup' , NOFORMAT

    2) BACKUP DATABASE [Db] TO [DbFULL] WITH INIT , NOUNLOAD , NAME = 'Db_Log', SKIP , STATS = 10, DESCRIPTION = 'Log Backup' , NOFORMAT

    B) dbnameLog_Incremental following step for each db:

    1) BACKUP LOG [Db] TO [DbLog] WITH NOINIT , NOUNLOAD , NAME = 'Db_Log', SKIP , STATS = 10, DESCRIPTION = 'Log Backup' , NOFORMAT

    C) dbnameLog_Init following step for each db:

    1) BACKUP LOG [Db] TO [DbLog] WITH INIT , NOUNLOAD , NAME = 'Db_Log', SKIP , STATS = 10, DESCRIPTION = 'Log Backup' , NOFORMAT

    Every job copies the .Bak files to its own safe zone.

    How it works:

    - Job dbnameFULL starts with disabling dbnameLog_Incremental renaming it to "BU_PROC_Disabled_dbnameLog_Incremental". (The rename is done just to keep track the backup sequence is on going)

    Right before its last step, it sets the next run-time for the dbnameLog_Init job

    as last step , it copies all bak.files to its own safe zone.

    - Job dbnameLog_Init has a "run only once" schedule that is being modified by dbnameFull job.

    Job dbnameLog_Init makes LOG backups (using the with INI parameter)

    Before it copies the bak files to its own safe zone, it enables (and renames back to original) the BU_PROC_Disabled_dbnameLog_Incremental job.

    -edited-

    We only shrink files if we run out of space or after we planned a huge data cleanup (and don't expect teh data to grow within a reasonable periode of time !

    Because of the frequent log backups, the log file(s) shouldn't grow that much, unless someone performes an exceptional huge transaction)

    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