MS SQL Server - Restore Issue

Author

Restore Issue

Jerry Spivey

2005-07-29, 8:23 pm

Hi,

I'm testing out the restore strategy for a 34GB database. The backup for
the database was taken from a Windows 2000 SP4 SQL Server 2000 SP4 system
via a maintenance plan. I copied the .bak file to another server Windows
2003 Ent SP1 SQL Server 2000 SP4 to perform the restore. I have used the
MOVE TO option via the graphical interface in EM to perform the restore.
The restore starts correctly but after one hour the progress bar has not
moved at all. This occurs with two different .bak files I have tried. If I
look in the destination directory I do see the .mdf file. I also see disk
activity for the RAID5 drives. How long should I expect this restore
process to take? Should I have seen some progress on the progress bar by
now? System is dual proc HP Proliant 380DL. If I hit stop the process
stops the .mdf file goes away.

Thanks

Jerry

ChrisR

2005-07-29, 8:23 pm

I'd say to use Query Analyzer for your restore. Make sure to use the "stats"
option.

"Jerry Spivey" <jspivey@vestas-awt.com> wrote in message
news:eaiEftIlFHA.1412@TK2MSFTNGP09.phx.gbl...
> Hi,
>
> I'm testing out the restore strategy for a 34GB database. The backup for
> the database was taken from a Windows 2000 SP4 SQL Server 2000 SP4 system
> via a maintenance plan. I copied the .bak file to another server Windows
> 2003 Ent SP1 SQL Server 2000 SP4 to perform the restore. I have used the
> MOVE TO option via the graphical interface in EM to perform the restore.
> The restore starts correctly but after one hour the progress bar has not
> moved at all. This occurs with two different .bak files I have tried. If
> I look in the destination directory I do see the .mdf file. I also see
> disk activity for the RAID5 drives. How long should I expect this restore
> process to take? Should I have seen some progress on the progress bar by
> now? System is dual proc HP Proliant 380DL. If I hit stop the process
> stops the .mdf file goes away.
>
> Thanks
>
> Jerry
>

ChrisR

2005-07-29, 8:23 pm

Also, if memory servers correctly your going to need to be on the same SP
anyways.
"Jerry Spivey" <jspivey@vestas-awt.com> wrote in message
news:eaiEftIlFHA.1412@TK2MSFTNGP09.phx.gbl...
> Hi,
>
> I'm testing out the restore strategy for a 34GB database. The backup for
> the database was taken from a Windows 2000 SP4 SQL Server 2000 SP4 system
> via a maintenance plan. I copied the .bak file to another server Windows
> 2003 Ent SP1 SQL Server 2000 SP4 to perform the restore. I have used the
> MOVE TO option via the graphical interface in EM to perform the restore.
> The restore starts correctly but after one hour the progress bar has not
> moved at all. This occurs with two different .bak files I have tried. If
> I look in the destination directory I do see the .mdf file. I also see
> disk activity for the RAID5 drives. How long should I expect this restore
> process to take? Should I have seen some progress on the progress bar by
> now? System is dual proc HP Proliant 380DL. If I hit stop the process
> stops the .mdf file goes away.
>
> Thanks
>
> Jerry
>

Jerry Spivey

2005-07-29, 8:23 pm

Thanks Chris.

Yes bypassing the EM tool and using the native RESTORE stament in QA was the
next thing to try however I am acutally seeing the .ldf file and I am now
starting to get some progress on the progress bar 1-hour into this. Must
just be the size of this database. Its just taking longer than I expected
it would.

Jerry

"ChrisR" <noemail@bla.com> wrote in message
news:uGxSjzIlFHA.3380@TK2MSFTNGP12.phx.gbl...
> Also, if memory servers correctly your going to need to be on the same SP
> anyways.
> "Jerry Spivey" <jspivey@vestas-awt.com> wrote in message
> news:eaiEftIlFHA.1412@TK2MSFTNGP09.phx.gbl...
>
>

ChrisR

2005-07-29, 8:23 pm

That being said, I think you will probably see it jump several bars at once
from time to time.

"Jerry Spivey" <jspivey@vestas-awt.com> wrote in message
news:uQGHE6IlFHA.3936@TK2MSFTNGP10.phx.gbl...
> Thanks Chris.
>
> Yes bypassing the EM tool and using the native RESTORE stament in QA was
> the next thing to try however I am acutally seeing the .ldf file and I am
> now starting to get some progress on the progress bar 1-hour into this.
> Must just be the size of this database. Its just taking longer than I
> expected it would.
>
> Jerry
>
>
> "ChrisR" <noemail@bla.com> wrote in message
> news:uGxSjzIlFHA.3380@TK2MSFTNGP12.phx.gbl...
>
>

Mike Epprecht \(SQL MVP\)

2005-07-29, 8:23 pm

Hi

Before SQL Server can start to restore the DB, it needs to create the DB
(MDF and LDF files) to look exactly the same like the DB the backup came
from (same size)

So, it has to Initialize at least 34GB of disk space. You will see a lot of
write activity, and once the restore actually starts to place the data in
the file, it goes quite quickly.

Depending on your drives, it can take many hours. Having dives optimally
configured help a lot too. RAID-5 can never touch the performance of RAID-10
or RAID-1.

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"Jerry Spivey" <jspivey@vestas-awt.com> wrote in message
news:eaiEftIlFHA.1412@TK2MSFTNGP09.phx.gbl...
> Hi,
>
> I'm testing out the restore strategy for a 34GB database. The backup for
> the database was taken from a Windows 2000 SP4 SQL Server 2000 SP4 system
> via a maintenance plan. I copied the .bak file to another server Windows
> 2003 Ent SP1 SQL Server 2000 SP4 to perform the restore. I have used the
> MOVE TO option via the graphical interface in EM to perform the restore.
> The restore starts correctly but after one hour the progress bar has not
> moved at all. This occurs with two different .bak files I have tried. If
> I look in the destination directory I do see the .mdf file. I also see
> disk activity for the RAID5 drives. How long should I expect this restore
> process to take? Should I have seen some progress on the progress bar by
> now? System is dual proc HP Proliant 380DL. If I hit stop the process
> stops the .mdf file goes away.
>
> Thanks
>
> Jerry
>

Jerry Spivey

2005-07-29, 8:23 pm

Thanks Mike.

"Mike Epprecht (SQL MVP)" <mike@epprecht.net> wrote in message
news:uthoPUJlFHA.2644@TK2MSFTNGP09.phx.gbl...
> Hi
>
> Before SQL Server can start to restore the DB, it needs to create the DB
> (MDF and LDF files) to look exactly the same like the DB the backup came
> from (same size)
>
> So, it has to Initialize at least 34GB of disk space. You will see a lot
> of write activity, and once the restore actually starts to place the data
> in the file, it goes quite quickly.
>
> Depending on your drives, it can take many hours. Having dives optimally
> configured help a lot too. RAID-5 can never touch the performance of
> RAID-10 or RAID-1.
>
> Regards
> --------------------------------
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
>
> IM: mike@epprecht.net
>
> MVP Program: http://www.microsoft.com/mvp
>
> Blog: http://www.msmvps.com/epprecht/
>
> "Jerry Spivey" <jspivey@vestas-awt.com> wrote in message
> news:eaiEftIlFHA.1412@TK2MSFTNGP09.phx.gbl...
>
>

Sponsored Links