March 21, 2014 at 6:14 am
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
March 21, 2014 at 6:53 am
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."
March 21, 2014 at 7:13 am
name of the database is tlmain
Regards,
Deepak Bisht
March 25, 2014 at 12:51 am
Got a similar issue reported in one of the forums.
http://www.sqlservercentral.com/Forums/Topic1381740-1292-1.aspx
March 26, 2014 at 9:13 am
Try detach and attach option
March 26, 2014 at 9:15 am
deepak.bisht (3/21/2014)
name of the database is tlmainRegards,
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[/url]
Learn Extended Events
March 28, 2014 at 9:14 am
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" 😉
March 31, 2014 at 12:40 am
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
April 9, 2014 at 1:36 am
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply