Backing up SQL server

[thecivicman]

If I do complete and differential backups, do i need transaction log backups?
Thanx
Dave

 

[SQLSister]

Usually yes. If you do not back up the transaction log, it will grow to take up your whole hard drive. The only reason to not do transaction log backups is if the database is set to simple recovery mode. But if it is a transactional database, most of teh time people want to be able to recover as much data as possible. GEnerally you want to back up the transaction log as often as need be to prevent the loss of data that is acceptable in your company. We run our transaction log backups every hour for some dbs and every 15 minutes for others. The more often you do transction log backups, the less large your transaction log will need to be and the less data you lose if there is a failure.

Questions about posting. See faq183-874

 

[thecivicman]

Thank you. That helps me out alot.
Dave

 

[SQLBill]

But that's not all. Lets say you don't do TL backups, but you keep the size down - no biggie. You last did a DIFF backup at 1200AM. At 2 am, a user deletes a table by mistake. In between there were lots of transactions. When you restore, you can only get back up to and including the data on the last DIFF - 12AM. So you lose 2 hours of work.

Now, you are doing TL backups. The user deletes the table. You immediately put the database in SINGLE_USER mode. Do a TL backup. Now you restore the latest FULL backup, the latest DIFF backup, and any TL backups since the DIFF. BUT on the LAST TL restore, you use:
WITH STOPAT '2004-12-10 01:55:00'
now you have ALL the data back up to just before the user deleted the table.

If there aren't many transactions being done, or they are easy to redo, then TL backups/restores may not be important.

What does your situation call for? How much data can you afford to lose?

-SQLBill

Posting advice: FAQ481-4875

 

[thecivicman]

I have one database server that is getting modified quiet often. So I figure I'll do a complete backup once a week, a diff backup every day, and a TL backup every 2 hours or so. If you have any suggestions or comments they would be very much appreciated.
Thanx guys, now I won't look like such a noob at work

Dave