March 3, 2008 at 6:39 am
Hi,
I have recently installed a new sql server 2000 ent edition.
I am having issues trying to understand how to do the backups.
this is what i do at the moment.
at 11:00 Backup Transaction log at 11:00 with Overwrite command below.
Backup LOG [Changepoint] TO DISK = N'F:\Backups\TRL\TRNLOG1100.bak' WITH INIT
at 15:00 Backup Transaction log at 15:00 with overwrite command below
Backup LOG [Changepoint] TO DISK = N'F:\Backups\TRL\TRNLOG1500.bak' WITH INIT
then Full Database Backup at 19:00 with Overwrite
BACKUP DATABASE [Changepoint] TO DISK = N'F:\Backups\DB\FullDB.bak' WITH INIT
Database recovery model is set to FULL
I have been checking the log file sizes and it got to 29gb in size how do i reduce this and when is the best time to do this in my backup schedule above.
I was hoping that there is some type of command i can add to my log backup ie. Backup LOG [Changepoint] TO DISK = N'F:\Backups\TRL\TRNLOG1500.bak' WITH INIT NO_LOG
what does NO_LOG do ?
what does Truncate_only do ?
what is the best thing to do with this, I have been reading BOL and can't figure this out.
Please help me and Thanks in Advance.
Best Regards,
Frank
March 3, 2008 at 6:57 am
NO_LOG and TRUNCATE_ONLY are the same, and you do not want to use either of these (unless you do not care about recovery).
These options will truncate your log, and in this way break the log chain and are similar in functionality to switching to SIMPLE recovery mode.
You could reduce the live log file with DBCC SHRINKFILE (but if you reduce it too much, it can grow again, and the physical file for the log file will become fragmented)
Have a look at the reasons why the log has got so big. Is it a runaway transaction? Is it an index rebuild? If you reduce the live log file size, does it grow back to 29GB? If the log file size is critical for you, you may as well have a
look at switching to BULK recovery mode just before index rebuilds and other operations that can be minimally logged.
Regards,
Andras
March 3, 2008 at 8:41 am
The best way to decide a backup strategy is to determine what the company accepts as an acceptable loss. In most production OLTP systems, the answer is NONE!!!! which means, you should perform Log/Diff/And full backups to ensure point in time recovery.
The next issue to decide is how fast must you recover, that will determine how often you back up the database(s). There is no cookie cutter scenerio, you must do your homework to bring about what is best for the company. Remember, the more backups, the more files that needs to be restored, however, with less backups, the restore may prove to be longer because the files are larger.
My first stop would be the IT director, if available and if not, the next on the management chain. You must understand their policies in regards to data and plan accordingly. Also, remember, when the backup can't be restored you have not done a backup. So, make sure you test your disaster plans, prior to the disaster happening. One thing I have always asked is for a "sandbox" environment to play in and test just those things. Doesn't have to be fancy, or large, just a place where you can go off and "Play" disaster recovery.
Another point to make, is as soon as you create a new database, "BACK IT UP". One thing I see with new DBAs is that they do not think a newly created blank database should be backed up. WRONG. Remember to do point in time recovery, you must have log backups, and to have log backups you must have an existing full backup. Hence, back up the newly created database, especially in a production system. (enough venting).
Hopefully this information has started your DBA juices flowing and you have an idea as to what you want to do. Remember, research, what is best for your company and no two situations are exactly alike.
Marvin Dillard
Senior Consultant
Claraview Inc
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply