Model DB is hung on a restore ??

Is there a way to stop a hung restore ?
Here are some error logs:
2003-12-09 12:35:20.94 server Microsoft SQL Server 2000 - 8.00.760 (Intel
X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
2003-12-09 12:35:20.94 server Copyright (C) 1988-2002 Microsoft Corporation.
2003-12-09 12:35:20.94 server All rights reserved.
2003-12-09 12:35:20.94 server Server Process ID is 3984.
2003-12-09 12:35:20.94 server Logging SQL Server messages in file
'e:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
2003-12-09 12:35:20.96 server SQL Server is starting at priority class
'normal'(4 CPUs detected).
2003-12-09 12:35:21.10 server SQL Server configured for thread mode
processing.
2003-12-09 12:35:21.10 server Using dynamic lock allocation. [2500] Lock
Blocks, [5000] Lock Owner Blocks.
2003-12-09 12:35:21.17 server Attempting to initialize Distributed
Transaction Coordinator.
2003-12-09 12:35:23.25 spid3 Starting up database 'master'.
2003-12-09 12:35:23.32 server Using 'SSNETLIB.DLL' version '8.0.760'.
2003-12-09 12:35:23.32 spid5 Starting up database 'model'.
2003-12-09 12:35:23.32 spid3 Server name is 'W2KSVR'.
2003-12-09 12:35:23.32 spid8 Starting up database 'msdb'.
2003-12-09 12:35:23.32 spid9 Starting up database 'pubs'.
2003-12-09 12:35:23.32 spid10 Starting up database 'Northwind'.
2003-12-09 12:35:23.32 spid11 Starting up database 'StarBldr'.
2003-12-09 12:35:23.32 spid12 Starting up database 'SurfControl_WebFilter'.
2003-12-09 12:35:23.32 spid13 Starting up database 'BRCS'.
2003-12-09 12:35:23.32 spid14 Starting up database 'BEDB'.
2003-12-09 12:35:23.33 spid11 Bypassing recovery for database 'StarBldr'
because it is marked IN LOAD.
2003-12-09 12:35:23.33 spid5 Bypassing recovery for database 'model' because
it is marked IN LOAD.
2003-12-09 12:35:23.33 spid5 Database 'model' cannot be opened. It is in the
middle of a restore.
2003-12-09 12:35:23.33 spid13 Bypassing recovery for database 'BRCS' because
it is marked IN LOAD.
HERE IS THE ERRORLOG.1 file:
2003-12-09 12:33:19.02 server Microsoft SQL Server 2000 - 8.00.760 (Intel
X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
2003-12-09 12:33:19.02 server Copyright (C) 1988-2002 Microsoft Corporation.
2003-12-09 12:33:19.02 server All rights reserved.
2003-12-09 12:33:19.02 server Server Process ID is 4364.
2003-12-09 12:33:19.02 server Logging SQL Server messages in file
'e:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
2003-12-09 12:33:19.02 server SQL Server is starting at priority class
'normal'(4 CPUs detected).
2003-12-09 12:33:19.16 server SQL Server configured for thread mode
processing.
2003-12-09 12:33:19.16 server Using dynamic lock allocation. [2500] Lock
Blocks, [5000] Lock Owner Blocks.
2003-12-09 12:33:19.24 server Attempting to initialize Distributed
Transaction Coordinator.
2003-12-09 12:33:21.30 spid3 Starting up database 'master'.
2003-12-09 12:33:21.35 server Using 'SSNETLIB.DLL' version '8.0.760'.
2003-12-09 12:33:21.35 spid5 Starting up database 'model'.
2003-12-09 12:33:21.36 spid3 Server name is 'W2KSVR'.
2003-12-09 12:33:21.36 spid8 Starting up database 'msdb'.
2003-12-09 12:33:21.36 spid9 Starting up database 'pubs'.
2003-12-09 12:33:21.36 spid10 Starting up database 'Northwind'.
2003-12-09 12:33:21.36 spid11 Starting up database 'StarBldr'.
2003-12-09 12:33:21.36 spid13 Starting up database 'BRCS'.
2003-12-09 12:33:21.36 spid12 Starting up database 'SurfControl_WebFilter'.
2003-12-09 12:33:21.36 spid14 Starting up database 'BEDB'.
2003-12-09 12:33:21.36 spid11 Bypassing recovery for database 'StarBldr'
because it is marked IN LOAD.
2003-12-09 12:33:21.36 spid5 Bypassing recovery for database 'model' because
it is marked IN LOAD.
2003-12-09 12:33:21.36 spid13 Bypassing recovery for database 'BRCS' because
it is marked IN LOAD.
2003-12-09 12:33:21.38 spid5 Database 'model' cannot be opened. It is in the
middle of a restore.
Any help would be grateful... Thanks
TIASee my other reply.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"_M_" <here@.gone.com> wrote in message
news:OI34LpovDHA.3116@.tk2msftngp13.phx.gbl...
> Is there a way to stop a hung restore ?
> Here are some error logs:
> 2003-12-09 12:35:20.94 server Microsoft SQL Server 2000 - 8.00.760 (Intel
> X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> 2003-12-09 12:35:20.94 server Copyright (C) 1988-2002 Microsoft
Corporation.
> 2003-12-09 12:35:20.94 server All rights reserved.
> 2003-12-09 12:35:20.94 server Server Process ID is 3984.
> 2003-12-09 12:35:20.94 server Logging SQL Server messages in file
> 'e:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
> 2003-12-09 12:35:20.96 server SQL Server is starting at priority class
> 'normal'(4 CPUs detected).
> 2003-12-09 12:35:21.10 server SQL Server configured for thread mode
> processing.
> 2003-12-09 12:35:21.10 server Using dynamic lock allocation. [2500] Lock
> Blocks, [5000] Lock Owner Blocks.
> 2003-12-09 12:35:21.17 server Attempting to initialize Distributed
> Transaction Coordinator.
> 2003-12-09 12:35:23.25 spid3 Starting up database 'master'.
> 2003-12-09 12:35:23.32 server Using 'SSNETLIB.DLL' version '8.0.760'.
> 2003-12-09 12:35:23.32 spid5 Starting up database 'model'.
> 2003-12-09 12:35:23.32 spid3 Server name is 'W2KSVR'.
> 2003-12-09 12:35:23.32 spid8 Starting up database 'msdb'.
> 2003-12-09 12:35:23.32 spid9 Starting up database 'pubs'.
> 2003-12-09 12:35:23.32 spid10 Starting up database 'Northwind'.
> 2003-12-09 12:35:23.32 spid11 Starting up database 'StarBldr'.
> 2003-12-09 12:35:23.32 spid12 Starting up database
'SurfControl_WebFilter'.
> 2003-12-09 12:35:23.32 spid13 Starting up database 'BRCS'.
> 2003-12-09 12:35:23.32 spid14 Starting up database 'BEDB'.
> 2003-12-09 12:35:23.33 spid11 Bypassing recovery for database 'StarBldr'
> because it is marked IN LOAD.
> 2003-12-09 12:35:23.33 spid5 Bypassing recovery for database 'model'
because
> it is marked IN LOAD.
> 2003-12-09 12:35:23.33 spid5 Database 'model' cannot be opened. It is in
the
> middle of a restore.
> 2003-12-09 12:35:23.33 spid13 Bypassing recovery for database 'BRCS'
because
> it is marked IN LOAD.
> HERE IS THE ERRORLOG.1 file:
> 2003-12-09 12:33:19.02 server Microsoft SQL Server 2000 - 8.00.760 (Intel
> X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> 2003-12-09 12:33:19.02 server Copyright (C) 1988-2002 Microsoft
Corporation.
> 2003-12-09 12:33:19.02 server All rights reserved.
> 2003-12-09 12:33:19.02 server Server Process ID is 4364.
> 2003-12-09 12:33:19.02 server Logging SQL Server messages in file
> 'e:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
> 2003-12-09 12:33:19.02 server SQL Server is starting at priority class
> 'normal'(4 CPUs detected).
> 2003-12-09 12:33:19.16 server SQL Server configured for thread mode
> processing.
> 2003-12-09 12:33:19.16 server Using dynamic lock allocation. [2500] Lock
> Blocks, [5000] Lock Owner Blocks.
> 2003-12-09 12:33:19.24 server Attempting to initialize Distributed
> Transaction Coordinator.
> 2003-12-09 12:33:21.30 spid3 Starting up database 'master'.
> 2003-12-09 12:33:21.35 server Using 'SSNETLIB.DLL' version '8.0.760'.
> 2003-12-09 12:33:21.35 spid5 Starting up database 'model'.
> 2003-12-09 12:33:21.36 spid3 Server name is 'W2KSVR'.
> 2003-12-09 12:33:21.36 spid8 Starting up database 'msdb'.
> 2003-12-09 12:33:21.36 spid9 Starting up database 'pubs'.
> 2003-12-09 12:33:21.36 spid10 Starting up database 'Northwind'.
> 2003-12-09 12:33:21.36 spid11 Starting up database 'StarBldr'.
> 2003-12-09 12:33:21.36 spid13 Starting up database 'BRCS'.
> 2003-12-09 12:33:21.36 spid12 Starting up database
'SurfControl_WebFilter'.
> 2003-12-09 12:33:21.36 spid14 Starting up database 'BEDB'.
> 2003-12-09 12:33:21.36 spid11 Bypassing recovery for database 'StarBldr'
> because it is marked IN LOAD.
> 2003-12-09 12:33:21.36 spid5 Bypassing recovery for database 'model'
because
> it is marked IN LOAD.
> 2003-12-09 12:33:21.36 spid13 Bypassing recovery for database 'BRCS'
because
> it is marked IN LOAD.
> 2003-12-09 12:33:21.38 spid5 Database 'model' cannot be opened. It is in
the
> middle of a restore.
> Any help would be grateful... Thanks
> TIA
>

Model DB in Warm-Standby

Can anyone help me with this? I tried to use the query
analyzer to bring the Model DB back on-line:
restore database model
with recovery
But this is what I get:
Server: Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the
database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.I am assuming that no one else has an open connection to
the model database. You will get this error if you have
the model database "open" in Enterprise Manager and
simultaneously try to execute the RESTORE command from
query analyzer. Hope this helps.
Tim
>--Original Message--
>Can anyone help me with this? I tried to use the query
>analyzer to bring the Model DB back on-line:
>restore database model
>with recovery
>But this is what I get:
>Server: Msg 3101, Level 16, State 1, Line 1
>Exclusive access could not be obtained because the
>database is in use.
>Server: Msg 3013, Level 16, State 1, Line 1
>RESTORE DATABASE is terminating abnormally.
>.
>sql

model db during restore

Hello, i have no specific problem but i just want to know your opinion on the issue. Last week I tested my recovery scenario and after installing new server i successfully restored master db. Then I tried to start server, but it wouldn't come up because physical path of model db in master..sysdatabases table didn't correspond to the actual location of db on new server, so that tempdb couldn't be created from model db on server startup. I started server without recovering dbs with exception of master using trace -T3608. Now I hoped that I could restore model using MOVE clause but it gave me error saying that restore operation needs to use temporary table in tempdb. To break this vicious circle I used model db files from installation CD and put them to the expected location. After two restarts server accepted the files and I went on restoring msdb and user databases. But I'm sure I must be missing something and there must be better way to do this but i haven't been able to find anything relating to this problem. Thank you for your input. mojzaGo to Microsoft site and download the best practices anylyzer. Its very good for variables read on the database and could help in some other ways.

Misunderstanding of backup

I always assumed that the backup & restore procedure stored simply
data, not structure.

Now I find that if I add a new table to a database, then restore using
an old backup, the new table is gone.

Is there any way to restore JUST the data from a backup? If not, is
there any way to archive & import just data? The import/export wizard
only seems to send a single table to a flat file, but I need all the
tables.

Is there perhaps a command-line parameterthat isn't available in the
wizard that would accomplish this?You can restore to a different database or server and then copy over
the data.
http://msdn.microsoft.com/library/d...backpc_6ng9.asp

--
David Portas
SQL Server MVP
--|||You can restore to a different database or server and then copy over
the data.
http://msdn.microsoft.com/library/d...backpc_6ng9.asp

--
David Portas
SQL Server MVP
--|||
David Portas wrote:
> You can restore to a different database or server and then copy over
> the data.
> http://msdn.microsoft.com/library/d...backpc_6ng9.asp
> --
> David Portas
> SQL Server MVP
> --

Must the data be copied using the MOVE command, or can I simply
physically relocate the ldf & mdf files to the target server?|||
David Portas wrote:
> You can restore to a different database or server and then copy over
> the data.
> http://msdn.microsoft.com/library/d...backpc_6ng9.asp
> --
> David Portas
> SQL Server MVP
> --

Must the data be copied using the MOVE command, or can I simply
physically relocate the ldf & mdf files to the target server?|||You can always detach and reattach the files (sp_attach_db and sp_detach_db)
but as you'll have to restore them first you may as well restore them where
you want them to start with.

--
David Portas
SQL Server MVP
--|||You can always detach and reattach the files (sp_attach_db and sp_detach_db)
but as you'll have to restore them first you may as well restore them where
you want them to start with.

--
David Portas
SQL Server MVP
--|||
David Portas wrote:
> You can always detach and reattach the files (sp_attach_db and sp_detach_db)
> but as you'll have to restore them first you may as well restore them where
> you want them to start with.
> --
> David Portas
> SQL Server MVP
> --

Once the new temp database is created, what procedure would you
recommend for copying just the data between databases?|||Darryl (DarrylJ@.yahoo.com) writes:
> David Portas wrote:
>> You can always detach and reattach the files (sp_attach_db and
>> sp_detach_db) but as you'll have to restore them first you may as well
>> restore them where you want them to start with.
>>
> Once the new temp database is created, what procedure would you
> recommend for copying just the data between databases?

Depends on why, and how often etc. If you want to do it on a regular
basis, replication could be the way to go. For a one-off thing,
it depends on whether the target database already has data, and
what you want to do with it etc.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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