SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Restore failed due to large .bak file
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

aex
Yak Posting Veteran

60 Posts

Posted - 02/28/2007 :  22:28:20  Show Profile
I am trying to restore using one of my .bak file and the restore failed.
Sql server report this:


System.Data.SqlClient.SqlError: There is insufficient free space on disk volume 'C:\' to create the database. The database requires 83679641600 additional free bytes, while only 46151348224 bytes are available. (Microsoft.SqlServer.Smo)

What I guess from this message is that my .bak file is larger than
46151348224 bytes (my .bak file to be restored is 800MB in size), but i find no way to increase the restore size.

Any help will be much appreciated.

Thanks.

aex

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 02/28/2007 :  22:46:23  Show Profile
most likely the database you are restoring has large datafiles. The .BAK file can actually be much smaller than the size of the database datafiles because of unused space preallocated in the datafile. for example, you might have a 100GB datafile but only 25GB of it is in use.

How do you fix this?

1. restore the database to a system with more disk space
2. take a new backup but shrink your datafiles prior.

If you are a restoring a system in a disaster recovery scenario, obviously option 2 won't work for you. If you are attempting to clone an environment though (like a stage or QA environment from production), then option 2 might be of use.



-ec

Edited by - eyechart on 02/28/2007 22:47:30
Go to Top of Page

aex
Yak Posting Veteran

60 Posts

Posted - 02/28/2007 :  23:09:12  Show Profile
Thanks eyechart for the reply.

For your first suggestion, it doesn't apply to my problem here because the size of my c: has 30GB++

For the later suggestion, i did shrink the database weekly. So i think the data file is reduced to smaller size.

I tried to restore another .bak file which is smaller in size, roughly 400MB, and it has been restored successfully.

Do you have any idea what is going on?

aex
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 02/28/2007 :  23:16:57  Show Profile
the possibility is that the backup is corrupt. however, the message is telling you that you need ~80GB of free space to restore the database and you only have ~42GB free. Try restoring to a system with more than 80GB free.



-ec
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 02/28/2007 :  23:22:40  Show Profile
Also, you can use the RESTORE FILELISTONLY command to list the database datafiles that the backup will restore and the sizes needed for them on disk.

syntax is something like this:

RESTORE FILELISTONLY
FROM DISK = '\\path\to\backup\backupname.BAK'


The SIZE column in the resultset will tell you how much space each datafile is going to be on disk in bytes. Look this command up in BOL for more details.


-ec

Edited by - eyechart on 02/28/2007 23:23:35
Go to Top of Page

aex
Yak Posting Veteran

60 Posts

Posted - 03/01/2007 :  00:33:04  Show Profile
Thanks again eyechart for the suggestion and i am going to try on that.

I have another question here which i want to know is that possible to restore only .mdf from .bak file by ignoring totally .ldf file.

aex
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 03/01/2007 :  01:09:28  Show Profile
quote:
Originally posted by aex

I have another question here which i want to know is that possible to restore only .mdf from .bak file by ignoring totally .ldf file.




nope.


-ec
Go to Top of Page

aex
Yak Posting Veteran

60 Posts

Posted - 03/27/2007 :  11:04:58  Show Profile
I posted this thread a month ago and because I am in extreme busy with my work and I have forgotten to thank to eyechart for giving him valuable opinion which help to solve my problem. Here I share out my experience for those who googling and get to this thread.

If you failed to backup a very big .bak file, try to use RESTORE FILELISTONLY... as suggested by eyechart to check what is the data file size. If the file size is larger than the database can accept then the database restore wouldn't work.

What happen here is whenever you create a new database, sql server will create two files, which one with .mdf extension and another is .ldf. For any database operation such as insert, delete, update, etc that you perform, sql server will keep it as a log in .ldf file. As database operation is keeping on executed for some time and each operation involves massive amount insert, delete... operation, then the .ldf file will grow bigger. So, what you need to do is to backup the database then shrink the log file. After the shrink operation, as you might guess, data file is shrinked into smaller size. Then only you backup again the shrinked-database and you will able to restore it in other sql server.

Again, thanks to eyechart for helping me to get rid of this problem.

aex
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000