Solved

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

Posted on 2012-04-03
8
356 Views
Last Modified: 2012-06-22
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
0
Comment
Question by:SamJolly
  • 4
  • 3
8 Comments
 
LVL 13

Accepted Solution

by:
dwkor earned 400 total points
ID: 37802463
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.
0
 

Author Comment

by:SamJolly
ID: 37802597
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
0
 
LVL 13

Expert Comment

by:dwkor
ID: 37802737
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.
0
 

Author Comment

by:SamJolly
ID: 37802918
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
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 13

Expert Comment

by:dwkor
ID: 37802951
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 :)
0
 

Author Comment

by:SamJolly
ID: 37803261
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
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 100 total points
ID: 37803395
>> 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.
0
 

Author Comment

by:SamJolly
ID: 37863282
Mistake on my part.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now