| Author |
Topic |
|
rudesyle
Posting Yak Master
110 Posts |
 Posted - 2007-05-21 : 12:32:50
|
| I have an 80 gig server. My client has sent me a backup of their db. The actual data file is 47 gigs. The client didn't truncate the transaction log, and on the backup , it is 37 gigs.Question : when restoring this to my dev box, do I need to restore the transaction log, or, is there a way to truncate it so the 37 gigs isn't transferred to the server?Thanks |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-05-21 : 12:45:34
|
| If it's a backup you'll have to restoer the whole thing.If it's a single .mdf and .ldf you could get the client to just send the .mdf and attach that.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-21 : 14:40:29
|
| SQL Server is gonna use up 37GB to make room for the 37GB LDF file that existed when the Backup was created.Once the Restore has finished you can truncate the 37GB LDF file (log), but that assumes you have enough disk space for the Restore in the first place!Kristen |
|
|
|
rudesyle
Posting Yak Master
110 Posts |
Posted - 2007-05-21 : 15:10:39
|
quote:
Originally posted by Kristen
SQL Server is gonna use up 37GB to make room for the 37GB LDF file that existed when the Backup was created.Once the Restore has finished you can truncate the 37GB LDF file (log), but that assumes you have enough disk space for the Restore in the first place!Kristen
Yeah, that's kind of the dilema (getting it restored). Once that's done, I can do the shrinking. Clients are wonderful! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-21 : 15:14:01
|
Well, you can ask for the MDF file as Nigel suggests. Mind you, if there is a whole load of Slack in that then you are still going to have some bloat."Clients are wonderful!"I most CERTAINLY would not Shrink my production database, carefully sized and optimised, just because you wanted a copy  Kristen |
|
|
|
rudesyle
Posting Yak Master
110 Posts |
Posted - 2007-05-21 : 15:25:35
|
quote:
[i]I most CERTAINLY would not Shrink my production database, carefully sized and optimised, just because you wanted a copy Kristen
Oh sure ... I'm sooooo board I just requested a copy for my own amusement ;) |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-05-21 : 22:29:17
|
| If you are going to be doing the kind of work that requires you to restore databases that size, you should make sure you have enough disk space available.Just like your client needs to make sure they have enough disk space to restore the database if they need to.CODO ERGO SUM |
|
|
|
rudesyle
Posting Yak Master
110 Posts |
Posted - 2007-05-22 : 10:53:19
|
quote:
Originally posted by Michael Valentine Jones
If you are going to be doing the kind of work that requires you to restore databases that size, you should make sure you have enough disk space available.Just like your client needs to make sure they have enough disk space to restore the database if they need to.CODO ERGO SUM
Ah, I know. But when the client tells you the db is around 20 gigs, and then sends you a 44 gig db, with a 32 gig, non-truncated transaction log..... you do what you got do. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-22 : 12:00:15
|
| I still don't get whats wrong with that or why you are pissed at the client. A 32GB Log on a 20GB database is a bit large, but not by much; we work on LDF being 120% of the MDF size.If you don't have the disk space just restore it to a compressed folder, truncate the DB, back it up again, then restore it somewhere uncompressed.Kristen |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-22 : 12:22:01
|
| Or just ask client send you a clean backup. |
|
|
|
rudesyle
Posting Yak Master
110 Posts |
Posted - 2007-05-22 : 12:53:51
|
quote:
Originally posted by Kristen
I still don't get whats wrong with that or why you are pissed at the client. A 32GB Log on a 20GB database is a bit large, but not by much; we work on LDF being 120% of the MDF size.If you don't have the disk space just restore it to a compressed folder, truncate the DB, back it up again, then restore it somewhere uncompressed.Kristen
I'm not pissed at the client at all. That Michael Valentine mentioned that I should be prepared in advance, and I was, based off of what the client relayed to me.I actually completed this yesterday. At the time I just wanted to know if there was anyway to just restore the mdf file from a backup. I now know that there isn't. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-22 : 13:00:00
|
| OK, I've got it now. |
|
|
|
|
Restoring a huge database