July 10, 2007 at 7:20 am
Hi,
I'm attempting to restore a production database to a test server. The databases have the same name. The problem is the log file on the production server from whence my backup file came is some 12GB but the actual usable data is only about 3gb. I don't have 12 GB free on the test server but when I try restoring the database to my test server after allocating 4GB data and 1GB log it fails. It appears it wants exactly the same size allocated as that in production. Is there any way round this. I'm using SQL Server 2000.
cheers..
July 10, 2007 at 7:23 am
You can truncate the log in the production server and take the backup and restore it but remember that truncte the log you will not be able to recover if the database crahses. So you have no other option except to have 12 GB space in the drive to accomdate the backup else restore in a server that has enough space truncate log and the backup and then resotre that backup file.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 11, 2007 at 4:32 pm
If there is only 3GB being used in a 12GB log file, you may be able to shrink the production log file using DBCC SHRINKFILE. Then do the backup and grow the production log file back to 12GB. The shrink and grow will impact your production environment so it's best to do "off hours".
July 13, 2007 at 1:25 am
If you are using Maintenance Plans then there is a option to remove unused space before backups.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 13, 2007 at 3:01 am
Hi,
you might plug in an usb-drive, restore using the usb-drive for the log file, then shrink the new log file to 1 GB and move it to the destination drive.
regards
karl
Best regards
karl
July 16, 2007 at 1:26 am
Thanks guys for your input - what we did eventually was have IBM allocate some more space on the test server to restore the database and then we were able to shrink the log and consequently the database to a sensible size and take a backup. The problem we (development DBAs) face is we have no access to production and weren't able to shrink it there.
thanks again - some great ideas...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply