accidentally populate tables into System databases/master database

Hi,

I accidentally populate tables into System databases/master database. What should I do? Should I delete all the tables I populate in mster database?

Thank you very much!

I would transfer the tables and data to the database where they 'should' be located, and then DELETE the ones in the master database.

|||

To move a table to another disk you have to
-create a secondary file group on an another location (or separate) of physical disk.
-drop the clustered index of the table and re-create it on that file group (Alter Table ... | MOVE TO { partition_scheme_name ( column_name ) | filegroup
| "default"}
- see Books Online).

This procedure can be use on large tables; if the size of your tables is not so big you can copy these tables with import/export wizard to a test db and copy backb in database with that filegroup made default.

|||

Thank you All,

I just deleted all the talbes were accidentally created in System/Master Database in sql server 2005. Sql server seems working fine.

Accidentally dropped these tables

I dropped these tables MSreplication_subscriptions and MSsubscription_agents
used for replication on the subscribing end.
How can I recreate them since its a system table.
From sp_MScreate_sub_tables
CREATE TABLE dbo.MSreplication_subscriptions
(
publisher sysname NOT NULL,
publisher_db sysname NOT NULL,
publication sysname NULL,
independent_agent bit NOT NULL,
subscription_type int NOT NULL,
distribution_agent sysname NULL,
time smalldatetime NOT NULL,
description nvarchar(255) NULL,
transaction_timestamp varbinary(16) NOT NULL,
-- SyncTran
update_mode tinyint NOT NULL,
agent_id binary(16) NULL,
subscription_guid binary(16) NULL,
subid binary(16) NULL,
immediate_sync bit NOT NULL default 1 -- sync_mode with a default of 1
)
CREATE TABLE dbo.MSsubscription_agents
(
id int identity,
publisher sysname NOT NULL,
publisher_db sysname NOT NULL,
publication sysname NOT NULL,
subscription_type int NOT NULL,
queue_id sysname NULL,
update_mode tinyint default 0 not null, -- 0 = read only, 1 =
sync/immediate, 2 = queued, 3 = failover, 4 = sqlqueued, 5 = sqlqueued
failover
failover_mode bit default 0 not null, -- 0 - sync/immediate, 1 = queued
spid int NOT NULL,
login_time datetime NOT NULL,
allow_subscription_copy bit default 0 not null,
attach_state int default 0 not null, -- 0: not attached 1 attached but
not processed 2 attached and processed.
attach_version binary(16) default newid() not null,
last_sync_status int NULL, -- allow null for upgrade
last_sync_summary sysname NULL, -- allow null for upgrade
last_sync_time datetime NULL, -- allow null for upgrade
queue_server sysname NULL -- only used for MSMQ based updating
subscribers
)
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23TZtfkaXFHA.3712@.TK2MSFTNGP09.phx.gbl...
> I dropped these tables MSreplication_subscriptions and
MSsubscription_agents
> used for replication on the subscribing end.
> How can I recreate them since its a system table.
>
|||But I guess just creating those tables may not be enough.. Am i right ? I
know it has some data in there .. Will the agents still fail ?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:O6Qwki6XFHA.2124@.TK2MSFTNGP14.phx.gbl...
> From sp_MScreate_sub_tables
> CREATE TABLE dbo.MSreplication_subscriptions
> (
> publisher sysname NOT NULL,
> publisher_db sysname NOT NULL,
> publication sysname NULL,
> independent_agent bit NOT NULL,
> subscription_type int NOT NULL,
> distribution_agent sysname NULL,
> time smalldatetime NOT NULL,
> description nvarchar(255) NULL,
> transaction_timestamp varbinary(16) NOT NULL,
> -- SyncTran
> update_mode tinyint NOT NULL,
> agent_id binary(16) NULL,
> subscription_guid binary(16) NULL,
> subid binary(16) NULL,
> immediate_sync bit NOT NULL default 1 -- sync_mode with a default of 1
> )
>
> CREATE TABLE dbo.MSsubscription_agents
> (
> id int identity,
> publisher sysname NOT NULL,
> publisher_db sysname NOT NULL,
> publication sysname NOT NULL,
> subscription_type int NOT NULL,
> queue_id sysname NULL,
> update_mode tinyint default 0 not null, -- 0 = read only, 1 =
> sync/immediate, 2 = queued, 3 = failover, 4 = sqlqueued, 5 = sqlqueued
> failover
> failover_mode bit default 0 not null, -- 0 - sync/immediate, 1 = queued
> spid int NOT NULL,
> login_time datetime NOT NULL,
> allow_subscription_copy bit default 0 not null,
> attach_state int default 0 not null, -- 0: not attached 1 attached but
> not processed 2 attached and processed.
> attach_version binary(16) default newid() not null,
> last_sync_status int NULL, -- allow null for upgrade
> last_sync_summary sysname NULL, -- allow null for upgrade
> last_sync_time datetime NULL, -- allow null for upgrade
> queue_server sysname NULL -- only used for MSMQ based updating
> subscribers
> )
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23TZtfkaXFHA.3712@.TK2MSFTNGP09.phx.gbl...
> MSsubscription_agents
>

