Online backup does not include log file

Discussion in 'Databases' started by jevans, Apr 23, 2008.

Thread Status:
Threads that have been inactive for 5 years or longer are closed to further replies. Please start a new thread.
  1. jevans

    jevans

    Hi -


    I used the backup utility to backup my db on Discountasp.net. I then copied the zipped up .bak file to my local machine where I wanted to restore it to my local sql2005 instance. The problem is that there is no log (.ldf) file in the _database folder and so there is no log file included in the backup. SQL server does not want to allow me to recover without an included log file. Any suggestions?

    Thanks

    Jim Evans
     
    jevans, Apr 23, 2008
    #1
  2. JorgeR

    JorgeR DiscountASP.NET Staff

    JorgeR, Apr 25, 2008
    #2
  3. jevans

    jevans

    Hi Junior -

    I respectfully disagree with your reply. But the backup utility *DID NOT* create both files. It only created the .mdf file. I opted for zipped mode so that it would automatically create a .zip file but that should not make a difference. I still have the file that was created and I can send it to support for analysis.

    Jim Evans
     
    jevans, Apr 28, 2008
    #3
  4. Bruce

    Bruce DiscountASP.NET Staff

    backup does not produce .mdf file (.mdf file is the database file) but rather a .bak file (which is the standard backup file). Are you sure you copy down the correct file?


    Bruce

    DiscountASP.NET
    www.DiscountASP.NET
     
    Bruce, Apr 29, 2008
    #4
  5. jevans

    jevans

    Bruce -


    You are correct - the zip file contains a .bak only - no .ldf.


    The problem is that SQL Server restore seems to require a log (.ldf) file and will not restore from that .bak file alone. I am trying to copy my production db (on discountasp.net) to my local dev environment.


    Thanks - Jim


    Jim Evans
     
    jevans, Apr 29, 2008
    #5
  6. JorgeR

    JorgeR DiscountASP.NET Staff

    Jim,

    I am interested to see the process that you are taking to restore the database What I was referring to the statement '... backup from the control panel will always create a backup with the mdf and ldf file in it.', is that when you create a bak file from the control panel, it will create a database backup with a bak extension. Inside that extension, SQL server places the mdf and ldf file with in that file structure. The only way to restore the database bak file (mdf & ldf file ) is to restore from device in enterprise manager or use the restore database command. try adding a capture image of the error that you are getting with the log file

    Please reference the url - http://msdn2.microsoft.com/en-us/library/ms177429.aspx on restoring the database

    junior


    junior

    DiscountASP.NET

    www.DiscountASP.NET
     
    JorgeR, Apr 30, 2008
    #6
  7. jevans

    jevans

    Junior -


    It turns out that you are correct - both files are contained in the .bak file. The problem arises when using SQL Server Management Studio's GUI to do the restore as the file paths are not the same as those contained in the.bak file. I endend up running script in query analyser similar to the following (replaced my paths and names here for security reasons) and was successful. "C:\Work\YourAPP\YourDB_backup.bak" would be the location of the .bak that you want to restore from.


    RESTORE FILELISTONLY


    FROM DISK = 'C:\Work\YourAPP\YourDB_backup.bak'


    RESTORE DATABASE YourDB


    FROM DISK = 'C:\Work\YourAPP\YourDB_backup.bak'


    WITH MOVE YourDB_data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\yourdb.mdf',


    MOVE 'YourDB_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\yourdb_log.ldf'


    Jim Evans
     
    jevans, May 1, 2008
    #7
Thread Status:
Threads that have been inactive for 5 years or longer are closed to further replies. Please start a new thread.

Share This Page