Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2012-04-03
8
Medium Priority
?
372 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 1600 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 
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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 400 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

886 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