Solved

which files to backup in sql 2008

Posted on 2011-09-05
36
356 Views
Last Modified: 2012-05-12
Hi,
in our accounting software which runs sql 2008, we were told to backup the 2 files in data directory of sql.  One is called 001.mdf which is company data itself and also 001_log.ldf which is the log file relating to that company.  The 001.mdf is 62gig but the 001_log.ldf is 260gig! We run full backup of bak files that are backed up from sql itself but wanted to know how to back up log file within sql studio manager and if it is necessary since we are running backup of 001.mdf already.  Any ideas on how to streamline process since log file seems so big to backup.  Is log file same as transaction file which has .trn exetension?  thanks.
0
Comment
Question by:dankyle67
  • 10
  • 10
  • 8
  • +1
36 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36486222
If you run a full backup within SQL Server, it should back up the transaction log also. If it is a maintenance window and you have the SQL Server service stopped, you can also copy the MDF and LDF files to a backup location also. However, if you take a full backup, you should get a BAK file that can be used to restore both files.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36486227
0
 
LVL 2

Accepted Solution

by:
awarren85 earned 200 total points
ID: 36486234
Here's the way I always looked at this:

.mdf (Data File) - This is the true MS SQL Database, this contains your tables and data.
.ldf (Log File) - This is a log file - containing records of what data was written and deleted from the database and when.  Allows for a point-in-time recovery of the database.
.trn (Backup Log File) - Same as above, but segregrated from the log file in order to keep the log file from growing too large.

To answer your question, all you really need is the .mdf file, as it contains your data.  Your log file, while important, isn't necessary unless you have database issues or need point-in-time recovery for your database.  

If you don't care about point-in-time recovery, you can change the SQL Database Recovery Model to 'SIMPLE' by right-clicking the database in SQL Server Management Studio, going to Properties, go to Options, and change Recovery Model to SIMPLE.

Then, to delete the log file, detach the database (right-click the Database, Detach), then delete the log file (the .ldf file from above).  You can move it to another directory if you wish instead of deleting.  Then, right-click the databases folder on the left-side and click 'Attach'.  Navigate to the .mdf file, it will complain about the Log File missing, but hit OK.  It should auto-create a new log file.

In our environment we tend to run the databases in 'SIMPLE' mode unless otherwise notated by the vendor.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36486304
By the way, you should run normal database backup each night and then setup periodic transaction log backups. Mine are set to hourly, but you should set this per your needs. As stated, you can also consider going to SIMPLE mode if recovery is not a concern, but in most cases people want the FULL RECOVERY just not the big transaction logs. :) To manage the size, you need to frequently backup the logs.

Here is a nice Article on the topic.
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_657-Managing-the-Transaction-Log-for-the-Accidental-DBA.html

Not sure I would recommend getting rid of the LDF in that fashion. Make sure you have a full backup of your database, then doing frequent log backups should allow you to safely truncate the log.
0
 
LVL 2

Expert Comment

by:awarren85
ID: 36486306
I Agree @mwvisa1, that's the way it's recommended to do, however the above procedure works in a pinch.
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 36486901
What ever you do, do not backup the base database files (mdf & ldf). If the sql server services are online at the time you could very well end up with a corrupt database if you needed to restore. Also do not detatch the database and delete the log file.

You will not be able to attatch it again without bringing the database up in a number of methods. It doesnt matter what recovery model the database is in, it still needs its log.

Please follow mwvisa1's advice of full backups and if needed transaction log backups. If you only need a daily backup without transactional backups (imagine this as the changes since the last backup) then you can change the recovery model to simple. This just means that once logged transaction have completed that the log would be cleared.

0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 36486910
Oh sorry missed a part of your question. To backup from Management Studio do the following...

BACKUP LOG [DBNAME] TO DISK='BACKUP LOCATION ie (C:\File.Bak)' WITH STATS=1

Open in new window


Just replace DBNAME with the database name and the BACKUP LOCATION section with the filesystem location where you want to back it up.
0
 

Author Comment

