SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 full backup and .trn log backup
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

learning_grsql
Posting Yak Master

218 Posts

Posted - 08/20/2013 :  17:34:57  Show Profile  Reply with Quote
I have set up maintenance plan in sql server 2005 for .bak full database backup and .trn only log transaction backup. Both backup is done every day night at 2 am.

I would like to know, when I want to restore the database, will it be fine with only .bak or shall I need both .trn and .bak files to restore?

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 08/20/2013 :  18:46:06  Show Profile  Visit SwePeso's Homepage  Reply with Quote
If you do a FULL backup at 2AM, why do a log backup?
Which recovery mode are you using?



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 08/20/2013 :  20:58:40  Show Profile  Reply with Quote
You will need only the full backup file for restoring.

However, there are at least two reasons you should do the transaction log backup as well.

First, if you don't take a log backup, the transaction log will monotonically grow, and sooner or later you will run out of disk space. The only way to free up space in the log file is to take a log back up. I assume you are in full or bulk-logged recovery model, otherwise you couldn't have taken a log backup.

Second, having log backup allows you to do point in time recovery. You can use the log backup together with the previous full backup to restore the database to any specific point in time between the log backup and the full backup.

I must also note that most people (that I know) take more frequent log backups. It may or may not be of benefit to you depending on how much DML activity goes on in your database.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 08/21/2013 :  02:20:23  Show Profile  Visit SwePeso's Homepage  Reply with Quote
The options for restoring data is dependant on the recovery mode.

Under Simple Recovery Mode, the log file is automatically truncated
See http://technet.microsoft.com/en-us/library/ms191164(v=sql.105).aspx


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Edited by - SwePeso on 08/21/2013 02:25:35
Go to Top of Page

learning_grsql
Posting Yak Master

218 Posts

Posted - 08/25/2013 :  16:33:01  Show Profile  Reply with Quote
quote:
Originally posted by James K

You will need only the full backup file for restoring.

However, there are at least two reasons you should do the transaction log backup as well.

First, if you don't take a log backup, the transaction log will monotonically grow, and sooner or later you will run out of disk space. The only way to free up space in the log file is to take a log back up. I assume you are in full or bulk-logged recovery model, otherwise you couldn't have taken a log backup.

Second, having log backup allows you to do point in time recovery. You can use the log backup together with the previous full backup to restore the database to any specific point in time between the log backup and the full backup.

I must also note that most people (that I know) take more frequent log backups. It may or may not be of benefit to you depending on how much DML activity goes on in your database.



Thanks James K. In fact I did this as per your instruction in one of my previous thread. By doing this, it's not increasing my log file size. However, if possible, I would like to know how log file actually works. Why the file size is not growing if we take backup otherwise it increases tremendously.

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 08/26/2013 :  08:29:51  Show Profile  Reply with Quote
The internals of log files can fill a volume, so hard for me to describe it here. See this page for a brief description of what the transaction log does: http://technet.microsoft.com/en-us/library/ms190925(v=sql.105).aspx

A single transaction log file is structured interally as a collection of virtual log files (VLFs). When SQL Server determines that the data in a given VLF is no longer required, it will "clear" that VLF, which can then be reused. For a VLF to be a candidate for clearing, it has to meet a number of criteria, one of which is that the log file should have been backed up. If you don't back up the log file, it is never cleared, and hence cannot be reused. Which of course means that as you do more and more DML operations, your log file will need more and more space.

The rationale for this behavior has to do with (disaster) recovery. In full recovery mode, you can recover up to your last log backup (or even later if you still have the server and can take a "tail of the log" backup). Full recovery mode is designed for just that purpose - i.e., to minimize data loss. In order for SQL Server to ensure that it can minimize data loss, it insists that you take log backups.

If your business can afford one day's data loss - i.e., in the even of disaster, all you would be required to do is restore last night's backup, then you don't need to be in full recovery mode. You can use simple recovery mode, and you wouldn't need to (and couldn't) take log backups.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 08/26/2013 :  09:59:55  Show Profile  Reply with Quote
You should read the following article; it is one of the best explanations of the transaction log and how to manage it.

http://www.sqlservercentral.com/articles/Administration/64582/
Managing Transaction Logs
By Gail Shaw, 2012/01/03




CODO ERGO SUM
Go to Top of Page

learning_grsql
Posting Yak Master

218 Posts

Posted - 08/28/2013 :  13:44:01  Show Profile  Reply with Quote
Thanks James and Michael.
@James, now I changed back to Simple Mode and scheduled only full backup, no log backup because for us it is fine as long as we could recover previous day backup. Now my concern is whether my log file size will increase due to this or do I need to take log backup also.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 08/28/2013 :  14:32:05  Show Profile  Reply with Quote
You should not need to and cannot take log backups now that you have changed to simple recovery. You will need only full backup to restore the database. But make sure that your backups are good and stored safely. You don't know that a backup is good until you have restored it, so test restoring it to some place.

If you are in simple recovery model, the runaway growth of log file will not happen. If a VLF can be cleared, it will be cleared at each checkpoint (which happens often).

Also, even though your organization might tell you that your SLA is 24 hours, my experience has been that it never hurts to be prepared to do better than what you have been asked to do. So, even though I suggested simple recovery model in an earlier post, you have to be sure that you can live with the lost data. You might consider taking differential backups couple of times a day. That will give you a little bit more protection against data loss.
Go to Top of Page

learning_grsql
Posting Yak Master

218 Posts

Posted - 08/29/2013 :  16:12:46  Show Profile  Reply with Quote
Thank you James :)
Go to Top of Page

colinpaul
Starting Member

1 Posts

Posted - 09/10/2013 :  07:01:31  Show Profile  Reply with Quote
If you want to repair your corrupt SQL database you can try Recoveryfix for sql database recovery software. This tool repairs all your SQL Server tables, user defined functions, triggers, rules, stored procedures, views and indexes from the corrupted or damaged MS SQL Server files. You can try & test the demo version of the software here :- http://www.sqlrecovery.org/download.html
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000