How long a does restore take

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

NC3 - 27 Mar 2008 16:05 GMT

I have a 52 GB database that take 3 hours to restore using SQL Server
native restore. Does this seem long to anyone who has experience with
databses of this size? I know each insatalltion is different so things
like this can vary.

The hardware specs don't seem to matter much because I've restored the
database on hardware with different specs but the restore times are
consistent. The database is a publisher in a trasnactional replciation
topology and I don't know if that matters or not.

the hardware is our environment for SQL is generally the same dual 2/8
Ghz with 4 to 8 gb or RAM. We do use SATA drives which I understand
are slower drives but I'm not the sys admin guru.

any insught is appreciated.

Reply to this Message

Davy Shipley - 27 Mar 2008 16:14 GMT

I use SQLLitespeed to backup and restore.
I can backup a 80Gb database to a 21Gb compressed backup in approximately 25
minutes.
I can do the restore within 45 minutes.

>I have a 52 GB database that take 3 hours to restore using SQL Server
> native restore. Does this seem long to anyone who has experience with
[quoted text clipped - 11 lines]
>
> any insught is appreciated.

Reply to this Message

Linchi Shea - 27 Mar 2008 16:41 GMT

> The hardware specs don't seem to matter much because I've restored the
> database on hardware with different specs but the restore times are
> consistent.

Hardware specs, especially the storage specs, do matter! Given a decent
RAID, I'd say your restore time is much too long, and I'd take a close look
at how the storage subsystem is doing during the restore.

Linchi

> I have a 52 GB database that take 3 hours to restore using SQL Server
> native restore. Does this seem long to anyone who has experience with
[quoted text clipped - 11 lines]
>
> any insught is appreciated.

Reply to this Message

NC3 - 27 Mar 2008 16:54 GMT

On Mar 27, 11:41 am, Linchi Shea
<LinchiS...@discussions.microsoft.com> wrote:

> > The hardware specs don't seem to matter much because I've restored the
> > database on hardware with different specs but the restore times are
[quoted text clipped - 23 lines]
>
> - Show quoted text -

How can I monitor the subsystem during a restore? I'd like to know as
well

Reply to this Message

Tom Cooper - 27 Mar 2008 17:32 GMT

You don't mention which release of SQL you are on. If you are on SQL 2005
and a version of Windows that makes "Instant Initialization" available, you
might want to consider using it. Kimberly Tripp has a good discussion of
this feature (including why you might not want to use it) at
http://www.sqlskills.com/blogs/kimberly/2007/03/04/InstantInitializationWhatWhyA
ndHow.aspx

Tom

Reply to this Message

NC3 - 27 Mar 2008 18:37 GMT

On Mar 27, 12:32 pm, "Tom Cooper"
<tomcoo...@comcast.no.spam.please.net> wrote:

> You don't mention which release of SQL you are on. If you are on SQL 2005
> and a version of Windows that makes "Instant Initialization" available, you
[quoted text clipped - 20 lines]
>
> - Show quoted text -

I'm using SQL 2005 sp2 on Windows 2003 sp1. Thanks for the reference
I'll be sure to give it a read.

Reply to this Message

Henrik Davidsen - 27 Mar 2008 21:24 GMT

>I have a 52 GB database that take 3 hours to restore using SQL Server
> native restore.

The size of 52 GB, is that the size of the backup file? When you have a 52
GB bacup file, that means, that the backup contains 52 GB of data. The
database consists of 1-many datafiles, and 1-many logfiles. Lets say, that
your database only has 1 datafile, and 1 logfile. If the size of the
datafile is 60 GB (have 8 gigs of available space in the database), and the
logfile is 10 gigs, then the restore actually has to write the 52 gigs of
data to the datafile, and write 10 gigs of zeros to the logfile (see Tom
Coopers posting, referring to "Instant initializing" and Kimberly Tripp's
article).
That gives at least 62 GB writes, and 52 GB reads (it neads to read from the
backup file, and write to the data file).

> Does this seem long to anyone who has experience with
> databses of this size? I know each insatalltion is different so things
> like this can vary.

That would depend. Se below.

> the hardware is our environment for SQL is generally the same dual 2/8
> Ghz with 4 to 8 gb or RAM. We do use SATA drives which I understand
> are slower drives but I'm not the sys admin guru.

I would say, that the disk subsystem really matters. You say you use SATA
drives. But how many? If you only have 2 disks in a RAID 1, then 52 GB
reads, and 60+ GB writes WILL take some time.

So, how many disks do you have, and how are the configured in RAID?

We have a database with 70 GB data, with a logfile of 50 GB. Our restore
takes about 25 minutes, having a disk subsystem with about 20 disks in
total.

/Sjang

Reply to this Message