which files to backup in sql 2008

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.

Here is a reference: http://msdn.microsoft.com/en-us/library/ms186289.aspx

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.
https://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.

I Agree @mwvisa1, that's the way it's recommended to do, however the above procedure works in a pinch.

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.

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

Select allOpen 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.

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?

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.

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.

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.  

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'.  

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?  

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.  

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

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.

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.

Thanks again for the clarification and step by step instructions.  Good stuff

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!

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...

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.

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.  

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.

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?  

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.

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.

Great, once again thanks for the clarification.  Made it clearer.  

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.

@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.

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.

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.

@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

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

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"