Restore script does not seem to work

Hello,
I am trying to restore a 1GB database by using the script below:
alter database [auto] set single_user
go
restore database [auto] from disk = 'F:\Temp\Restore\auto_db'
with norecovery,
move 'auto_dat' to 'F:\SQLDATA\MSSQL\Data\auto.mdf',
move 'auto_log' to 'F:\SQLDATA\MSSQL\Data\auto.ldf',
stats = percentage
go
I added the stats line because it ran for over 2 hours.
I let this run for about half an hour, but it never showed any statistics.
The performance monitor shows 345 MB of memory used and mostly 0 % CPU,
going up to 2 % every few seconds.
The machine is a Pentiium 4 2.8 GHz with 512 MB RAM running 2003 Server
Standard, and there is 20 GB of space on F.
I have restored the same DB by using the Enterprise Manager and I think it
took 5 minutes.
I want to write a script to do this to simplify the process, since this is a
backup server and I am trying to make the job easier for whoever end up with
the job of restoring.
Any ideas why this is happening? I have triple checked the folder structures
and the names.
Any help would be much appreciated.
RagnarThe problem is the stats thing ie
restore database [auto] from disk = 'F:\Temp\Restore\auto_db'
> with norecovery,
> move 'auto_dat' to 'F:\SQLDATA\MSSQL\Data\auto.mdf',
> move 'auto_log' to 'F:\SQLDATA\MSSQL\Data\auto.ldf',
> stats = 5
> go
THis means that SQL should show stats after each 5% has completed...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:ufjA2LqdFHA.3012@.tk2msftngp13.phx.gbl...
> Hello,
> I am trying to restore a 1GB database by using the script below:
> alter database [auto] set single_user
> go
> restore database [auto] from disk = 'F:\Temp\Restore\auto_db'
> with norecovery,
> move 'auto_dat' to 'F:\SQLDATA\MSSQL\Data\auto.mdf',
> move 'auto_log' to 'F:\SQLDATA\MSSQL\Data\auto.ldf',
> stats = percentage
> go
> I added the stats line because it ran for over 2 hours.
> I let this run for about half an hour, but it never showed any statistics.
> The performance monitor shows 345 MB of memory used and mostly 0 % CPU,
> going up to 2 % every few seconds.
> The machine is a Pentiium 4 2.8 GHz with 512 MB RAM running 2003 Server
> Standard, and there is 20 GB of space on F.
> I have restored the same DB by using the Enterprise Manager and I think it
> took 5 minutes.
> I want to write a script to do this to simplify the process, since this is
> a backup server and I am trying to make the job easier for whoever end up
> with the job of restoring.
> Any ideas why this is happening? I have triple checked the folder
> structures and the names.
> Any help would be much appreciated.
> Ragnar
>|||Thank you Wayne,
It turns out that there was something wrong with the server, our IS guys
eventually had to recycle it.
Once I fixed the STATS statement it ran in 202 seconds.
But I was puzzled about the stats display, nothing showed up until the job
finished!
I had used this before, I just copied some sample code, and as far as I can
remember it would print a line whenever a certain percentage had been
processed.
Ragnar
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23ZzzIlqdFHA.2288@.TK2MSFTNGP14.phx.gbl...
> The problem is the stats thing ie
> restore database [auto] from disk = 'F:\Temp\Restore\auto_db'
>> with norecovery,
>> move 'auto_dat' to 'F:\SQLDATA\MSSQL\Data\auto.mdf',
>> move 'auto_log' to 'F:\SQLDATA\MSSQL\Data\auto.ldf',
>> stats = 5
>> go
> THis means that SQL should show stats after each 5% has completed...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
> news:ufjA2LqdFHA.3012@.tk2msftngp13.phx.gbl...
>> Hello,
>> I am trying to restore a 1GB database by using the script below:
>> alter database [auto] set single_user
>> go
>> restore database [auto] from disk = 'F:\Temp\Restore\auto_db'
>> with norecovery,
>> move 'auto_dat' to 'F:\SQLDATA\MSSQL\Data\auto.mdf',
>> move 'auto_log' to 'F:\SQLDATA\MSSQL\Data\auto.ldf',
>> stats = percentage
>> go
>> I added the stats line because it ran for over 2 hours.
>> I let this run for about half an hour, but it never showed any
>> statistics.
>> The performance monitor shows 345 MB of memory used and mostly 0 % CPU,
>> going up to 2 % every few seconds.
>> The machine is a Pentiium 4 2.8 GHz with 512 MB RAM running 2003 Server
>> Standard, and there is 20 GB of space on F.
>> I have restored the same DB by using the Enterprise Manager and I think
>> it took 5 minutes.
>> I want to write a script to do this to simplify the process, since this
>> is a backup server and I am trying to make the job easier for whoever end
>> up with the job of restoring.
>> Any ideas why this is happening? I have triple checked the folder
>> structures and the names.
>> Any help would be much appreciated.
>> Ragnar
>

Restore runs longer in batch mode

I have created a Job that uses "cmcexec" to execute a SQL script. This Job
brings SQL to single_user mode then restores from a directory that is local
to the server.
1) When I run this restore in the GUI it takes 15 minutes but is still
executing
(1 + hours).
2) How can I tell that it is actually running and not hung up?
Hi,
From Query Analyzer, Could you execute the syetm procedure sp_who2 and see
the status of the backup process.
Repeatedly execute sp2 and check if the CPU time and DISK IO are increasing
for backup process. If the value is increasing
then the process is active.
Thanks
Hari
MCDBA
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:E13E54D2-C77F-4A44-BDAB-630166185032@.microsoft.com...
> I have created a Job that uses "cmcexec" to execute a SQL script. This
Job
> brings SQL to single_user mode then restores from a directory that is
local
> to the server.
> 1) When I run this restore in the GUI it takes 15 minutes but is still
> executing
> (1 + hours).
> 2) How can I tell that it is actually running and not hung up?