Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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
?
373 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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 

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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

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…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

580 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