Microsoft SQL Server forum - Still need help creating a DB by RESTORE

Author

Still need help creating a DB by RESTORE - problems

EggsAckley@Last.com

2005-05-26, 8:24 pm

Hi:

I still need help with this. I tried the following command in Query
Analyzer:

RESTORE DATABASE x
from y
with NORECOVERY,
MOVE 'x_dat' to "C:/***/mssql/data/x_dat1.mdf;

I make no reference to the log file 'x_log' which is also present in
the backup file, as demonstrated by the results of RESTORE
FILELISTONLY.

It seems content with the data, but then it complains about the
logfile. Of course if I specify a location for the log, it complains
that there is insufficient space on the disk (which is true, it wants
31 GB).

Is there a way to restore only the data portion? Or is there another
way to create a DB from a backup file, when the DB doesn't already
exist on my server?

I very much appreciate anyone's help with this.

Eggs

On Wed, 25 May 2005 18:07:55 -0400, EggsAckley@Last.com wrote:

>Hi:
>
>I have a file that I have been told is a SQL Server backup from a
>server somewhere. The file is about 200MB in size
>
>I am trying to create the database on my local server using RESTORE. I
>created the backup device, associated it with a backup name etc.,
>copied the file into the backup dir.
>
>When I run the RESTORE command, Query Analyzer tells me the database
>needs 31 GB of space and the RESTORE aborts. I've tried this several
>times, get the same result every time.
>
>Anybody ever seen anything like this? Is there another way to create a
>DB in a server using a backup file?
>
>I am running SqlServer 2000, Developer edition on a machine running
>Windows Server 2003 OS.
>
>I would appreciate any help/suggestions.
>
>Waz

Erland Sommarskog

2005-05-26, 8:24 pm

(EggsAckley@Last.com) writes:
> I still need help with this. I tried the following command in Query
> Analyzer:
>
> RESTORE DATABASE x
> from y
> with NORECOVERY,
> MOVE 'x_dat' to "C:/***/mssql/data/x_dat1.mdf;
>
> I make no reference to the log file 'x_log' which is also present in
> the backup file, as demonstrated by the results of RESTORE
> FILELISTONLY.
>
> It seems content with the data, but then it complains about the
> logfile. Of course if I specify a location for the log, it complains
> that there is insufficient space on the disk (which is true, it wants
> 31 GB).
>
> Is there a way to restore only the data portion? Or is there another
> way to create a DB from a backup file, when the DB doesn't already
> exist on my server?
>
> I very much appreciate anyone's help with this.

The by far easiest solution in this case, is to add a new disk to the
machine. Look at the price for a 40 GB disk and compare with what you
cost your employer/client per hour. You don't have much time to look
for shortcut solution, before your work is more expensive than the disk.

If this happens on a machine which require special expensive disks
on some sort, just find another computer where you can restore,
shrink the log, and then move the database to where you want it.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Sponsored Links