(I am not a DBA, I play one at work because I’ve been thrown into the position since I do all the .net programming, why shouldn’t I take care of the SQL server and web server also.. So this is what worked for me and probably are no where close to best practices)
My task: Move a database from SQL2000 to SQL2008 R2
System.Data.SqlClient.SqlError: There is already an object named ‘sysnsobjs’ in the database. (Microsoft.SqlServer.Smo)
Why I got this error:
I knew going into this that we’d have a problem. When I started at my company the SQL DB was already created for our website. Who ever it was, they just started throwing data into the master db and left it at that. Fast forward 10 years, I had made a copy of the db and made it, it’s own db but still I knew the copy of system files were still in there and when time came to do a update we were going to have problems. So it was no suprise when I received the error that a system file already existed.
What doesn’t work:
Looking online I’ve seen many people ask about this error and after about 2-3 pages of try this and try that, most just either give up or create some script to pull the data and create tables on a one off basis. So we know that a restore from backup doesn’t work, it gives our error of There is already an object named ‘sysnsobjs’ in the database.
So what about import data from one server to the other?
I created a new Database, named it the same as my sql2000 db and did an import from one server (2000) to the new server(2008 r2). At first glance all looked good, my tables were there and data and my stored procedures were all there but then when I checked on views, there was nothing. All the views were imported as tables (someone explained that it’s because that’s really what they are), so importing failed.
So what worked for me: A Walk through:
Importing from one db to the other but still on the SQL2000 server mostly worked.
1. Create your new db on the current (old) server
2. Right click new db and choose import
3. Choose your source and destination db’s
4. Instead of choosing “Copy tables and views from source database” choose “Copy objects and data between SQL Server databases”
5. On the select Ojects to Copy screen
uncheck Copy all objects and hit the button Select objects (choose everything, check everything)
uncheck use default objects, click Options button. Check everything except Generate Scripts in Unicode
6. Go have lunch this will take awhile
Mine actually failed (but not because of the system files) mine failed becuse of 2 views but everything else was copied over correctly.
7. Now make a backup of this new database
8. Copy bak file to new database server
9. Restore DB on new server
Make sure to check your permissions but all should be there and ready to use. Since you only need to make sure this DB is the same as your last (If you have both SQL servers still running) us something like the demo of http://www.sqlaccessories.com/SQL_Examiner_Suite/ to compare the table structure and it will also compare all of your stored procedures.