by:dankyle67
ID: 36488659
thanks all good information.  Just to confirm, i changed the backup mode from full recovery to simple.  After doing this i ran a scheduled job by right clicking database and selected backup and scheduled it to run a few minutes after i set it up to test.  Original mdf file was around 62gig as previously mentioned and ldf was 260g.  After running the backup, tjhe bak file that was there now showed up as 118g.  Are you saying that as long as i use simple recovery mode and run full backups every nite, i wont need to back up the transaction log?  This would make the space issue a lot easier for us as well as only having to worry about a single file to restore which would be comany001.bak.  Out of curiousity, if i run shadow copies of the entire drive that sql data is on, if for some reason i didnt have a good restore from the bak file, would i be able to restore the mdf file alone from the shadow copy or would i also need the ldf file associated with it?  also, since you mentioned that would no longer need log file when using simple recovery mode, is there a way to shrink the existing ldf so we could also get more space back on the drive it sits on currently?
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 36488746
No you will not need to backup the transaction log while the database is in simple mode.

Shadow copies can cause SQL to slow down. Just use the native SQL BACKUP commands, bear in mind that the transaction log holds records which have been commited from the database perspective and may not have been applied to the mdf file. If this were lost you could loose data that the database thought was committed. This is why if you look at the sql event log you see rolling forward or rolling back transactions on startup.

You would need to shrink the transaction log too as you will have a massive log file which is empty. Use DBCC SHRINKFILE for this.
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 36488763
As a note no matter what recorvery mode the database is in it still needs its transaction log. This is how it manages to rollback transactions and maintain integrity in the database.
0
 

Author Comment

by:dankyle67
ID: 36489417
So you are saying that even in simple mode the transaction log is still being used but when you backup and restore, as long as you select full backup in the backup job, then you only need one file to perform restore which would be the 001.mdf.  Can i also peform the transaction log file shrink by using sql studio manager since i am not that familiar with using sql commands.  
0
 
LVL 2

Expert Comment

by:awarren85
ID: 36489473
In simple mode, the transaction log file is used for uncommitted commands and as temp space when commands are running.  Completed transactions are not stored there (unlike in regular mode), which helps greatly on the size.

If you do the detach/reattach, in the first comment, you can get rid of the huge transaction log file (which has hundreds of GB of commands already ran against the database) and start fresh.

To try to shrink, right-click the database, click Tasks, then Shrink then Files.  Change file type to 'Log'.  
0
 
LVL 16

Assisted Solution

by:EvilPostIt
EvilPostIt earned 100 total points
ID: 36489493
Correct.

Have a read through the following, im sure you will find it helpful.

http://msdn.microsoft.com/en-us/library/ms175477.aspx

Yes you can shrink by right clicking on the database Tasks -> Shrink -> Files.

Select the log file and shrink.
0
 

Author Comment

by:dankyle67
ID: 36489534
Alternatively, since the log file which is huge at this point is populated with committed commands that have already been written to mdf file, then would it also be ok just to move the ldf file to another disk location which has lots more space?  
0
 
LVL 2

Expert Comment

by:awarren85
ID: 36489552
That's what I would do, detach the database, move the LDF off, then re-attach but leave out the LDF file.  It will autocreate a new one.  
0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 200 total points
ID: 36489610
Truncate the LDF using DBCC SHRINKFILE or via GUI, then move the file to new location.
0
 

Author Comment

by:dankyle67
ID: 36489625
Perfect, one last question.  In general, if i have the mdf file which got corrupted on tuesday lets say and i wanted to restore monday nites backup but didnt have the bak file of the mdf within the sql management studio backups but had a good mdf from another backup, if i wanted to replace the tuesday corrupted mdf withe the monday good mdf then would i detach database in sql management studio then rename and then copy good mdf into data directory and then attaach to current live database?  Just wanted to have this as an extra layer of backup.  thanks
0
 
LVL 2

Expert Comment

by:awarren85
ID: 36489633
Sorry, to be more clear:

