February 4, 2007 at 7:18 am
Hi!
Due to an error, I had a database which originally was created in the master database.
I have created a backup of the database and restored it under a different name. For MS SQL 2000 this works fine, but if I try to restore the database in MS SQL 2005 I get the following error:
* System.Data.SqlClient.SqlError: There is already an object named 'sysnsobjs' in the database
Any ideas?
I cannot find this object in my database, thus I cannot delete it.
Thanks in advance!
- Thom
February 4, 2007 at 4:22 pm
I didn't quite understand what you are trying to says..."Due to an error, I had a database which originally was created in the master database."...
sys.sysnsobjs | Exists in every database. Contains a row for each namespace-scoped entity. This table is used for storing XML collection entities. |
You can't delete this table because it is system object...
Run the following to chech the object exists or not..
SELECT
* FROM SYSOBJECTS WHERE NAME = 'sysnsobjs'
MohammedU
Microsoft SQL Server MVP
February 5, 2007 at 1:06 am
Thomas,
I'm a bit confused. What do you mean with "a database which originally was created in the master database." You cannot create a database inside another database.
How do you restore? Do you use SSMS or some third party application?
Markus
[font="Verdana"]Markus Bohse[/font]
February 5, 2007 at 9:51 am
The database (all the tables etc) are created within Master db. An installer program did this (used the wrong user).
I have created a backup of master db and restored it under a different name. This works fine for MS SQL 2000, but SQL 2005 throws the mentioned error.
I am using SQL Server Management Studio, normal restore function when I try to restore.
April 16, 2010 at 4:40 pm
Guys I'm having the same problem. I'm trying to restore a master database as a different database name to a SQL 2008 instance. The master database comes from a SQL 2000 instance and I'm getting the same error message about the sysnsobjs already exists. Its a normal SQL backup and I'm using SQL MAnagement Studio to do the restore(no third party tool)
Anybody have any ideas or hints on how to do this restore?
May 17, 2010 at 4:44 pm
I am seeing this trying to restore a sql2000 master databsae to a sql 2005 database with another name for the master database. the old version doesn't restore on the 2005. any ideas on how to deal with this?
Thanks
Bill
June 29, 2010 at 7:36 am
I'm experiencing the same problem on SQL 2008. When I move to a new server, I always backup master and msdb on the old server and restore it onto the new server under a different name to have handy for a few weeks should something go wrong with the move. This is the first time I've moved a 2000 database server to 2008, and I'm getting this error. I'm thinking about restoring the backup to the 2000 server as a user database, then deleting that object, backing it up, and trying to restore it again in the 2008 environment.
December 14, 2010 at 9:19 am
Same here with 2000 master.bak file to 2005 with a different name. Anyone solve this yet?
December 14, 2010 at 9:47 am
I am not 100% certain this will work as the master db contains all user information, but if you use command line restore with single user tag, you should be able to restore the database.
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
December 14, 2010 at 10:49 pm
Not sure why you are moving Master but just in case I'm confused in your reasonings
read
http://support.microsoft.com/kb/264474
and this
http://www.julian-kuiters.id.au/article.php/sql2000-restore-master-database
CodeOn
😛
December 18, 2010 at 2:19 pm
Not trying to restore it to the master on the new server, just trying to create a copy. An application I am trying to restore actually created som files in the master and I need to access them.
This shouldnt be a problem, starting to think it might be a bug in the new sp4 I installed.
What is happening is that whenever a new db is created on the 2005 there is an entry in sysobjects for something called sysnsobjs. Then after the actual files have been restored the RESTORE process automatically tries to update it to the latest version (From 539 to 611). This fails with the message "sysnsobjs already exists in database" and the database is inaccessible.
I tried running with CONTINUE_AFTER_ERROR and no good either. Didn't really seem to continue either just quit at the same point with a slightly different message.
Doesn't seem to be an option on the restore to tell it to leave it an the prior version....
Tried to delete the reference sysnsobjs before the restore but in its ultimate wisdom MS has decided to make it impossible to "run ad hoc queries" against system tables.
Protecting me from myself even though i am on a test server that I could rebuil if necessary while making it impossible to restore a 2000 db to a 2005.
December 20, 2010 at 9:47 am
If it is a test server why not install another SQL instance, start it with the appropriate start up params to be bale to restore the master DB and restore the backup you have and then script out\ export whatever objects you require?
Andrew
December 20, 2010 at 11:24 am
That might work. I would have tried it I dont have 2000 to install and I assumed if 2005 wouldn't let me restore to a 2000 master to a user database it surely wouldnt let me do it to a system.
Right now I'm im installing the 2000 msde on another machine (a whole nother bunch of problems just getting that to run) but I dont have a lot of confidence in that route. I try your way even if this does work and post results.
I've seen posts all over about this same and no solutions that work. Maybe we can finally put an end to this.
December 21, 2010 at 1:30 pm
Restoring the 2000 master db to a user db on 2000 msde worked. Go figure.
From traces and log files I believe this is an MS bug. What happens is that even though you are restoring to a user db SQL sees the origional db name in the .bak file and treats it like a restore to a system db so it tries to update it to the latest version.
This is not done for other user dbs. If they looked at the name you are restoring to rather than the name in the .bak file this would not be a problem.
Add another 20 hrs to the large amount of time I've spent due to MS bugs. To be fair though for what I have paid them (even though a large amount) overall they have obviously saved me more than they cost me.
But they could do better.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply