May 16, 2007 at 5:44 pm
I have a backup of a SQL 2000 Master database that I need to restore onto a 2005 Master database.
-I have set SQL 2005 to run in sigle-user mode. I then goto a command prompt and type out the restore commands......The restore fails.
---Is this because it is not a 2005 master database and a 2000 master database needs to be converted?
- I then try to restore the 2000 master database backup to a new TEST database I created in 2005 and i get to 100% restored and then I get an error: Restore Failed: There is already an object named 'sysnsobjs' in the database
---- I was doing this because my plan was to restore the 2000 master to a regular 2005 db and then convert it to 2005 compatibility level in the options and then make a backup of this new db and then restore it to the 2005 master db.
--- Do I get this error because I cannot restore a 2000 master DB to a 2005 regular DB?
Thanks,
Donald
May 18, 2007 at 3:35 am
If you take a SQL 2000 master database and somehow make it a SQL 2005 master database you are heading for trouble. There are significant changes in the data structures in master between SQL 2000 and SQL 2005, so probably not all of the features of SQL 2005 will work properly. Also, you will not get any support from Microsoft if you hit problems.
You should go back a step and ask what information in the SQL 2000 master do you need in SQL 2005.
If it is logins, then script them out of SQL 2000 and into SQL 2005. If it is database attachments then work out a script to attach these to SQL 2005. If you have added many user stored procedures, then script them from SQL 2000 and add them to SQL 2005. If you have changed any ssytem stored procedures then you must re-engineer these changes for SQL 2005.
Do not try to force a SQL 2000 master or msdb database into SQL 2005 if you want SQL 2005 to work correctly.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
May 18, 2007 at 5:27 am
Yes Please follow the steps given by EdVassie. By restoring the SQL Server 2000 Master DB you are inviting the problem and this may also lead you to rebuild the Server.
Minaz Amin
"More Green More Oxygen !! Plant a tree today"
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply