How do I upgrade from MSDE 1.0 to either MSDE 2000 or SQL Server 2005 Express Edition?

I would like to upgrade an older MSDE 1.0 installation on Windows 2000 Server to a MSDE 2000 installation on Windows 2003 Server. I am also willing to use SQL Server 2005 Express Edition if it's easier.

I looked at the following white paper: http://support.microsoft.com/?kbid=322620, but am not sure if it applies to the the desktop versions.

I have tried doing a backup from MSDE 1.0 and restore to MSDE 2000, but got the following error during the restore:

C:\Program Files\Microsoft SQL Server\MSSQL>osql -E
1> RESTORE DATABASE master FROM DISK = "C:\Program Files\Microsoft SQL Server\MS
SQL\Data\backup\master.bak"
2> go
Msg 3168, Level 16, State 1, Server SERVER2, Line 1
The backup of the system database on device C:\Program Files\Microsoft SQL
Server\MSSQL\Data\backup\master.bak cannot be restored because it was created
by a different version of the server (117441135) than this server (134218488).
Msg 3013, Level 16, State 1, Server SERVER2, Line 1
RESTORE DATABASE is terminating abnormally.

TIA
abansal97Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you cannot restore the master database like this, as it is a system database.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_4g4w.asp

similary, the model & msdb database (msdb does not apply to msde installation though):
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_2w1f.asp
0
 
NightmanCTOCommented:
You can't restore the master database across versions - only user databases
0
 
abansal97Author Commented:
OK, that gave me the clue to proceed. Since the timestamp is equal, I'll split the points.

I additionally had to do the following.

I looked for the user databases, by typing the following:
1> SELECT name FROM master.dbo.sysdatabases WHERE DBID > 4
2> GO

After finding the databases, I then backed them up in MSDE 1.0.

When restoring the databases, I had to do the following, since the destination files had a different path than the original:
1> RESTORE FILELISTONLY FROM DISK = path_to_backup_set
2> GO
then
1> RESTORE DATABASE database_name FROM DISK = path_to_backup_set WITH MOVE logfile TO path_to_new_logfile, MOVE datafile TO path_to_new_datafile
2> GO

HTH someone.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.