| Author |
Topic |
|
learning_grsql
Posting Yak Master
230 Posts |
 Posted - 2013-08-20 : 17:34:57
|
| 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
30421 Posts |
Posted - 2013-08-20 : 18:46:06
|
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 |
 |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-08-20 : 20:58:40
|
| 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. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-08-21 : 02:20:23
|
The options for restoring data is dependant on the recovery mode.Under Simple Recovery Mode, the log file is automatically truncatedSee http://technet.microsoft.com/en-us/library/ms191164(v=sql.105).aspx
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|
learning_grsql
Posting Yak Master
230 Posts |
Posted - 2013-08-25 : 16:33:01
|
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. |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-08-26 : 08:29:51
|
| 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. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
|
|
learning_grsql
Posting Yak Master
230 Posts |
Posted - 2013-08-28 : 13:44:01
|
| 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. |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-08-28 : 14:32:05
|
| 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. |
|
|
|
learning_grsql
Posting Yak Master
230 Posts |
Posted - 2013-08-29 : 16:12:46
|
| Thank you James :) |
|
|
|
colinpaul
Starting Member
1 Post |
Posted - 2013-09-10 : 07:01:31
|
| 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 |
|
|
|
elliswhite
Starting Member
36 Posts |
Posted - 2014-05-07 : 02:43:37
|
| u should take backup of the both file .bak and log file at particular instance of time in alternative days. Yes to restore backup database u must need to take backup of the log file as well. |
|
|
|
|
full backup and .trn log backup