InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 22:54:32 GMT20http://www.sqlservercentral.com/Forums/Topic793042-357-1.aspx[quote][b]PhilM99 (9/24/2009)[/b][hr]Thanks to Silverfox and ALZDBA for the quick replies!OK, if I do this am I on the right track?:1) Manually: Set recovery model to Full Do a log backup using the DBCC command with INIT to get the log file contents to a reasonable size Do a Shrinfile of the log using the DBCC command to get the file size back down2) Set up scheduled jobs steps toBackup the database fully in the middle of the night i.e. BackupLOG with NOINIT, BACKUP Database with INIT (what does that mean, I'll look itup)Schedule an Incremental Backup of the log, say, hourly during daylighthours (that's probably NOINIT, too)at some point schedule a log backup with INIT (I don't get that part)ALZDBA, I don't understand why you have the complex arrangement of jobs modifying the schedule of other jobs. What is it that you are achieving by doing that?Thanks[/quote]do a log backup using the backup log command, and apart from that you should be set to go.Thu, 24 Sep 2009 06:41:38 GMTSilverfoxhttp://www.sqlservercentral.com/Forums/Topic793042-357-1.aspx[quote][b]PhilM99 (9/24/2009)[/b][hr]Thanks to Silverfox and ALZDBA for the quick replies!OK, if I do this am I on the right track?:1) Manually: Set recovery model to Full Do a log backup using the DBCC command with INIT to get the log file contents to a reasonable size Do a Shrinfile of the log using the DBCC command to get the file size back down2) Set up scheduled jobs steps toBackup the database fully in the middle of the night i.e. BackupLOG with NOINIT, BACKUP Database with INIT (what does that mean, I'll look itup)Schedule an Incremental Backup of the log, say, hourly during daylighthours (that's probably NOINIT, too)at some point schedule a log backup with INIT (I don't get that part)ALZDBA, I don't understand why you have the complex arrangement of jobs modifying the schedule of other jobs. What is it that you are achieving by doing that?Thanks[/quote]You will understand that once you get to your statement [quote](what does that mean, I'll look itup)[/quote]The INIT parameter means it will overwrite the given backup file's content.The NOINIT parameter means it will NOT overwrite the file's content.I've built in this little piece of rocket science :hehe: with the construct of these 3 jobs because I don't want to have two backup jobs running at the same time and because I aim for incremental log-bak files having a single file spanning between two full backup sequences.The separate Log _Init job is in place because the last step of the Full bak job has this xcopy step towards the safe zone.By default, I alter the schedule of the log_init job the current time + 30 minutes.Since the log_init will overwrite the log-bak file, size of that file will be small.After that, the incremental log backup job is activated, and the circle is complete.Thu, 24 Sep 2009 06:38:19 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic793042-357-1.aspxThanks to Silverfox and ALZDBA for the quick replies!OK, if I do this am I on the right track?:1) Manually: Set recovery model to Full Do a log backup using the DBCC command with INIT to get the log file contents to a reasonable size Do a Shrinfile of the log using the DBCC command to get the file size back down2) Set up scheduled jobs steps toBackup the database fully in the middle of the night i.e. BackupLOG with NOINIT, BACKUP Database with INIT (what does that mean, I'll look itup)Schedule an Incremental Backup of the log, say, hourly during daylighthours (that's probably NOINIT, too)at some point schedule a log backup with INIT (I don't get that part)ALZDBA, I don't understand why you have the complex arrangement of jobs modifying the schedule of other jobs. What is it that you are achieving by doing that?ThanksThu, 24 Sep 2009 06:06:58 GMTPhilM99http://www.sqlservercentral.com/Forums/Topic793042-357-1.aspx[quote]...Should I set the recovery model to FULL?[/quote]If you want to be able to perform point in time restores, you should set it to full recovery mode.[quote]Should I continue to do a full backup every night?[/quote]That depends on its size and modification load.[quote]How do I do a differential backup hourly?[/quote]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.[quote]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?[/quote]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 backupThe way it works:3 jobs:A) [b]dbnameFULL [/b]following steps for each db: 1) BACKUP LOG [Db] TO [DbLog] WITH [b]NOINIT [/b], NOUNLOAD , NAME = 'Db_Log', SKIP , STATS = 10, DESCRIPTION = 'Full Backup' , NOFORMAT 2) BACKUP DATABASE [Db] TO [DbFULL] WITH [b]INIT [/b], NOUNLOAD , NAME = 'Db_Log', SKIP , STATS = 10, DESCRIPTION = 'Log Backup' , NOFORMAT B) [b]dbnameLog_Incremental [/b]following step for each db: 1) BACKUP LOG [Db] TO [DbLog] WITH [b]NOINIT [/b], NOUNLOAD , NAME = 'Db_Log', SKIP , STATS = 10, DESCRIPTION = 'Log Backup' , NOFORMAT C) [b]dbnameLog_Init [/b]following step for each db: 1) BACKUP LOG [Db] TO [DbLog] WITH [b]INIT [/b], 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 [b]dbnameFULL [/b] 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 [b]next run-time[/b] for the [b]dbnameLog_Init job[/b]as last step , it copies all bak.files to its own safe zone.- Job [b]dbnameLog_Init [/b] has a "run only once" schedule that is being modified by dbnameFull job. Job [b]dbnameLog_Init [/b] makes LOG backups (using the with INI parameter) Before it copies the bak files to its own safe zone, it [b]enables [/b](and renames back to original) the [b]BU_PROC_Disabled_dbnameLog_Incremental [/b] job.-edited-We only [b]shrink [/b]files if we [b]run out of space[/b] or after we planned a [b]huge data cleanup[/b] (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)Thu, 24 Sep 2009 04:00:27 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic793042-357-1.aspxok, lets see if I can cut through some of the issues here.a (.LDF) log file, will always grow, when you backup the log, you are removing all the transactions that have been flushed to disk. but it will not affect the log file physical size. it just increases the free space within the log file. the truncate option for the backup log chops off the free space at the end of the transaction log.To physically resize the log file, if it is too big, is to do a backup log, then use dbcc shrinkfile, and shrink the log file, this can cause fragmentation which you can sort later on, by rebuilding the indexes.Lets base this on an database called adventureworksTo shrink the logUSE [AdventureWorks]GODBCC SHRINKFILE (N'AdventureWorks_Log' , 0, TRUNCATEONLY)GOto do a differential backupBACKUP DATABASE [AdventureWorks] TO DISK = N'c:\AdventureWorks.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'AdventureWorks-Differential Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10GOto do a log backupBACKUP LOG [AdventureWorks] TO DISK = N'c:\AdventureWorks.bak' WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10GOThese are the default options.To fix your current situation, I would suggest doing a log backup, followed by a dbcc shrinkfile. that should bring the size of the log file down.You need to change the recovery model to full, otherwise you cannot do log backups. depending on the number of transaction per day, you could schedule your backup log every hour perhaps.Another thing to remember, in your maintenance plan you can also schedule log backups and shrinking files if needed.If you need clarification on any of this, let me know.Thu, 24 Sep 2009 03:20:54 GMTSilverfoxhttp://www.sqlservercentral.com/Forums/Topic793042-357-1.aspxSetup:SS 2008Small Online Database (127Mb)A few hundred web transactions per day modify the database.This seems so complicated I have been reduced to detaching the db every day (mostly) and making a zip copy of it for backup and then reattaching it. So far so good except the log file has grown to 377Mb.I have recovery set to SIMPLE.I managed to cook up a maintenance plan that runs every night at 3am that does two things...1. Does a FULL backup of the database2. Backs up the transaction log.This seems to work in that it gives me two files every night.(a .bak file and a .trn file).But the log keeps on growing and despite studying this endlessly including on this site I'm lost in the terminiology between truncate and shrink and simple and full. I have even read the treatises written by various gurus.Sorry, but I'm not a DBA nor do I plan to be one.I need to know how to get a daily backup of the database, how to run a differential backup maybe every hour during the day, and how to get the log to stop growing... because once I've done my backup the log file size should reset itself or something.I like to use the SSMS for the setup because the DBCC commands are kinda Greek to me (no disrespect meant to people from Greece).I need to know what to do.Should I set the recovery model to FULL?Should I continue to do a full backup every night?How do I do a differential backup hourly?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?ThanksWed, 23 Sep 2009 21:13:21 GMTPhilM99