Accidentally dropped DBO from database

I altered a script that drops all users from a database and accidentally
changes an AND to an OR so that the user DBO was dropped after a refresh fro
m
production to dev.
Can I readd this user or is the only solution is to restore from backup?
Thanks,
LindaYou can use sp_changedbowner and dbo will map to whatever
login you assign as the database owner with
sp_changedbonwer.
You can find more information on sp_changedbowner in SQL
Server books online.
-Sue
On Tue, 18 Jan 2005 06:25:03 -0800, "Linda"
<Linda@.discussions.microsoft.com> wrote:

>I altered a script that drops all users from a database and accidentally
>changes an AND to an OR so that the user DBO was dropped after a refresh fr
om
>production to dev.
>Can I readd this user or is the only solution is to restore from backup?
>Thanks,
>Linda|||The user dbo was dropped from sysusers. This did not solve the problem. I
ended up restoring the database from backup but that took several hours and
I
had been hoping a quick script to add dbo back to sysusers might solve it.
"Sue Hoegemeier" wrote:

> You can use sp_changedbowner and dbo will map to whatever
> login you assign as the database owner with
> sp_changedbonwer.
> You can find more information on sp_changedbowner in SQL
> Server books online.
> -Sue
> On Tue, 18 Jan 2005 06:25:03 -0800, "Linda"
> <Linda@.discussions.microsoft.com> wrote:
>
>|||Actually, what I suggested is exactly how you fix the issue.
Without any information on what happened or what errors you
had, it's hard to give you more specifics.
If you were receiving the error:
Error 21776: [SQL-DMO] The name 'dbo' was not found in the
Users collection. If the name is a qualified name, use [] to
separate the various parts of the name, and try again
That error is due to having databases where the owner (dbo)
is mapped to a login that doesn't exist on the server. It's
not a matter of DBO being dropped but rather problems with
the mappings.
If you get an error along the lines of "the user is already
a user in the database" when using sp_changedbowner, that's
generally due to a mismatch between dbo and the database
owner. You can work around this by creating a temp login,
change the owner to this new login, then change the owner to
the login you actually want to be the owner and drop the
temporary login.
-Sue
On Thu, 20 Jan 2005 08:57:06 -0800, "lslmustang"
<lslmustang@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>The user dbo was dropped from sysusers. This did not solve the problem. I
>ended up restoring the database from backup but that took several hours and
I
>had been hoping a quick script to add dbo back to sysusers might solve it.
>"Sue Hoegemeier" wrote:
>

Accidentally disable admin login

Hi, I have accidentally disabled the Administrator account in Sql Server 2005 (Douh!) on my clients server (double Dough!) & now I can login to enable it.

The <BuiltIn\Administartors> account is still active, The accout i disabled was <Domain\Administrator> (forgot I was logined via Terminal server).

Any help/assistance would be much appreciate!!

Cheers,

Nez

HI, i was unable to delte this post so may as well put the answer here.

to enable it again i used the SQLCMD.exe in the ".../90/tools/binn" folder

sp_grantlogin etc...

Cheers

