Solved

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

Posted on 2012-04-03
8
367 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 

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 69

Assisted Solution

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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

729 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