November 29, 2005 at 2:54 pm
Here's the deal; I have a bak file that is 11GB in size. When I try to restore it, ENt Mgr tells me I am coming up short in drive space. When I factor how short I am with what I do have I am able to determine that the DB is looking for about 70GB of drive space. Now I don't know if all of that is wasted log file space or if it is split between the data & the log files and I really don;t care. What I need is a way to get this thing restored, using only as much space as the DB truely needs and not the massize amounts it is wanting. I know that the bak file is not compressed and so if it is 11GB then the actual amount of drive space needed to restore it should not need to be 70GB.
Anyone have any idea on how to get this beast restored? I do not have the ability to get another backup nor a mdf file so attaching is not an option unless it can somehow be done with a bak file.
Thanks
Ed
Kindest Regards,
Just say No to Facebook!November 29, 2005 at 4:42 pm
How big is the database from which the backup was made?
November 30, 2005 at 1:13 am
When you are restoring the database, you are using the Options tab to configure where to create the files, right?
Normally I configure a server to default to the Log and Data partitions for new databases, so forget that some DBAs don't and have caught myself trying to restore to the C drive.
No wonder says I, and was surprised at the answer, you dummy!
Andy
November 30, 2005 at 2:20 am
Ed,
Unfortunately, if the database you are restoring had an 80 GB data/log file (even if it contained unused space), when you restore it SQL Server will attempt to create the matching file structure. In other words, it will try to create an 80 GB file, which will be mostly empty by the sounds of it.
Run the following in QA:
restore
filelistonly from disk = '<file path and file name for backup file>'
That will tell you how many database and log files are contained in the backup file and their respective sizes. You're basically going to need to find a drive (or combination of drives) to fit those files in.
As a follow up: if you don't have enough space on the above SQL Server: What you can do is restore the database to a SQL Server with sufficient space, shrink the log and data files and take a new backup.
November 30, 2005 at 6:33 am
Thanks to everyone for your replies. I just can't believe that there is no way in SQL Server to deal with a case like this, where the client has backed up a file with an unecessarly large amount of unused space allocated for the log file, enough so that the person trying to restore it can not do so because they don;t have that much free drive space.
What about in SQL Server 2005? If I have SQL2K5 does anyone know if there is a way to restore this DB without having to allocate so much dead space?
Karl: I tried running the FILELIST command and unfortunately it erros out each time telling me that it 'exceeded retry count' and followed by 'terminating abnormally'. Does that error really mean anything releveant like the backup is bad?
Thanks to everyone again for your help! SQLServerCentral.com rocks!
Ed
Kindest Regards,
Just say No to Facebook!November 30, 2005 at 7:17 am
Ed,
I agree it is a pain in the but as I deal with it daily.
The one thing you might be able to do, is shrink the data and log files on the database it was backed up from and then backup and restore that. This isnt the prettiest of solutions, but it should work.
One other thought, you could always really cheat and get one of those USB external drives that have 100 or more GB, put it on, and use it to restore, then shrink and restore again. They aren't that expensive and could be faster than getting an extra hard drive.
November 30, 2005 at 8:06 am
Ed,
about the filelist error you got. Haven't seen that one before to be honest.
Run the following:
restore verifyonly from disk = ''
In theory it should return "Backup set is valid".
November 30, 2005 at 2:44 pm
SQL Server doesn't know that you don't want that extra space. It doesn't really know that it is unused space. It just knows that the backup uses 80 GB of space and you only have 70 GB of space (those numbers just used for example). Until the backup is restored, the sizes are just numbers to the system. Once it is restored, then it knows what the numbers mean and how much space is used and unused.
-SQLBill
December 5, 2005 at 1:02 pm
Thanks to everyone for offering some input. Unfortunately in the end we just had to remove everything off of our SQL Server hard drives in order to make room for a 60GB log file that in reality had 1 GB of data. Very frustrating. It amazes me that even as of SQL 2005, Microsoft has not come up with a way to address artificially bloated files in a backup like this. Even though its true that it is the users responability to make sure they have their file sizes set correctly, when you work in support you are not always going to work with clients who manage their DB's correctly. ANd you may have no way of knowing the fiels are artificially inflated until you've gotten them restored and looked at for yourself. Oh well.
Ed
Kindest Regards,
Just say No to Facebook!December 5, 2005 at 1:39 pm
Suggestion:
Create a script that shrinks the files and then does the backup. Supply the script to your clients whenever you need them to send you backup files.
-SQLBill
December 6, 2005 at 1:33 pm
What database system do work on that allows you to take a backup and compress the file contents. I work with several other systems (oracle, db2) and what I backup is what I restore.
A solution to large log files is to backup the log more often so that it doesn't grow so big. If there is a batch job that caused it to grow that big, do incremental commits.
This is not a sql server backup problem. It's an admin problem.
--Tom
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply