restore database

Hi,
I used to restore 40gig database by created an empty 40gig database then
restore data from device to the new database. It used to take us about 3.5
to 4 hours for the process. Today, one of my co-worker created 2meg database
with incremental growth, then retored data from device to the new database.
From what I read, the later process will be slower than the first.
Can anyone tell me how much slower? If it takes a whole day, I rather
restore the process by create a 40gig empty database then restore data to it
.
Any suggestion would greatly appreciated,
CulamThe actual restore process can be slower, it can be as fast, and it can even
be slightly faster. To
understand this, you need to understand how restore work in SQL Server.
If the existing database doesn't have the exact same file layout as the back
uped database had, then
SQL Server will delete the database first! And then create it and read the p
ages in the backup into
it.
So, is your 40GB database *has* the same layout, then SQL Server will just r
ead the pages into that
database. If it doesn't, then SQL Server will delete the database, create it
(!), and read the
pages.
Same goes for the small dest database, of course, where it will not have the
same layout (assuming
your originating database is larger than 2MB.
In general, do NOT create the database before doing the restore. Unless you
are aware of how restore
work, you will likely create a database which doesn't have the same file lay
out, resulting in the
RESTORE process will immediately delete the database and then create it prop
erly. And you can ask
yourself: "Why did I create it in the first place?"
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"culam" <culam@.discussions.microsoft.com> wrote in message
news:BCB4AB17-105E-45D3-8B5A-2CB1EAAB3C66@.microsoft.com...
> Hi,
> I used to restore 40gig database by created an empty 40gig database then
> restore data from device to the new database. It used to take us about 3.
5
> to 4 hours for the process. Today, one of my co-worker created 2meg databa
se
> with incremental growth, then retored data from device to the new database
.
> From what I read, the later process will be slower than the first.
> Can anyone tell me how much slower? If it takes a whole day, I rather
> restore the process by create a 40gig empty database then restore data to
it.
> Any suggestion would greatly appreciated,
> Culam|||You are correct about the db layout. I was assuming a
simple/same db layout with a single file since he is only
creating 2 MB database and does not know the process. I
did not want to confuse the person.
Also, the TOTAL restore time may not be accurate because
if I were to create a 40 GB shell db, it is going to take
a few minutes to create the shell and then add to that the
actual restore time. Again, assuming simple single file db.
DeeJay
>--Original Message--
>The actual restore process can be slower, it can be as
fast, and it can even be slightly faster. To
>understand this, you need to understand how restore work
in SQL Server.
>If the existing database doesn't have the exact same file
layout as the backuped database had, then
>SQL Server will delete the database first! And then
create it and read the pages in the backup into
>it.
>So, is your 40GB database *has* the same layout, then SQL
Server will just read the pages into that
>database. If it doesn't, then SQL Server will delete the
database, create it (!), and read the
>pages.
>Same goes for the small dest database, of course, where
it will not have the same layout (assuming
>your originating database is larger than 2MB.
>In general, do NOT create the database before doing the
restore. Unless you are aware of how restore
>work, you will likely create a database which doesn't
have the same file layout, resulting in the
>RESTORE process will immediately delete the database and
then create it properly. And you can ask
>yourself: "Why did I create it in the first place?"
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"culam" <culam@.discussions.microsoft.com> wrote in message
>news:BCB4AB17-105E-45D3-8B5A-2CB1EAAB3C66@.microsoft.com...
40gig database then[vbcol=seagreen]
to take us about 3.5[vbcol=seagreen]
created 2meg database[vbcol=seagreen]
to the new database.[vbcol=seagreen]
the first.[vbcol=seagreen]
whole day, I rather[vbcol=seagreen]
then restore data to it.[vbcol=seagreen]
>
>.
>|||> Also, the TOTAL restore time may not be accurate because
> if I were to create a 40 GB shell db, it is going to take
> a few minutes to create the shell and then add to that the
> actual restore time.
Yes. But if you don't create it first, then SQL server will create it for yo
u when you execute the
RESTORE command. And if you create it the incorrect way (for instance only m
ake it 2mb in size),
then SQL server will delete it, create it and then restore the pages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message news:4c1901c490e2$163fb8e0$a601280a@.ph
x.gbl...[vbcol=seagreen]
> You are correct about the db layout. I was assuming a
> simple/same db layout with a single file since he is only
> creating 2 MB database and does not know the process. I
> did not want to confuse the person.
> Also, the TOTAL restore time may not be accurate because
> if I were to create a 40 GB shell db, it is going to take
> a few minutes to create the shell and then add to that the
> actual restore time. Again, assuming simple single file db.
> DeeJay
> fast, and it can even be slightly faster. To
> in SQL Server.
> layout as the backuped database had, then
> create it and read the pages in the backup into
> Server will just read the pages into that
> database, create it (!), and read the
> it will not have the same layout (assuming
> restore. Unless you are aware of how restore
> have the same file layout, resulting in the
> then create it properly. And you can ask
> 40gig database then
> to take us about 3.5
> created 2meg database
> to the new database.
> the first.
> whole day, I rather
> then restore data to it.|||I concur.

>--Original Message--
take[vbcol=seagreen]
the[vbcol=seagreen]
>Yes. But if you don't create it first, then SQL server
will create it for you when you execute the
>RESTORE command. And if you create it the incorrect way
(for instance only make it 2mb in size),
>then SQL server will delete it, create it and then
restore the pages.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
news:4c1901c490e2$163fb8e0$a601280a@.phx.gbl...
only[vbcol=seagreen]
take[vbcol=seagreen]
the[vbcol=seagreen]
db.[vbcol=seagreen]
work[vbcol=seagreen]
file[vbcol=seagreen]
SQL[vbcol=seagreen]
the[vbcol=seagreen]
and[vbcol=seagreen]
message[vbcol=seagreen]
2CB1EAAB3C66@.microsoft.com...[vbcol=seagreen]
used[vbcol=seagreen]
worker[vbcol=seagreen]
device[vbcol=seagreen]
than[vbcol=seagreen]
>
>.
>|||Thank you for the lesson.
I took about four hours to restore 40 gigs database on:
Intel Pentium III Xeon
4 processors
2gig of RAM.
"culam" wrote:

> Hi,
> I used to restore 40gig database by created an empty 40gig database then
> restore data from device to the new database. It used to take us about 3.
5
> to 4 hours for the process. Today, one of my co-worker created 2meg databa
se
> with incremental growth, then retored data from device to the new database
.
> From what I read, the later process will be slower than the first.
> Can anyone tell me how much slower? If it takes a whole day, I rather
> restore the process by create a 40gig empty database then restore data to
it.
> Any suggestion would greatly appreciated,
> Culam