Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


User database moved to "System Databases" container


User database moved to "System Databases" container

Author
Message
deepak.bisht
deepak.bisht
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 116
I need help.

One of my user database is somehow moved to "System Databases" container in SQL Server Management Studio(I don't know how it moved).

i.e. it is visible in Enterprise Manager the database is visible under Databases> System Databases (along with master, msdb, model and tempdb).

Now, I am not able to move it back to user databases section. I have tried drag and drop, checked DB properties etc. but it is not moving back.

I am using SQL server 2005 Ent edition SP2

Regards,
Deepak Bisht
free_mascot
free_mascot
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2879 Visits: 2235
What is the name of the database?

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
deepak.bisht
deepak.bisht
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 116
name of the database is tlmain

Regards,
Deepak Bisht
deepak.bisht
deepak.bisht
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 116
Got a similar issue reported in one of the forums.

http://www.sqlservercentral.com/Forums/Topic1381740-1292-1.aspx
tillersdba
tillersdba
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 53
Try detach and attach option
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21075 Visits: 18259
deepak.bisht (3/21/2014)
name of the database is tlmain

Regards,
Deepak Bisht


Sounds like you are using Talisma. In some cases they named their distribution database as tlmain.

At any rate, distribution databases can have a different name and do get created in the system databases section.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8784 Visits: 16558
deepak.bisht (3/21/2014)
I need help.

One of my user database is somehow moved to "System Databases" container in SQL Server Management Studio(I don't know how it moved).

i.e. it is visible in Enterprise Manager the database is visible under Databases> System Databases (along with master, msdb, model and tempdb).

Now, I am not able to move it back to user databases section. I have tried drag and drop, checked DB properties etc. but it is not moving back.

I am using SQL server 2005 Ent edition SP2

Regards,
Deepak Bisht

As pointed out already, this is a distribution database not a user database. They are designed to sit under the system database container
What does the following query return?

exec sp_helpdistributor



-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
deepak.bisht
deepak.bisht
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 116
The database is user database and now it is under System Databases in SS management studio.

Let me explain how it moved under system databases-
1. Third party tool was used to configure replication.
2. By mistake the name of the user database was entered at the time of specifying the name of distributor database. (SQL server ent. manager does not allow you to enter name of user database as distributor db but this third-party application allowed).
3. Third-party tool changed the is_distributor value to 1 for the user database in sys.databases.
4. Replication configuration stopped in between as the same database was being used as publisher and distributor
5. is_distributor value was not changed back to 0. It's value is still 1.

SSMS shows databases named master, model, msdb or tempdb, and any other db where the field is_distributor = 1 in the view sys.databases in System Databases container.

Now, the challenge is to change the value of is_distributor to 0 of the user database.


exec sp_helpdistributor does not return anything. It says "Command(s) completed successfully."

Regards,
Deepak Bisht
deepak.bisht
deepak.bisht
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 116
Finally I was able to fix this issue...:-D

The steps are as follows-
- Take the backup of master database
- Start SQL Server in single user mode using –m switch
- Login to SQL server Dedicated Administrator connection
- Update value of ‘category’ field to 23040 from existing value 23056 in ‘sys.sysdbreg’ table for <dbname> database
- Start SQL server in normal mode
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search