|||Appreciate your solution and feedback here, in this case you can take help of DAC too if there is no other route.

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.
>

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 Schlter 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 Schlter 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

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 Schlüter 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:
> > 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.- Hide quoted text -
> - 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,
> >
> > 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|ter Persson
> > Database Administrator / System Administrator
> >
> >
> >
> > hamii...@.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.- Hide quoted text -
> >
> > - Show quoted text -
> 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.
>

accidentally deleted BUILTIN\Administrators group in Security tab

How can I recreate the BUILTIN\Administrators group that I had deleted
accidentally? My Slqserver 2K is in mixed mode.
Thanks
BillU don't have to as long as sql service and sql agent accounts have appropriate rights however you can do this fform EM --> logins and just add the user Builtin\Administrators

accidentally deleted BUILTIN\Administrators group in Security tab

How can I recreate the BUILTIN\Administrators group that I had deleted
accidentally? My Slqserver 2K is in mixed mode.
Thanks
BillU don't have to as long as sql service and sql agent accounts have appropria
te rights however you can do this fform EM --> logins and just add the user
Builtin\Administrators

accidentally deleted a table in my local SQL Server how can I get back the table

Hi,

I accidentally deleted a table in my local server. How can I get back the table? I did not do it as a transaction!

Thanks in AdvanceYou need to restore back from your backup if available. There may be third party softwares that allow you to do such operations ...

Accidentally created new LDF, now get error with old LDF

Greetings all. I wanted to separate the MDF and LDF files. I am an SQL
newbie and here is what I did.
1. Detach MDF through right-click options
2. Move LDF to new drive
3. Attach MDF through right-click options
4. Path would not change for original LDF location and I hit OK by
accident (not sure how) but I did, and it created a new LDF in the
original location.
5. Detach MDF again
6. Attach MDF again
7. Manually typed path of moved LDF (the one I wanted to begin with)
and did not click verify (that always changed the location back to the
original). Just clicked OK and it took the path.
8. ERROR 5173 Cannot associate files with different databases
Any help would be greatly appreciated.
Regards,
Gary
When you attached the db the first time it created a new log file and
adjusted the metadata to account for it. At that point the original log
file was no longer valid. So you should detach it and move the new log file
to the other location and attach that one.
Andrew J. Kelly SQL MVP
"Dreamer" <spencermountains@.yahoo.com> wrote in message
news:1134830448.181128.92340@.z14g2000cwz.googlegro ups.com...
> Greetings all. I wanted to separate the MDF and LDF files. I am an SQL
> newbie and here is what I did.
> 1. Detach MDF through right-click options
> 2. Move LDF to new drive
> 3. Attach MDF through right-click options
> 4. Path would not change for original LDF location and I hit OK by
> accident (not sure how) but I did, and it created a new LDF in the
> original location.
> 5. Detach MDF again
> 6. Attach MDF again
> 7. Manually typed path of moved LDF (the one I wanted to begin with)
> and did not click verify (that always changed the location back to the
> original). Just clicked OK and it took the path.
> 8. ERROR 5173 Cannot associate files with different databases
> Any help would be greatly appreciated.
> Regards,
> Gary
>
|||Thanks for the reply. Do I not need the original LDF file then? Have I
lost anything?
|||Hopefully not<g>. When you detach the db it should commit or rollback all
transactions that haven't already been attended to. Before you do anything
like detach a db it is always a good idea to do a full backup first in case
anything happens and you need to get back to where you were. But in this
case you should be fine as long as the attach works with no errors.
Andrew J. Kelly SQL MVP
"Dreamer" <spencermountains@.yahoo.com> wrote in message
news:1134832357.374087.105070@.g44g2000cwa.googlegr oups.com...
> Thanks for the reply. Do I not need the original LDF file then? Have I
> lost anything?
>

Accidentally created new LDF, now get error with old LDF

