Saturday, February 25, 2012

Accidentally dettached

Hello all, i trying to shrink db from sql express manager, then
suddently error msg appear, afterthat the db disappeared from the
list !!
I want the db back & working, i trying attach but without success.
Can anyone help me about how can i restore my db (mdf,ldf are found),
attach not work.Hi
"hamiiied@.gmail.com" wrote:

> Hello all, i trying to shrink db from sql express manager, then
> suddently error msg appear, afterthat the db disappeared from the
> list !!
> I want the db back & working, i trying attach but without success.
> Can anyone help me about how can i restore my db (mdf,ldf are found),
> attach not work.
>
It would be useful if you had said how you tried to re-attach the database
and what error number and message you recieved. There may be a more
fundamental underlying error why you can not do this!
You can re-attach a database using the sp_attach_db command in SQLCMD the
syntax of this can be found in books online or at
http://msdn2.microsoft.com/en-us/library/aa259611(SQL.80).aspx. If you dont
have the log file you can use sp_attach_single_file_db.
With SQLExpress you can also use the CREATE DATABASE with the FOR ATTACH
clause.
John|||Hi,
I think it will be easier to help you, if you'd tell which error
messages you get. Also, what makes you believe that the databases has
been detached? Detaching a database is something you'll have to do, and
not something that just happens.
Regards
Steen Schlter Persson
Database Administrator / System Administrator
hamiiied@.gmail.com wrote:
> Hello all, i trying to shrink db from sql express manager, then
> suddently error msg appear, afterthat the db disappeared from the
> list !!
> I want the db back & working, i trying attach but without success.
> Can anyone help me about how can i restore my db (mdf,ldf are found),
> attach not work.
>|||On Jun 25, 10:48 am, "Steen Schl=FCter Persson (DK)"
<steen@.REMOVE_THIS_asavaenget.dk> wrote:
> Hi,
> I think it will be easier to help you, if you'd tell which error
> messages you get. Also, what makes you believe that the databases has
> been detached? Detaching a database is something you'll have to do, and
> not something that just happens.
> --
> Regards
> Steen Schl=FCter Persson
> Database Administrator / System Administrator
>
> hamii...@.gmail.com wrote:
> - Show quoted text -
Hi, thanks for your replies, i fix the bugs by reinstalling SQL
server, then attaching the mdf.|||"hamiiied@.gmail.com" wrote:

> On Jun 25, 10:48 am, "Steen Schl|ter Persson (DK)"
> <steen@.REMOVE_THIS_asavaenget.dk> wrote:
> Hi, thanks for your replies, i fix the bugs by reinstalling SQL
> server, then attaching the mdf.
>
So we don't really know what was wrong!!
John|||For understanding what happen i can explain the problem from start to
end ...
when i complete development tasks on my db using sql express managment
studio, i right click on db --> shrink, the server hanging for tens
of seconds, afterthat it return error message, it is long msg but i
read its header like 'can't determine rows or index in sysindexes or
like this',,, i follow the instruction to solve until it routing me to
microsoft support, finally microsoft online help return sorry no idea
about this error.
i think the error happen in the master db..
after that the db disappeared from the db list..
i explore the location of mdf,ldf i found it intact.
then i try to attach my db but without benifit, it return to me error
msg like the msg when you try to attach already attached db.
then i think to add new db with the same name, the server again return
error!
So i concluded from this case my db name & alias already found in
master db or whatever sql registry, but not physically attached to
mdf,ldf files.
when i attach my db to another sql server on other pc, it work fine &
no data lost.
So i uninstalled my sql server then reinstall & attach, finally it
work fine ...
that is all.
i know it can be solved by another way if i expert in dba admin.
thank you all, & welcome to reply if any one get the idea on dba admin
basis & knowledge...|||"hamiiied@.gmail.com" wrote:

> For understanding what happen i can explain the problem from start to
> end ...
> when i complete development tasks on my db using sql express managment
> studio, i right click on db --> shrink, the server hanging for tens
> of seconds, afterthat it return error message, it is long msg but i
> read its header like 'can't determine rows or index in sysindexes or
> like this',,, i follow the instruction to solve until it routing me to
> microsoft support, finally microsoft online help return sorry no idea
> about this error.
> i think the error happen in the master db..
> after that the db disappeared from the db list..
> i explore the location of mdf,ldf i found it intact.
> then i try to attach my db but without benifit, it return to me error
> msg like the msg when you try to attach already attached db.
> then i think to add new db with the same name, the server again return
> error!
> So i concluded from this case my db name & alias already found in
> master db or whatever sql registry, but not physically attached to
> mdf,ldf files.
> when i attach my db to another sql server on other pc, it work fine &
> no data lost.
> So i uninstalled my sql server then reinstall & attach, finally it
> work fine ...
> that is all.
> i know it can be solved by another way if i expert in dba admin.
> thank you all, & welcome to reply if any one get the idea on dba admin
> basis & knowledge...
>
My guess is that you have auto close set for this database and it closed
because there was no connections to the database half way through your
process. For performance reasons auto close is not a good idea.
John|||Hi,
You need to try the following steps to get it back:
1. Put your current .mdf, .ndfs, .ldfs in a safe place - i.e. make a
copy of them and place them under a different path.
2. Delete the .mdf, .ndfs, .ldfs from the current path (where SQL
Server expects to find them).
3. Create a database with exactly the same name as your non-functioning
database and with the exact same file names and file locations.
4. Shut down the SQL Server service.
5. Delete the files of the dummy database you just created in step 3.
6. Replace the files you deleted in step 5 with the original database
files, i.e. the ones you put in a safe place in step 1.
7. Startup the SQL Server service.
8. Assuming that the database does come online properly run DBCC
CHECKDB on it.
Jonathan
hamiiied@.gmail.com wrote:
> Hello all, i trying to shrink db from sql express manager, then
> suddently error msg appear, afterthat the db disappeared from the
> list !!
> I want the db back & working, i trying attach but without success.
> Can anyone help me about how can i restore my db (mdf,ldf are found),
> attach not work.
>

No comments:

Post a Comment