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 Administration
 changing recovery model equals data loss?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

t0tenkopf
Starting Member

Senegal
2 Posts

Posted - 04/25/2014 :  06:30:27  Show Profile  Reply with Quote
Hi all,

There is something I dont understand:
Lets say I have a database.
MDF file is 400Mb
Log 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

Edited by - t0tenkopf on 04/25/2014 06:51:29

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 04/25/2014 :  08:35:31  Show Profile  Reply with Quote
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.
Go to Top of Page

t0tenkopf
Starting Member

Senegal
2 Posts

Posted - 04/25/2014 :  09:47:22  Show Profile  Reply with Quote
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 loss

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?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 04/25/2014 :  12:45:16  Show Profile  Reply with Quote
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.
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2019 Posts

Posted - 04/28/2014 :  01:32:21  Show Profile  Visit jackv's Homepage  Reply with Quote
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.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

elliswhite
Starting Member

36 Posts

Posted - 05/05/2014 :  07:40:56  Show Profile  Reply with Quote
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
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