Creating a DB by restore - problems

[EggsAckley]

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

[Dan Guzman]

A restored database is the same size as the original database so you'll need
that amount of free space for the restore. You can determine the amount of
space required with RESTORE FILELISTONLY. For example:

RESTORE FILELISTONLY
FROM DISK='C:\MyBack upFile.bak'

Note that the backup file may be considerably smaller since unused data
pages are not backed up.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<Eg********@Las t.com> wrote in message
news:mb******** *************** *********@4ax.c om...

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

[EggsAckley]

On Wed, 25 May 2005 23:55:55 GMT, "Dan Guzman"
<gu******@nospa m-online.sbcgloba l.net> wrote:

A restored database is the same size as the original database so you'll need
that amount of free space for the restore. You can determine the amount of
space required with RESTORE FILELISTONLY. For example:

RESTORE FILELISTONLY
FROM DISK='C:\MyBack upFile.bak'

Note that the backup file may be considerably smaller since unused data
pages are not backed up.

Dan:

Thanks for your response. RESTORE FILELISTONLY says the data file
needs 1.2 gig but the log file needs 31 gig. How can I restore the
data without the log, or can I?

Thanks, Waz

[Greg D. Moore \(Strider\)]

<Eg********@Las t.com> wrote in message
news:mb******** *************** *********@4ax.c om...

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?
Sounds like they may have had a huge database that only contained 200 MB of
data.

Other than havnig 31 GB free, not sure what you can do.

Can you post the results of a RESTORE FILEHEADERSONLY command?


I am running SqlServer 2000, Developer edition on a machine running
Windows Server 2003 OS.

I would appreciate any help/suggestions.

Waz

[EggsAckley]

On Thu, 26 May 2005 01:21:58 GMT, "Greg D. Moore \(Strider\)"
<mo************ ****@greenms.co m> wrote:

Sounds like they may have had a huge database that only contained 200 MB of
data.

Other than havnig 31 GB free, not sure what you can do.

Can you post the results of a RESTORE FILEHEADERSONLY command?

BackupName
BackupDescripti on
BackupType ExpirationDate
Compressed Position DeviceType UserName
ServerName
DatabaseName
DatabaseVersion DatabaseCreatio nDate
BackupSize FirstLsn LastLsn
CheckpointLsn DifferentialBas eLsn
BackupStartDate
BackupFinishDat e SortOrder
CodePage UnicodeLocaleId UnicodeComparis onStyle CompatibilityLe vel
SoftwareVendorI d SoftwareVersion Major SoftwareVersion Minor
SoftwareVersion Build MachineName
Flags BindingId RecoveryForkId
Collation
---------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------- ------------------------------------------------------
---------- -------- ----------
---------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------
--------------- ------------------------------------------------------
---------------------- ---------------------------
--------------------------- ---------------------------
---------------------------
------------------------------------------------------
------------------------------------------------------ ---------
-------- --------------- ---------------------- ------------------
---------------- -------------------- --------------------
--------------------
---------------------------------------------------------------------------------------------------------------------------------
----------- --------------------------------------
--------------------------------------
---------------------------------------------------------------------------------------------------------------------------------
Scheduled xx Test backup
Scheduled xx Test Backup
1 NULL 0
1 102 TESTSERVER\Admi nistrator
TESTSERVER
xx Test
539 2003-08-06 17:38:53.000
206685184 359700002331020 0001 359700002331110 0001
359700002331020 0003 359600000748930 0003 2004-01-12
18:55:39.000 2004-01-12 18:56:14.000
52 228 1033 196609 80
4608 8 0 194
TESTSERVER
0 {007ED5AD-104E-452D-xxxx-512A2B3C700A}
{43356D09-0597-4AD4-xxxx-6D38CAD81F5D} SQL_Latin1_Gene ral_CP1_CI_AS

(1 row(s) affected)

Waz

[Greg D. Moore \(Strider\)]

<Eg********@Las t.com> wrote in message
news:tj******** *************** *********@4ax.c om...

On Thu, 26 May 2005 01:21:58 GMT, "Greg D. Moore \(Strider\)"
<mo************ ****@greenms.co m> wrote:

Sounds like they may have had a huge database that only contained 200 MB ofdata.

Other than havnig 31 GB free, not sure what you can do.

Can you post the results of a RESTORE FILEHEADERSONLY command?

Thanks. unfortunately I meant FILELISTONLY as Dan had correctly said.

And no, unfortunately I don't know of anyway to restore a database w/o the
log file.

[EggsAckley]

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, Eg********@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]

(Eg********@Las t.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, es****@sommarsk og.se

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

[EggsAckley]

On Thu, 26 May 2005 22:24:20 +0000 (UTC), Erland Sommarskog
<es****@sommars kog.se> wrote:

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.

Thanks very much for the reality check. I used my USB external drive
and was able to do the restore in a straightforward manner. The
initial stumbling point was that I couldn't see the USB drive when I
installed it under Windows 2003 Server, then I realized W2003Svr
requires you to manually assign drive letters, so I did.

Can you (or anyone) suggest general guidelines for improving the
performance and/or efficiency of stored procedures under SqlServer
2000? Again, I very much appreciate any guidance.

Eggs

[Erland Sommarskog]

(Eg********@Las t.com) writes:

Can you (or anyone) suggest general guidelines for improving the
performance and/or efficiency of stored procedures under SqlServer
2000? Again, I very much appreciate any guidance.

The question is a bit open-ended. But here are some general points
from the top of my head:

o Try as much as possible to avoid iterative solution, and use set-
based solutions.

o When using temp tables, create them as the first executable statement
in the procedure. (DECLARE @local is not an executable statement.) If
you create temp tables in the middle of it all, you will get a
recompile.

o Share your graces between temp tables and table variables. Sometimes
ons is right, and sometimes the other. My general suggestion is that
you start with a temp table, but if you find that you get performance
problems because of recompiles, switch to temp tables. (Keep in mind
that those recompiles can just as well be life-savers!)

o And while it's sometimes it's a good idea to keep a temp table/table
variable for storage of intermediate results, it can also sometimes
be more effecient with one big query from hell that does it all in
one statement.

o Don't do this:
CREATE PROCEDURE some_sp ... @startdate = NULL, .... AS
IF @startdate IS NULL
SELECT @startdate = convert(char(8) , getdate(), 112)
since SQL Server sniffs the parameter value, it will build query
plans assuming that @startdate is NULL. It's better to copy to
a local variable, of which SQL Server makes no assumption at all
about the value. Even more effecient is to move processing to an
inner procedure once all defaults have been filled in.

o Microsoft recommends that you always use two-part notation, for
instance "SELECT ... FROM dbl.tbl" and not "SELECT ... FROM tbl",
and claims this is more effecient. I claim that if the procedure
is owned by dbo, there should be no difference, and if there is,
that's a bug. One of these days, I will have to benchmark it.
Anyway, since MS recommends it, I thought I should mention it.

If you have particular issues you want to dicsuss, you are welcome.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

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