We don't have a DBA and I have to do backups. We have only one database that is 3gb (the others are less than 100mb). The 3gb database is write-heavy, and has very important data. I set up daily backups for all the databases but i think that might not be enough.
- What are transactional log backups?
- what is my appropriate backup action plan for the 3gb database?
- how do i do the log backups and for what purpose?
- is a daily full backup enough?
Ok, you really need to head over to the documentation and read it - become a small dba. Expecting people here to copy / paste the EXTREMELY detailed information from BOOKS ONLINE - the SQL Server documentation - is not good behavior. That said, you also areon the wrong site - this is so absolutely NOT a programming issue. There is a sister site (serverfault.com) for server operational issues, which backups belong to.
To get you started:
The transaction log records all changes done to the Database. THis means you take a full backup, then the tx log can be used to roll that forward up to THE LAST TRANSACTION BACKED UP IN THE LOG. MEans that if you take a backup of the tx log afte 16 hours, the server dies, on the new server you restore the daily backup, then process the transaction log and are back to the last commited transaction after 16 hours ;) If you ask me - a business not doing that deserves the damage they get from not doing that.
The transaction log actually is the HUGH advantage you have in something like SQL Server compared to classical file level backups.
All other questions I can not answer. Seriously. These are not "dba level" decisions, they are business decisions. I know companies doing transaction log backups every 5 minutes, shipping them over to a seaprate server (check: Log File Shipping). Reason: Loosing data would be a desaster. Imagine Amazon loosing all sales for half a day. I know other businesses doing daily, sometimes even weekly backups (small shop, intranet site). I know others not relying on backups for desaster issues, but using replication and / or mirroring, with daily full backups and hourly log backups, so that if a server dies, they dont get any downtime. All this is "the same" from a technical point of view - every recommendation depends on the business case, which you say nothing about.
As a normal scenario I would suggest regular full backups (weekly, during off time like sunday), daily differential bakups (a lot smaller than the full one) and then a log backup every x hours (1, 6, 12 - depends on your business case).
The others have given you some basic overview. I'm going to give you some generic answers to your questions, but note that the actual answers will depend on your business needs.
Transaction log backups backup the transaction log. This log records every transaction performed on the database. When a database is in Simple recovery mode, this log is cleared after every Checkpoint on the DB. When the database is in Full mode, these logs continue to fill with transactions until backed up or manually truncated. By backing up the transaction logs, you can use them in combination with "regular" database backups to roll forward to recently performed transactions when recovering the database.
There is no one answer to this question, but in general I tend to do a weekly Full backup, combined with daily Differential backups.
The log backups will be performed by a SQL Maintenance Plan that you create. You perform these backups to clear the transaction log, and record the transactions in case you need to recover them. Please note that if you don't intend to use transaction log backups, you should change your database to Simple recovery mode to avoid issues will full logs or full disks, as the transactions will not auto-clear from the log when in Full recovery mode.
You can perform the log backups with whatever frequency is necessary for your application. For example, if it's unacceptable to ever lose more than 5 minutes of transactions, you'd perform log backups every five minutes, in combination with your regular full and differential database backups.
That depends entirely on your needs. My standard is weekly Full plus daily Differential, and then log backups every 15 to 30 minutes for Full recovery databases, or on another schedule if required. Another decision you'll need to make is how long to retain your backups for purposes of recovery. The primary tradeoffs there are obviously complexity of management and disk space required.
I'm not an expert, but my understanding is that with transaction log backups you can rollback transactions that occur. This is not necessary if you are not wanting to undo things that have happened.
The question "is a daily full backup enough?" depends on the situation. If it's every 24 hours, and you lose the database 1 hour before the backup, then you've lost 23 hours worth of data.
A differential backup backs up all changes since the last full backup, an incremental backup backs up all changes since the last full or differential/incremental/full backup. Depending on your setup, one of these might be useful for you.
You could do something like adding a differential backup in the middle of your full backup, or use incremental backups throughout the day...