Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

User database moved to "System Databases" container Expand / Collapse
Author
Message
Posted Friday, March 21, 2014 6:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 5:48 AM
Points: 5, Visits: 105
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
Post #1553452
Posted Friday, March 21, 2014 6:53 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
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."
Post #1553463
Posted Friday, March 21, 2014 7:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 5:48 AM
Points: 5, Visits: 105
name of the database is tlmain

Regards,
Deepak Bisht
Post #1553473
Posted Tuesday, March 25, 2014 12:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 5:48 AM
Points: 5, Visits: 105
Got a similar issue reported in one of the forums.

http://www.sqlservercentral.com/Forums/Topic1381740-1292-1.aspx
Post #1554292
Posted Wednesday, March 26, 2014 9:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 3, 2014 7:16 AM
Points: 33, Visits: 47
Try detach and attach option
Post #1555031
Posted Wednesday, March 26, 2014 9:15 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:00 PM
Points: 21,212, Visits: 14,910
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1555036
Posted Friday, March 28, 2014 9:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:50 AM
Points: 6,175, Visits: 13,319
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"
Post #1555994
Posted Monday, March 31, 2014 12:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 5:48 AM
Points: 5, Visits: 105
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
Post #1556341
Posted Wednesday, April 9, 2014 1:36 AM This worked for the OP Answer marked as solution
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 5:48 AM
Points: 5, Visits: 105
Finally I was able to fix this issue...

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


Post #1559811
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse