Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Database

Posted on 2013-05-31
13
Medium Priority
?
298 Views
Last Modified: 2013-06-20
Hi All,

I am trying to restore a database to a server from a SQL backup.  The .ldf file is about 160 gb and database is about 3 gb.  I am unable to truncate the logs, etc online as it says it does not have enough free space when restoring it.  Is there a way to truncate the log from the backup or somehow get it restored to a new SQL server?  Also, the size of drive I am restoring to has 900 GB free.
0
Comment
Question by:Jack_son_
[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
13 Comments
 
LVL 23

Assisted Solution

by:nemws1
nemws1 earned 800 total points
ID: 39211659
Are you sure it's restoring the LDF file to the partition that has 900 gigs free (and not some other partition, like C: where it shouldn't be restoring it).

Double-check "Restore as" locations in the restore dialog box Options screen.

Unfortunately, you can't shrink the log file as it restores.  You'll need to restore the database, shink the log file (we are talking about a non-production system here, right?) and then back it up again (turn on compression!) to get a smaller backup file.
MSSQL Restore dialog box with restore locations highlighted
0
 
LVL 7

Assisted Solution

by:Robert Saylor
Robert Saylor earned 400 total points
ID: 39211708
This works on MySQL but not sure about MS SQL. If you have 1 database create the same database on the new server then stop SQL on both servers. Physically copy the database files from one server to another. Start SQL.

I would try this as a last resort. My experience is in MySQL and not MS SQL.
0
 
LVL 8

Assisted Solution

by:didnthaveaname
didnthaveaname earned 400 total points
ID: 39211885
As a sidebar to what nemws1 said, if you have a 3GB database with a 160GB transaction log, I would encourage you to consider if you really need the full recovery model for your transaction logs (I am assuming you're currently in full recovery model with a 3GB database and transaction logs that are 53 times the size of the database).  

A good link on the differences between full/simple/bulk logged recovery models is: http://msdn.microsoft.com/en-us/library/ms189275.aspx.  What it boils down to, honestly, is if you need point in time restore capabilities.  

If you do, you'll want to set up a maintenance plan/sql server agent job to back up your transaction logs much more frequently (largely dependent on the size of the logs and how frequently the data changes) in conjunction with full backups (see here: http://msdn.microsoft.com/en-us/library/ms190217%28v=sql.105%29.aspx).

Otherwise, move to simple recovery model as the transaction log is more or less useless to you from a backup/restore perspective (you would only have full/differential backups to worry about under this model).

edit: forgot to remove something
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 23

Assisted Solution

by:nemws1
nemws1 earned 800 total points
ID: 39211889
Great suggestion didnthaveaname.  Also, if you *are* going to go with the FULL recovery model, you just need to set up agent jobs to do FULL and LOG backups.  Do a quick search for "Ola Hallengren's Maintenance Script" for a great free solution.  Doing this will keep your log file size down as well (after you shrink it, that is).
0
 

Author Comment

by:Jack_son_
ID: 39213517
Thanks, is there a way I can just import the database only without the log files?  Just for testing
0
 
LVL 23

Expert Comment

by:nemws1
ID: 39213531
Not from a backup, no. ;-(

Did you check that you were restoring to the correct drives?
0
 
LVL 3

Expert Comment

by:expert_dharam
ID: 39213544
Just attach the MDF file.. New log file will be created automatically...
0
 

Author Comment

by:Jack_son_
ID: 39213618
Ok, that is what I am testing, but says log file is missing?
0
 
LVL 23

Expert Comment

by:nemws1
ID: 39213659
Your original question stated that all you had was a backup file.  Not an MDF/LDF combo.

Which is it?  What do you have and what are you trying to do?
0
 

Author Comment

by:Jack_son_
ID: 39213676
Have both.  Issue is I don't have enough space to restore with the .bak file.  I am trying another direction restoring with a copy of the db.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39213692
Since the Transaction Log is an integral part of the database the suggestion that "Just attach the MDF file.. New log file will be created automatically..." is suspect at best.

If you cannot switch to Simple Recovery Model and shrink the Transaction Log where it is at or find enough disk space to restore the backup you are quite simply SOL.
0
 
LVL 23

Expert Comment

by:nemws1
ID: 39213701
If you have the MDF and LDF files, have to tried to attach them?  (in SSMS, connect to server, then right click the server and select 'attach' - select the MDF and LDF files).

Once attached you can shrink them.

Also, you stated you had *plenty* of space to restore the database from backup (many gigs).  You should still be able to restore from backup.
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 400 total points
ID: 39214337
If you have the mdf file, you can do an a single file attach.

Something like this:
CREATE DATABASE [MyDBName]
    ON 
        NAME = logical_file_name ,
    FILENAME = 'E:\Path\MyDBName.mdf'
    FOR ATTACH_REBUILD_LOG

Open in new window

0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
What we learned in Webroot's webinar on multi-vector protection.
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
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

721 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