Solved

how to setup log file or intermediate backup

Posted on 2012-04-04
6
507 Views
Last Modified: 2012-06-21
I back up my SQL Server 2005 databases daily with a SQL Server Agent job. Now I'd like to do some kind of intermediate backup, or backups, during the business day which will get saved on a remote computer every 20 minutes so we can always be sure to never lose anything other than the last 20 minutes.

I need advice. Should I set the databases to Full or Simple? Should I create transactions logs or do differential backups? I'm not entirely sure how transaction logs work. Do they journalize each transaction? What is this business about having to clean up log space, and "expiration"?
0
Comment
Question by:jmarkfoley
6 Comments
 
LVL 3

Accepted Solution

by:
sjurus earned 300 total points
ID: 37809571
Hi,
 if you want to do transaction log backup you must set the databases to Full recovery model because in this mode you have full transaction logging. What a choice whether to do transaction log backup or Differential backup is related more to business requirements.

Personally, I very often use such a model:

full backup on Sunday
Differential daily backup at night
and during the day the transaction log backup

You can also adopt a different model: database in Simple Recovery Model

full backup on Sunday
before starting work Differential backup (if performed at night are some additional operations on the database)
Differential backup after work
Only in this model, you have to realize to a business that in case of failure the data will be recovered from the first day of work

Of course, start and end and how often the backup log file depends on the specifics of the system and business requirements

This article will explain how work the transaction log:
http://www.sqlservercentral.com/articles/Design+and+Theory/63350/

So really the choice model disaster recovery actively addressed mainly depends on the system, such a database (transaction, data warehouse, etc) on the number of operations during the day, the amount of data we can lose, etc.
0
 
LVL 32

Assisted Solution

by:ewangoya
ewangoya earned 100 total points
ID: 37809713
Depending on the size of your database, daily full backup may not be efficient in terms of resources (space, CPU)
You can use mirroring or transaction log shipping
Recovery model should be Full

Set a weekly full backup and a daily differential backup.
You can backup the transaction logs every few minutes as you wish
Regular transaction log backups also ensure that your log file does not grow out of hand (I suspect that is what you mean by cleanup)
0
 
LVL 1

Author Comment

by:jmarkfoley
ID: 37812500
These database aren't that big, so I am currently doing daily full backups. I guess I'd like to do transactions logs every <n> minutes as ewangoya suggests.

sjurus' link was very informative in telling me about the theory behind logfile, but it didn't really say how to set them up. So, first step, make the database Full Recovery Mode. Then what? How do I specify where the log files go? How often they are created? I didn't really understand the bit about logfile size. Do I set this? is this for each log file? is there more than one? I'm new at this.

Thanks
0
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

 
LVL 1

Author Comment

by:jmarkfoley
ID: 37813818
more info

I created a maintence plan task to backup the log files. I have a bit of a problem in that it doesn't seem to run automatically even though I believe I've set it to run every 20 minutes. Can anyone advise me what I could be doing wrong?

Anyway, I did get the T-SQL and ran it by hand. For example:

BACKUP LOG [OSH2011] TO  DISK = N'\\OHPRSstorage\public\SYSADMIN\SQLserverTransactionLogs\OSH2011\OSH2011_backup_201204051533.trn' WITH NOFORMAT, NOINIT,  NAME = N'OSH2011_backup_20120405153333', SKIP, REWIND, NOUNLOAD,  STATS = 10

I was surprised to see that this generated a .trn file of 2,348,673,536 bytes. A fullbackup of this database is 429MB. Why do I have a 2GB transaction file? If that's the case, I'd be ahead to do differential backups.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 50 total points
ID: 37814854
Why do I have a 2GB transaction file? If that's the case, I'd be ahead to do differential backups.
How big was the Transaction Log file?
What was/is the Recovery Model?
When was the last time you did a Transaction Log backup?
0
 
LVL 8

Assisted Solution

by:avirups
avirups earned 50 total points
ID: 37930904
If you want to avoid the maintenance involving the transaction log file and since your database is not very large yet, why don't you keep the recovery model to SIMPLE and schedule a Differential Backup every 20 minutes along with your Full Daily Backup.

That should do the trick. Now in this case, whenever you want to restore you only need to restore the last full backup with NORECOVERY option and then restore the last Differential Backup with RECOVERY option.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Learn to move / copy / export exchange contacts to iPhone without using any software. Also see the issues in configuration of exchange with iPhone to migrate contacts.
This article explains how to install and use the NTBackup utility that comes with Windows Server.
In this video we show how to create a User Mailbox in Exchange 2013. We show this process by using the Exchange Admin Center. Log into Exchange Admin Center.: First we need to log into the Exchange Admin Center. Navigate to the Recipients >> Mailb…
The basic steps you have just learned will be implemented in this video. The basic steps are shown to configure an Exchange DAG in a live working Exchange Server Environment and manage the same (Exchange Server 2010 Software is used in a Windows Ser…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question