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

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

I checked the path it is "Drogram 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

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 'Crogram 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 Drogram 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 Drogram 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