SQL Server Performance

Error In Restoring

Discussion in 'SQL Server 2005 General DBA Questions' started by matrixchyah2005, Feb 12, 2007.

  1. matrixchyah2005 New Member

    Hi,
    I tried restoring a database backup file to an existing fresh DB in SQL2005 Server, it gives a error saying no space , i'am pasting the error below, Bak file size is 2.6 GB and drive is Fat32 with 19GB free space . when i checked the bak file using restore filelistonly the transaction log is showing approximately 7GB.
    what can be the problem in this situation? how can i solve it?

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------
    Restore failed for Server 'TestSQL2005'. (Microsoft.SqlServer.Smo)
    ------------------------------
    ADDITIONAL INFORMATION:
    System.Data.SqlClient.SqlError: MODIFY FILE encountered operating system error 112(There is not enough space on the disk.) while attempting to expand the physical file. (Microsoft.SqlServer.Smo)
  2. ranjitjain New Member

  3. matrixchyah2005 New Member

    Thanks for the link ranjit , according to that information it says we need to reduce the size of transaction log then take a backup and then restore.
    But in my case i don't have the live DB to take a backup only i have is the bak file....
    can we do something to restore this backup????

    thanks in advance

    Mat

  4. satya Moderator

    There is not enough space on the disk
    So you need to check whether the disk has free space where the data & log files are created.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  5. matrixchyah2005 New Member

    I checked and the drive where i'am trying to create the data & log files is having 19GB free space.
    Backup file is only 2.6 GB

    Mat
  6. madhuottapalam New Member

    confirm once again... hope it is not 26 GB...

    Madhu
  7. matrixchyah2005 New Member

    Yes i do confirm its 2.6 GB not 26 GB and when i checked the bak file using restore filelistonly command data file is almost 2.6GB and transaction log is approximately 7GB but still 19 GB free space is available in the disk....is SQL2005 taking any additional space in restore process and release after that???

    Mat
  8. satya Moderator

    Run RESTORE FILELISTONLY and see what is the path it is defined.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  9. matrixchyah2005 New Member

    I checked the path it is "D:program FilesMicrosoft SQL ServerMSSQLData" in the backup file, but the database i'am trying to restore is in path "D:Inv Data" which is in the same drive with free space 19GB.

    Mat
  10. satya Moderator

    Then you have to use WITH MOVE option in RESTORE statement, also check how much free space available on C: drive.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  11. matrixchyah2005 New Member

    Free space available in C drive is 6.76 GB...
    Can you provide me a sample code for WITH MOVE option in RESTORE statement because when i tried before it was not working...

    Mat
  12. madhuottapalam New Member

    RESTORE FILELISTONLY FROM DISK = 'D:SomeBackupFile.Bak'

    RESTORE DATABASE SomeDb FROM DISK = 'D:SomeBackupFile.Bak'
    WITH MOVE 'Logical Name of Datafile' TO 'D:Inv DataSomeDB.mdf',
    MOVE 'Logical Name of LogFile' TO 'D:Inv DataSomeDb_log.ldf'

    Logical Name for Log and data file will get from RESTORE FILELISTONLY FROM DISK = 'D:SomeBackupFile.Bak'

    See Restoer Database in BOL also

    Madhu



  13. gpboy New Member

    We're having the same issue. We do not have access the live DB.


    RESTORE FILELISTONLY FROM DISK = Our DB
    Size = D PRIMARY 4201447424
    Size = L NULL 161314570240

    Our Test SQL Server:
    c: 19GB free, d: 30 GB free, Logs: 30GB free, Data: 22 GB free

    I've currently done:

    restore database OurDB from disk = 'f:MSSQLMSSQLBACKUPxxx.bak'
    with file = 1,
    NoRecovery, Partial,
    Move 'xxxDB' to 'C:program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataxxx.mdf',
    Move 'xxx_log' to 'E:xxx_log.ldf'

    The DB is currently in a Restore state and I can't even restore the log file.

  14. madhuottapalam New Member


    select (161314570240/1000000000) =161.31457024000 -- it means u r log is 161GB
    select (4201447424 /1000000000) =4.20144742400 -- u r db is of 4.5 GB

    so system is rightly said that there is no enough space. what u can do is truncate the log before taking the bakup. Take the full backup after the truncation.

    Madhu

  15. MohammedU New Member

    Truncate and shrink the log before taking backup.

    BACKUP LOG <DB NAME> WITH TRUNCATE_ONLY
    GO
    DBCC SHRINKFILE (2, 1000) --To shink log file to 1GB.
    GO

    How to stop the transaction log of a SQL Server database from growing unexpectedly
    http://support.microsoft.com/kb/873235

    Shrinking the Transaction Log
    http://msdn2.microsoft.com/en-us/library/aa174524(SQL.80).aspx

    Why you want to be restrictive with shrink of database files
    http://www.karaszi.com/SQLServer/info_dont_shrink.asp

    MohammedU.
    Moderator
    SQL-Server-Performance.com
  16. matrixchyah2005 New Member

    I tried the syntax given by Mr.Madhu but i got the following error..

    Msg 3154, Level 16, State 4, Line 1

    The backup set holds a backup of a database other than the existing 'DBNAME' database.

    Msg 3013, Level 16, State 1, Line 1

    RESTORE DATABASE is terminating abnormally.



    Mat
  17. madhuottapalam New Member

    the error is self descriptive...

    just post the result of

    RESTORE FILELISTONLY FROM DISK = 'D:SomeBackupFile.Bak'

    Madhu
  18. matrixchyah2005 New Member

    Here it is..

    TanjungMaria D:program FilesMicrosoft SQL ServerMSSQLDataTanjungMaria.mdf D PRIMARY 2693070848 35184372080640 1 0 0 00000000-0000-0000-0000-000000000000 0 0 0 512 1 NULL 16826000000011300002 FFA4D584-306A-4645-924C-880C1BCF553E 0 1
    TanjungMaria_log D:program FilesMicrosoft SQL ServerMSSQLDataTanjungMaria_log.ldf L NULL 6629031936 35184372080640 2 0 0 00000000-0000-0000-0000-000000000000 0 0 0 512 0 NULL 0 00000000-0000-0000-0000-000000000000 0 0


    and the restore command i tried is following

    RESTORE DATABASE Tanjungmaria FROM DISK = 'D:INV.Bak'
    WITH MOVE 'TanjungMaria' TO 'E:Inv DataTanjungmaria.mdf',
    MOVE 'TanjungMaria_log' TO 'E:Inv DataTanjungmaria_log.ldf'

    Thanks
    Mat
  19. madhuottapalam New Member

    I hope u r not restoring SQL Server 2005 backup in SQL Server 2000....

    Madhu
  20. matrixchyah2005 New Member

    No its not like that backup is from SQL2005 only with same edition..

    Mat
  21. madhuottapalam New Member

    do u already have a database called Tanjungmaria? if YES... then Drop that database first...

    Drop Database Tanjungmaria --To drop if exists

    RESTORE DATABASE Tanjungmaria FROM DISK = 'D:INV.Bak'
    WITH MOVE 'TanjungMaria' TO 'E:Inv DataTanjungmaria.mdf',
    MOVE 'TanjungMaria_log' TO 'E:Inv DataTanjungmaria_log.ldf'

    Madhu
  22. matrixchyah2005 New Member

    i tried dropping the existing DB but still that space error is coming telling it can't restore the transaction log.
    one thing i noticed is after that error came the mdf file is getting created with 2.6 GB and transaction log is 0 Bytes .
    Can we do some turn around using that mdf file like attaching and creation a new transaction log???
    i tried attaching but it too is not working????


    Mat
  23. madhuottapalam New Member

    what is the error u gets when u try to drop Tanjungmaria....

    do one thing.. Just restore the database with some other name

    Run the following script as it is


    RESTORE DATABASE TanjungmariaTest FROM DISK = 'D:INV.Bak'
    WITH MOVE 'TanjungMaria' TO 'E:Inv DataTanjungmariaTest.mdf',
    MOVE 'TanjungMaria_log' TO 'E:Inv DataTanjungmariaTest_log.ldf'

    Madhu
  24. gpboy New Member

    MohammedU,

    I do not have the option to request a different backup method. Is it still possible to restore?

    Thanks.


    quote:Originally posted by MohammedU

    Truncate and shrink the log before taking backup.

    BACKUP LOG <DB NAME> WITH TRUNCATE_ONLY
    GO
    DBCC SHRINKFILE (2, 1000) --To shink log file to 1GB.
    GO

    How to stop the transaction log of a SQL Server database from growing unexpectedly
    http://support.microsoft.com/kb/873235

    Shrinking the Transaction Log
    http://msdn2.microsoft.com/en-us/library/aa174524(SQL.80).aspx

    Why you want to be restrictive with shrink of database files
    http://www.karaszi.com/SQLServer/info_dont_shrink.asp

    MohammedU.
    Moderator
    SQL-Server-Performance.com
  25. MohammedU New Member

    If you have free space on your network where this server can access it...
    You can use trace flag 1807 which allow you to create/restore the db file on network mapped drives or UNC locations...

    This trace flag is generally unsupported under SQL Server 7.0 and SQL Server 2000.

    I believe it will work in 2005 also...

    Restore the db to UNC path then detach the db and attach as single file... sql will create small tlog file...





    MohammedU.
    Moderator
    SQL-Server-Performance.com
  26. gpboy New Member

    Funny that you mentioned this. I did try it yesterday and it looks like the backup we got has corrupted transaction log. It got stuck on 90% restore both times.
    Thanks for all your help.


    quote:Originally posted by MohammedU

    If you have free space on your network where this server can access it...
    You can use trace flag 1807 which allow you to create/restore the db file on network mapped drives or UNC locations...

    This trace flag is generally unsupported under SQL Server 7.0 and SQL Server 2000.

    I believe it will work in 2005 also...

    Restore the db to UNC path then detach the db and attach as single file... sql will create small tlog file...





    MohammedU.
    Moderator
    SQL-Server-Performance.com
  27. MohammedU New Member

    Funny that you mentioned this. I did try it yesterday and it looks like the backup we got has corrupted transaction log. It got stuck on 90% restore both times.
    Thanks for all your help.


    I am confused about your answer...
    You mean trace flag corrupted the log or your backup file corrupt?

    I used trace flag 1807 many time, I never had issues with it but make sure you have reliable network between servers when you use UNC files for db.
    And it not support and undocumented...

    MohammedU.
    Moderator
    SQL-Server-Performance.com

Share This Page