locked
Is log file needed for backup? RRS feed

  • Question

  • Hi all, I'm wondering if a backup done without the log file, could this cause an issue when restoring the database? Background, we use Backup Exec to do our back ups. The person that was handling this chore passed away a few weeks ago and we are left trying to figure out what he did. He had setup the backup and it appears to be only backing up the data file and not the log file. Could this be a problem in the future? Also, what do you all think of Backup Exec for backing up sql server? Is there anything else that is better? Thanks Mike
    Wednesday, January 23, 2013 5:33 PM

Answers

  • Hi Mike,

    I think you could use SQL Server GUI or T-SQL to backup SQL Server databases. For a data backup file without log file, you could restore to the point the backup process taken but could not restore to any point without log file. I recommend you backup SQL Server data and log regularly.

    Refer to How to: Back Up a Database (SQL Server Management Studio): http://msdn.microsoft.com/en-us/library/ms187510%28v=SQL.100%29.aspx.
    BACKUP (Transact-SQL): http://msdn.microsoft.com/en-us/library/ms186865.aspx

    If you have any feedback on our support, please click here.

    Thanks.


    Maggie Luo
    TechNet Community Support

    • Marked as answer by Maggie Luo Thursday, January 31, 2013 9:41 AM
    Thursday, January 24, 2013 8:44 AM
  • Again, you don't tell SQL Server to backup the database file or the log file. That is a misconception.

    You tell SQL Server to perform a full backup or a transaction log backup. That is *not* the same thing as copying the mdf or ldf file. Important distinction!

    If you are alright with the frequency you perform database backups (you can afford the data loss for inbetween period), then to only do full backups (aka database backups) is fine. Just make sure you set recovery model to simple. That error message is disturbing since it indicates that backup exec don't have much understanding how you handle the transaction log in SQL Server.

    You either don't perform log backups (if that is ok with you RPO) and set the recovery model to simple.

    Or you *do* perform log backups and set recovery model to full.

    If you have recovery model to full and *don't* perform log backups, then the log will grow and grow. Very common rookie mistake. See for instance http://www.karaszi.com/SQLServer/info_large_transaction_log_file.asp.


    Tibor Karaszi, SQL Server MVP | web | blog

    • Marked as answer by Maggie Luo Thursday, January 31, 2013 9:43 AM
    Thursday, January 24, 2013 6:43 PM

All replies

  • I hope that you don't just backup the underlying database files. That is not how backup is done in SQL Server.

    What you do is to connect to SQL Server (possibly your 3:rd party backup tool) and execute a SQL BACKUP command. You might be satisfied with, say, only Daily full (database) backups (BACKUP DATABASE). If you can afford to lose one day work, of course. If that isn't OK, then you want to do more frequent backups, probably transaction log backups (BACKUP LOG). There is also an option to do differential backups, most ofthen if you already do full and log backups and you want to have something in between. This is a huge topic, but this should get you going with the thought process.


    Tibor Karaszi, SQL Server MVP | web | blog

    • Proposed as answer by Kalman Toth Wednesday, January 23, 2013 10:00 PM
    Wednesday, January 23, 2013 5:40 PM
  • With BackupExec, you need to use the SQL Server add-in to connect to the SQL Server and do a backup.   You cannot simply backup the .mdf files and restore.

    Wednesday, January 23, 2013 5:42 PM
  • Hi Mike,

    I think you could use SQL Server GUI or T-SQL to backup SQL Server databases. For a data backup file without log file, you could restore to the point the backup process taken but could not restore to any point without log file. I recommend you backup SQL Server data and log regularly.

    Refer to How to: Back Up a Database (SQL Server Management Studio): http://msdn.microsoft.com/en-us/library/ms187510%28v=SQL.100%29.aspx.
    BACKUP (Transact-SQL): http://msdn.microsoft.com/en-us/library/ms186865.aspx

    If you have any feedback on our support, please click here.

    Thanks.


    Maggie Luo
    TechNet Community Support

    • Marked as answer by Maggie Luo Thursday, January 31, 2013 9:41 AM
    Thursday, January 24, 2013 8:44 AM
  • Hi

    SQL server database Backup cant be done using only mdf file. But if you have shutdown database clenely then you can restore database with only datafile available (if you have copy of mdf file available)

    Please refer below link , this explains how backup works(either you are using third party tool).

    http://technet.microsoft.com/en-gb/magazine/2009.07.sqlbackup.aspx

    Thanks

    Saurabh Sinha

    Thursday, January 24, 2013 9:18 AM
  • Thanks everyone,

    I had thought it was important to backup the log files along with the data files, but I was assured by my ex-co worker that this was ok. I believe he was using the Backup exec addin for sql serer, cause I noticed that it had an area where you could select SQL Server and then select the database, but it didn't give you the options to select the actual db files (just the database). In the email notifications I receive the following message from backup exec:

    V-79-40960-37914 - Database Alternitives_Brook2 is configured to maintain transaction logs. Transaction log backups are not being performed. This will result in the log growing to fill all available disk space. Regular log backups should be scheduled or the database should be changed to the simple recovery mode.

    I will check out the links that you all provided. I might just use the tools within SQL server to do the backup, cause I'm not sure why backupexec is not backing up the log file. Thanks for the input.

    Thursday, January 24, 2013 12:13 PM
  • You would be good if you will take log backup provided you have healthy full database backup & all log backups post that full backup.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful, if a post was useful to help other user's find a solution quicker.

    Thursday, January 24, 2013 12:20 PM
  • Again, you don't tell SQL Server to backup the database file or the log file. That is a misconception.

    You tell SQL Server to perform a full backup or a transaction log backup. That is *not* the same thing as copying the mdf or ldf file. Important distinction!

    If you are alright with the frequency you perform database backups (you can afford the data loss for inbetween period), then to only do full backups (aka database backups) is fine. Just make sure you set recovery model to simple. That error message is disturbing since it indicates that backup exec don't have much understanding how you handle the transaction log in SQL Server.

    You either don't perform log backups (if that is ok with you RPO) and set the recovery model to simple.

    Or you *do* perform log backups and set recovery model to full.

    If you have recovery model to full and *don't* perform log backups, then the log will grow and grow. Very common rookie mistake. See for instance http://www.karaszi.com/SQLServer/info_large_transaction_log_file.asp.


    Tibor Karaszi, SQL Server MVP | web | blog

    • Marked as answer by Maggie Luo Thursday, January 31, 2013 9:43 AM
    Thursday, January 24, 2013 6:43 PM