How can I reduce my SQL Server 2005 Backup file? Something to do with the log file?

Hi,

I have a large SQL Server 2005 BAK file which looks to be about 1.6GB, but when restored looks to need about 18GB. The Log file looks huge. I only want the data from the database. How can I minimise the size of this restore job. Also I think the backup method was "FULL". A colleague did this for me, perhaps I may need to redo this using another method avoiding the Log file somehow, or can I restore the BAK file requiring far less space using some form of "NO LOG" instruction.

Thanks,

Sam
SamJollyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dwkorCommented:
There are no SAFE ways to perform database backup without transaction log. In case if you want to move data outside of production environment, you can detach the database, copy only data files and attach it with sp_attach_single_file_db (http://msdn.microsoft.com/en-us/library/ms174385.aspx).  Although, like I said, it's not for production database.

What exactly are you trying to accomplish? Is it single time action or do you need to move data from one environment to another based on some time interval? In the second case you can consider to use some of HA techniques, perhaps log shipping.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SamJollyAuthor Commented:
Hi,

Thanks for this.

Basically what I am trying to do is take a backup of a live database for deployment on my dev server. The main reason is to have a ready backup of the tables should I have a problem with an imminent upgrade to the live database. If it goes pearshape I can run a compare and restore the relevant tables back to what they need to be. Also it is good to have an upto date copy of the DB on my dev server. However I cannot do a detach on the live server since this would mean taking the DB offline.

I thought the MDF contains the actual data and the log contains the historic transactions which I do not need. I just need the data as is now...

Thanks,

Sam
dwkorCommented:
Hi Sam,

SQL Server uses write-ahead technique - e.g. when you update the data, it puts the record to transaction log (synchronously) and update data in the memory cache. Next, at some point, it flushes the data to the data file. E.g. you can have the situation when data in the data file has not been updated but update record is in the log. In such case, in case of crush, SQL Server redo the transaction based on the data from the log. In real life it's way more complicated but it can give you some ideas.

In your specific case - what kind of backup strategy is your company uses? If your DBAs do regular backups (as they should), perhaps you can ask them for one to install it on dev environment. Even if it's time consuming, it could be one time action only in your case. Next, if you have Enterprise or Developer edition, you can use database snapshot during the testing (and be able to rollback your changes almost immediately). If you don't use Enterprise/Developer edition you can apply your changes in transaction and rollback transaction if something happens. This would allow you not to restore DB again and again. And of course, virtualization (and VM snapshots) are always the options.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

SamJollyAuthor Commented:
Well I have got a BAK file from the DBA which I am trying to restore. It is just too big at 18GB. So with this BAK file it seems that I have no options apart from finding 18GB on my HDD which is possible.

Sam
dwkorCommented:
It would be the simplest option. Next, for your purposes, you can switch database to simple recovery mode and shrink transaction log. Just don't do it in production :)
SamJollyAuthor Commented:
Aah.... so could I ask my DBA to recreate the BAK file using "simple recovery mode" (instead of full recovery mode) which would shrink the log file ??????

Thanks,

Sam
Scott PletcherSenior DBACommented:
>> Aah.... so could I ask my DBA to recreate the BAK file using "simple recovery mode" (instead of full recovery mode) which would shrink the log file ?????? <<


The log file has be to be shrunk *before* the .BAK file is created.

In SQL 2005, he can either set the recovery to simple or just truncate the log.

The log will always expand to its original size when restored.
SamJollyAuthor Commented:
Mistake on my part.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.