...
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