Error In Restoring | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Error In Restoring

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)
Hi,
Please go through this thread with same error discussion
http://www.dbforums.com/showthread.php?t=648197
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
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.
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
confirm once again… hope it is not 26 GB… Madhu
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
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.
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
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.
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
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
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.

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

the error is self descriptive… just post the result of RESTORE FILELISTONLY FROM DISK = ‘D:SomeBackupFile.Bak’ Madhu
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
I hope u r not restoring SQL Server 2005 backup in SQL Server 2000…. Madhu
No its not like that backup is from SQL2005 only with same edition.. Mat
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
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
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
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

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

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