Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
t0tenkopf
Starting Member
2 Posts |
 Posted - 2014-04-25 : 06:30:27
|
| Hi all,There is something I dont understand:Lets say I have a database.MDF file is 400MbLog is 10 Gb big - I was lazy and didnt create job to backup log on regular basis...Now I first do a full backup before I do anything stupid. Then I do backup of log to force a checkpoint.After that I switch to simple recovery model, shrink log file and change back to full recovery model.Does that means that I will get some data loss?edit: also when I did full backup I got a file 400mb big, shouldnt log file be also backuped with full backup?ty |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-04-25 : 08:35:31
|
| Log file records information about the database changes (which then can be used to recover the database to a point in time, or to roll back changes if required etc.). When you do a full backup, it does not copy all the information in the log file to the backup. It does copy a little bit - enough to enable it to do the so called redo and undo in order to preserve the data integrity. So if you have a full backup you can restore the database successfully. You have not lost any data.When you change the recovery model to simple, you are breaking the log chain, so you won't be able to do a recovery using a backup that was taken before the recovery model was changed along with the log files to recover to a point in time after you reverted back to full recovery model. (I know, that sounded confusing).At this point what I would suggest is to take another full backup, then schedule regular full backups and frequent log backups. Scheduling backups is easy - Right click on Maintenance plans under Management node in Object Explorer in SSMS, select new maintenance plan and follow through the wizard. |
 |
|
|
t0tenkopf
Starting Member
2 Posts |
Posted - 2014-04-25 : 09:47:22
|
| Can you please explain what type of data is in log file if the actual data is in fact in mdf file?It seems pretty crazy to have 400mb of data and 10Gb of "transaction data"? So far I thought that log contains all the transactions with data and that truncating the log without checkpoint will result in data lossIf I take DB from example above and without doing any backups, I just switch to simple recovery mode, shrink the log, will I experience data loss then? |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-04-25 : 12:45:16
|
quote:
Can you please explain what type of data is in log file if the actual data is in fact in mdf file?
Take a look at this article. It explains transaction log better than I can. There are a number of other resources that you can find via google that will go into more depth and detail, but this article is short and to the point. http://www.sqlservercentral.com/articles/Administration/64582/quote:
It seems pretty crazy to have 400mb of data and 10Gb of "transaction data"? So far I thought that log contains all the transactions with data and that truncating the log without checkpoint will result in data loss
It is not inconceivable at all that a database that is 400 MB got to that point by transactions that end up being 10 Gigs or more. The history of how the data in the database got to the state that it is currently at is probably of no interest. That is why the all that data in the log file is not something that you are interested in. However, to shrink that down to manageable size, you should go through the process described in that article.quote:
If I take DB from example above and without doing any backups, I just switch to simple recovery mode, shrink the log, will I experience data loss then?
You will not lose any data. But, it is very IMPORTANT that you take a backup before you do any type of significant changes to the database, and it is very important that you schedule regular backups of the database. The SQL landscape is littered with people who ignored this simple advice and have suffered because of that. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2014-04-28 : 01:32:21
|
| t0tenkopf - you say the transaction log file is 10 GB - but there may be unused space?. For example, if there was a long running transaction , the trans log could expand before the transaction is commited. Keep in mind , that every time you take a FULL BACKUP without using the COPY parameter - the log chain is broken. Therefore, get into the habit of taking ad-hoc backups (that are not part of the RESTORE chain ) using COPY http://www.sqlserver-dba.com/2010/10/copy-only-backup-in-sql-server-2005-and-not-break-the-backup-chain.htmlJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
elliswhite
Starting Member
36 Posts |
Posted - 2014-05-05 : 07:40:56
|
| yes u should take backup of the log file with database backup which will help u to restore backup database further. If u don't know how to take backup of the database manually then visit blog: www.sqlrecoverysoftware.net/blog/backup-and-restore-in-sql-server.html |
|
|
|
|
|
|
|
|
changing recovery model equals data loss?