| Author |
Topic |
|
aex
Yak Posting Veteran
60 Posts |
 Posted - 2007-02-28 : 22:28:20
|
| 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 than46151348224 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
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-02-28 : 22:46:23
|
| 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 space2. 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 |
 |
|
|
aex
Yak Posting Veteran
60 Posts |
Posted - 2007-02-28 : 23:09:12
|
| 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 |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-02-28 : 23:16:57
|
| 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 |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-02-28 : 23:22:40
|
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 FILELISTONLYFROM 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 |
|
|
|
aex
Yak Posting Veteran
60 Posts |
Posted - 2007-03-01 : 00:33:04
|
| 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 |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-03-01 : 01:09:28
|
quote:
Originally posted by aexI 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 |
|
|
|
aex
Yak Posting Veteran
60 Posts |
Posted - 2007-03-27 : 11:04:58
|
| 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 |
|
|
|
|
Restore failed due to large .bak file