master database

master database

Post by Jame » Sat, 07 Jul 2001 00:06:46



My SQL Server machine had a system crash and when I rebooted the master
database did not have all of my databases listed in it.  Does anyone know
how to fix this without having to rebuild the system?
 
 
 

master database

Post by Sam » Sat, 07 Jul 2001 01:35:33


If you still have the mdf and ldf files in the data directory you can run
the sp_dbattach stored procedure.  This will recreate the missing databases.
Check out BOL.

Quote:> My SQL Server machine had a system crash and when I rebooted the master
> database did not have all of my databases listed in it.  Does anyone know
> how to fix this without having to rebuild the system?


 
 
 

master database

Post by Andre » Sat, 07 Jul 2001 01:44:05


Did you make a backup of the master DB?

Thanks
Andrew


Quote:> My SQL Server machine had a system crash and when I rebooted the master
> database did not have all of my databases listed in it.  Does anyone know
> how to fix this without having to rebuild the system?

 
 
 

master database

Post by Jame » Sat, 07 Jul 2001 02:24:34


Quote:>My SQL Server machine had a system crash and when I rebooted the master
>database did not have all of my databases listed in it.  Does anyone know
>how to fix this without having to rebuild the system?

Upon further review I've determined that it may not have been a system
crash.  What I now believe to be the case is that when I upgraded from SQL 7
to SQL 2000 the Model DB may not have had enought disk space to grow during
the upgrade.  So I again pose my question (albiet somewhat clarified):  Does
anyone know how to fix this without having to rebuild the system?
 
 
 

master database

Post by Sarah Henwo » Sat, 07 Jul 2001 03:11:04


You can attach your databases using the sp_attach_db stored procedure.  See
SQL Server Books On Line for more information on attaching databases with
this procedure.

Sarah Henwood
Microsoft SQL Server Support

 
 
 

master database

Post by Bill Ka » Sat, 07 Jul 2001 07:29:30


another friendly advise....make sure you sp_detach_db first.....

thx


Quote:

> You can attach your databases using the sp_attach_db stored procedure.
See
> SQL Server Books On Line for more information on attaching databases with
> this procedure.

> Sarah Henwood
> Microsoft SQL Server Support

 
 
 

master database

Post by Ed Harp » Sat, 07 Jul 2001 22:50:38


James:

If you still have the LDF & MDF files, you can use sp_attach_db.
If you only have the MDF file, you can use sp_attach_single_file_db.

However, I find it incredible that you were able to recover from a system
crash and the only adverse affect is
missing a database from the master database.  I would expect this type of
result if you had used an older copy
of the master database, or had restored from backup.

HTH

Ed Harper
Microsoft SQL Support.

 
 
 

master database

Post by Nick Hal » Sat, 07 Jul 2001 23:15:15


I'd agree with Ed. It's sound like things aren't as they seem.
You know that something has gone wrong but you're not sure what, and the
only symptoms are those which you can see. If you have any other doubts then
I would get yourself either a fresh box setup or reinstall the whole thing.
I for example, had an upgrade from SQL7.0 sp3 to SQL2000 go wrong half way

box. However, I had some system sp issues, so I rebuilt the system on
another box. It's better to be safe than sorry  :o)

Nick Hall
Senior Database Administrator
http://www.Figleaves.com


Quote:> James:

> If you still have the LDF & MDF files, you can use sp_attach_db.
> If you only have the MDF file, you can use sp_attach_single_file_db.

> However, I find it incredible that you were able to recover from a system
> crash and the only adverse affect is
> missing a database from the master database.  I would expect this type of
> result if you had used an older copy
> of the master database, or had restored from backup.

> HTH

> Ed Harper
> Microsoft SQL Support.

 
 
 

master database

Post by Sarah Henwo » Sun, 08 Jul 2001 01:08:24


Since you indicated that your databases do not appear listed after your
restore/rebuild of the master databases, then there are no existing
database entries to detach.  In your case, the sp_detach_db would fail
since the database does not currently exist.  If your databases and log
files are intact, then the sp_attach_db procedure should be able to attach
the databases and recreate the entries in the master database to these
databases.

Sarah Henwood
Microsoft SQL Server Support