Greetings all. I wanted to separate the MDF and LDF files. I am an SQL
newbie and here is what I did.
1. Detach MDF through right-click options
2. Move LDF to new drive
3. Attach MDF through right-click options
4. Path would not change for original LDF location and I hit OK by
accident (not sure how) but I did, and it created a new LDF in the
original location.
5. Detach MDF again
6. Attach MDF again
7. Manually typed path of moved LDF (the one I wanted to begin with)
and did not click verify (that always changed the location back to the
original). Just clicked OK and it took the path.
8. ERROR 5173 Cannot associate files with different databases
Any help would be greatly appreciated.
Regards,
GaryWhen you attached the db the first time it created a new log file and
adjusted the metadata to account for it. At that point the original log
file was no longer valid. So you should detach it and move the new log file
to the other location and attach that one.
--
Andrew J. Kelly SQL MVP
"Dreamer" <spencermountains@.yahoo.com> wrote in message
news:1134830448.181128.92340@.z14g2000cwz.googlegroups.com...
> Greetings all. I wanted to separate the MDF and LDF files. I am an SQL
> newbie and here is what I did.
> 1. Detach MDF through right-click options
> 2. Move LDF to new drive
> 3. Attach MDF through right-click options
> 4. Path would not change for original LDF location and I hit OK by
> accident (not sure how) but I did, and it created a new LDF in the
> original location.
> 5. Detach MDF again
> 6. Attach MDF again
> 7. Manually typed path of moved LDF (the one I wanted to begin with)
> and did not click verify (that always changed the location back to the
> original). Just clicked OK and it took the path.
> 8. ERROR 5173 Cannot associate files with different databases
> Any help would be greatly appreciated.
> Regards,
> Gary
>|||Thanks for the reply. Do I not need the original LDF file then? Have I
lost anything?|||Hopefully not<g>. When you detach the db it should commit or rollback all
transactions that haven't already been attended to. Before you do anything
like detach a db it is always a good idea to do a full backup first in case
anything happens and you need to get back to where you were. But in this
case you should be fine as long as the attach works with no errors.
--
Andrew J. Kelly SQL MVP
"Dreamer" <spencermountains@.yahoo.com> wrote in message
news:1134832357.374087.105070@.g44g2000cwa.googlegroups.com...
> Thanks for the reply. Do I not need the original LDF file then? Have I
> lost anything?
>

Accidentally created new LDF, now get error with old LDF

Greetings all. I wanted to separate the MDF and LDF files. I am an SQL
newbie and here is what I did.
1. Detach MDF through right-click options
2. Move LDF to new drive
3. Attach MDF through right-click options
4. Path would not change for original LDF location and I hit OK by
accident (not sure how) but I did, and it created a new LDF in the
original location.
5. Detach MDF again
6. Attach MDF again
7. Manually typed path of moved LDF (the one I wanted to begin with)
and did not click verify (that always changed the location back to the
original). Just clicked OK and it took the path.
8. ERROR 5173 Cannot associate files with different databases
Any help would be greatly appreciated.
Regards,
GaryWhen you attached the db the first time it created a new log file and
adjusted the metadata to account for it. At that point the original log
file was no longer valid. So you should detach it and move the new log file
to the other location and attach that one.
Andrew J. Kelly SQL MVP
"Dreamer" <spencermountains@.yahoo.com> wrote in message
news:1134830448.181128.92340@.z14g2000cwz.googlegroups.com...
> Greetings all. I wanted to separate the MDF and LDF files. I am an SQL
> newbie and here is what I did.
> 1. Detach MDF through right-click options
> 2. Move LDF to new drive
> 3. Attach MDF through right-click options
> 4. Path would not change for original LDF location and I hit OK by
> accident (not sure how) but I did, and it created a new LDF in the
> original location.
> 5. Detach MDF again
> 6. Attach MDF again
> 7. Manually typed path of moved LDF (the one I wanted to begin with)
> and did not click verify (that always changed the location back to the
> original). Just clicked OK and it took the path.
> 8. ERROR 5173 Cannot associate files with different databases
> Any help would be greatly appreciated.
> Regards,
> Gary
>|||Thanks for the reply. Do I not need the original LDF file then? Have I
lost anything?|||Hopefully not<g>. When you detach the db it should commit or rollback all
transactions that haven't already been attended to. Before you do anything
like detach a db it is always a good idea to do a full backup first in case
anything happens and you need to get back to where you were. But in this
case you should be fine as long as the attach works with no errors.
Andrew J. Kelly SQL MVP
"Dreamer" <spencermountains@.yahoo.com> wrote in message
news:1134832357.374087.105070@.g44g2000cwa.googlegroups.com...
> Thanks for the reply. Do I not need the original LDF file then? Have I
> lost anything?
>