Right-click the database, click Tasks, Detach.  A window will appear.  Ensure that all users are out of the database and that you have no SQL windows open using that database.  Hit OK to detach.  It will throw errors if the database is in use.

Then, once detached, go to that directory on the server and either delete or move off the LDF file.

Then, right-click on Databases, hit Attach.  On the top part, hit Add.  Navigate and find the MDF file and hit OK.  On the bottom, it will say 'Not Found' beside the Log File LDF.  Highlight that row and hit Remove.  Hit OK to complete Attach.

The LDF will auto-create.

Also, mwvisa1 is correct, may want to truncate first, then move the log file.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 2

Expert Comment

by:awarren85
ID: 36489642
Sorry, we keep stepping on each other.  Yes, to restore the MDF you'd follow the procedure above, detach your old (corrupted) MDF and then re-attach your good, backup MDF.
0
 

Author Comment

by:dankyle67
ID: 36489773
Thanks again for the clarification and step by step instructions.  Good stuff
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 36493883
I would just like to re-iterate at this point that you should NOT delete the log file. I showed this post to a Microsoft Field Engineer and he could not believe what had been written here as this can cause many issues with the database! Also that a backup solution of backing up the base files was also accepted as an answer!
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36495157
I agree. I tried to back you up in that point and was hoping my iterative comment, stating to truncate file and then move which T-SQL can manage, being accepted was a good sign that our point was heard. I would have liked to see the one regarding simply doing full backups accompanied by more frequent trans log backups accepted versus moving to simple, but that is just me...
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 36495550
It all depends upon the SLA of the app to be honest. If the business doesnt mind loosing a days worth or transactions then simple is probably the best option. Otherwise then obviously transaction log backups are the right way to go.

But then again if the business isnt to bothered about data integrity or actually trusting the data in the database then its obviously best to just detatch the database and delete the log ;) It may work but there is a reason that management studio complains about there being a log missing.

To be honest my previous post was more about the fact that the accepted solution is quite frankly the wrong one and wanted to let anyone know that if they look it up in the future. People seem to forget that these post are also looked at as a technical resource to resolve problems, this sort of thing tends to give people misguided advice.
0
 

Author Comment

by:dankyle67
ID: 36495841
I see your point and agree with it.  Just to be safe i kept the log file intact and did not move or delete it.  Only problem as i mentioned is that it is 4x as large as mdf file so after switching mode to simple(client is not concerned with point in time restores and only wants previous day if necessary) then i would definately like to shrink the log file even especially since it was stated that these are all committed sql entries that are already in the mdf file.  Since i am not that familiar with SQL, i accepted the answer that suited my environment but may not be a good fit for many other organizations especially mission critical sites.  Thanks again for clarification.  
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36496226
By the way, if you want to move the LDF file, here is a reference to a method not involving detach. Requires an outage. Using ALTER DATABASE, you can change the path to the LDF file right before taking the DB offline. Move the files to the new path and bring DB online.

http://social.msdn.microsoft.com/forums/en-US/sqlsetupandupgrade/thread/b63f3b3e-6bfa-40aa-8b13-f6806d6bc3f4/

Look for the comment from Microsoft employee, Balmukund.
0
 

Author Comment

by:dankyle67
ID: 36496305
What i did recently as a test was to make a backup of the mdf files and ldf files and when i first looked at the size of data in sql mgmt studio it reported aroun 348g which is approximately size of mdf and ldf combined which makes sense.  Then i right clicked database and selected shrink.  Afterwards the size reported was aroung 67g which means log file was practically reduced entirely.  I had changed the recovery method to simple so was wondering this maybe reason i didnt have option to select log files when i performed shrink function only gave me choice of database itself.  Usually dont you have choice of shrinking only the log file?  
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36496379
Yes. Right-click Database > Tasks > Shrink > Files, then select File Type "Log".
SIMPLE recovery mode writes transactions to MDF soon after committed; therefore, the log space is usually immediately available for reuse or reclamation. That is why the log file reduced entirely BTW.
0
 
LVL 2

Expert Comment

by:awarren85
ID: 36496417
Just to re-interate, when I said deleting the log files, the database is in SIMPLE mode while doing this and the database is detached when deleting the log files.  Of course, you need the log files in any other mode.
0
 

Author Comment

by:dankyle67
ID: 36496449
Great, once again thanks for the clarification.  Made it clearer.  
0
 

Author Comment

by:dankyle67
ID: 36496526
also, my mistake was i selected database to shrink instead of files so thats why didnt see option for log files which now you  made clear, thanks again.  Oh one thing of importance i kept forgetting to mention along the way was that whenever i attempted a restore of database as a test, for instance i was restoring to test company 850 the bak file that i tried to restore would then show 2 files in options section and listed the mdf as well as ldf files but showed up as original location of file and said restore as "d:\program files\microsoft sql server\data which is the live company.  This concerned me at first sight since it looked like it could possibly restore to the original location and overwrite live data.  It ran fine and data populated correct test company 850.  I assume that when you do a restore, sql opens up the bak file and identifies where the original backups were coming from and their names as well.  Just need a yes or no confirmation on this.  Sorry didnt remember till now.
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 36496551
@awarren85 no matter what recovery model you are using, the transaction log is still used!

Only difference between simple and the other recovery models is that data is flushed form the transaction log by internal sql processes instead the backup log process. But this doesnt happen straight away.

Deleting the transaction log should only be used as a last resort in the event that there is no other way to bring the database back up! It shouldnt be used instead of the shrink process.

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36496563
Yes, it shows the original file paths and allows you yo specify the new ones. Therefore, that is another way to split LDF and MDF files...during a restore imply point them to different different drives/locations.
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 36496575
Hi dankyle67,

Yes this is correct, the bak file contains all original files and their locations.

Unless you select the "overwrite" checkbox the restore session will error out saying that a database already exists with that name.
0
 
LVL 2

Expert Comment

by:awarren85
ID: 36496618
@EvilPostIt:  That's why you detach first -- detach will not work if there are active connections.  A detach will cause SQL Server to "checkpoint" the MDF, causing the transaction log to flush:

http://msdn.microsoft.com/en-us/library/ms191164.aspx

There are several articles that list the detach, delete log files, re-attach method, including:

http://www.codeproject.com/KB/database/truncate_log_SQL_server.aspx
http://www.nigelrivett.net/TransactionLogFileGrows_1.html

0
 

Author Comment

by:dankyle67
ID: 36496634
Yes EvilPost i was really having hard time getting restore to work until i found that option to overwrite existing database.  The restore was long however since it brought back the log file too so i am assuming that in the future if i have to restore, the log file will be smaller since i did the shrink function already and yes it reduced file as mentioned considerably.  thanks
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 36501101
Hi Dan,

Yes, the size of the transaction log is recorded upon backup although not its inactive contents. In future it will be the size it was backed up at.

@awarren85, you may want to have a look at VLF's and what they are inside the log file.

http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx?pr=blog

For info regarding the author of this article http://www.sqlskills.com/AboutPaulSRandal.asp

As you can see from his bio he was in charge of the storage engine team (BTW storage engine would include log files!) and also in charge of the development of DBCC CHECKDB.

Also please note the comment just above the Recovery Models sections "Under no circumstances should you delete the transaction log"
0

Featured Post

Free camera licenses with purchase of My Cloud NAS

Milestone Arcus software is compatible with thousands of industry-leading cameras for added flexibility. Upon installation on your My Cloud NAS, you will receive two (2) camera licenses already enabled in the software. And for a limited time, get additional camera licenses FREE.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Finally comes the third version of VMware Virtual SAN, whose name is 6.1, and comes loaded with new features many of which really are excellent and even surprising.
In this Micro Tutorial viewers will learn how to restore single file or folder from Bare Metal backup image of their system. Tutorial shows how to restore files and folders from system backup. Often it is not needed to restore entire system when onl…
This tutorial will walk an individual through the steps necessary to install and configure the Windows Server Backup Utility. Directly connect an external storage device such as a USB drive, or CD\DVD burner: If the device is a USB drive, ensure i…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now