Backup/Restore from MSDE 1.0 to MSDE 2000

To anyone who can assist:
Everything I have read online today states that I should be able to take a
backup copy of my database in MSDE 1.0 and restore it to MSDE 2000. However,
I am experiencing problems with this. We are wanting to upgrade our customer
sites to MSDE 2000 (from MSDE 1.0), and we have as part of our product a
utility which they can backup and restore their databases.
The Backup utility calls this statement:
BACKUP DATABASE dbName TO DISK = fileName WITH BLOCKSIZE=2048
The Restore utility calls this statement:
RESTORE DATABASE dbName FROM DISK = fileName WITH REPLACE
I am able to uninstall MSDE 1.0 from our server, install MSDE 2000, copy our
DB (mdf & ldf files) from the original location to the Data directory for
MSDE 2000, and attach to the database. Everything works great until I
attempt to restore a copy of the database that was generated in MSDE 1.0.
Our Restore utility stops our Database server service, runs the RESTORE
command, then attempts to restart our DB service. This fails, and when I
attempt to look at the database in DbaMgr2K (a very nice tool, BTW), I
receive an error message stating that I need to upgrade this database.
Is there something I am overlooking? The only thing seems to be different
between the two installations is that we had to change our ODBC
configuration. Using MSDE 1.0, we could connect to the "SQL Server"
connection. Using MSDE 2000, we had to change this to our local server.
Thanks in advance for your help!
Becky
hi Becky,
BElders wrote:
> ...
> I am able to uninstall MSDE 1.0 from our server, install MSDE 2000,
> copy our DB (mdf & ldf files) from the original location to the Data
> directory for MSDE 2000, and attach to the database. Everything
> works great until I attempt to restore a copy of the database that
> was generated in MSDE 1.0. Our Restore utility stops our Database
> server service, runs the RESTORE command, then attempts to restart
> our DB service. This fails, and when I attempt to look at the
> database in DbaMgr2K (a very nice tool, BTW), I receive an error
> message stating that I need to upgrade this database.
it's not clear to me why are you stopping the service... if you stop it, how
do you perform the RESTORE operation?
the service of the restoring server must be running to do that , but
probably I misunderstood your words.. or this is can be the cause of the
restore operation failing...
restoring the MSDE 1.0 database on a MSDE 2000 instance will upgrade it to
2000 version, including the te database compatibility level, and DbaMgr2k
and DbaMgr are engine version specefic and not database version (thank you
for your interest in these prjs of mine), so you have to use DbaMgr2k for
MSDE 2000 instances, and DbaMgr for MSDE 1.0 instances..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Just to clear up the confusion...I am not stopping the SQL server service
when using our Restore utility. The service I mentioned is part of our
product, and this is really performed as a precaution. Also, the Restore
command appears to execute OK. The error occurs when our service is
attempting to restart.
That being said, I decided to play around with the Backup/Restore operations
within DbaMgr2k (I am pretty new at using the tool). I received an error
message when trying to run the Restore using the old copy because it could
not locate the mssql7\data directory (removed - by me - as part of the
uninstall of MSDE 1.0). I added this directory back, and the RESTORE
functioned correctly. One thing that I did not realize was that the MDF/LDF
files are relocated in this process. I attempted to mimic this using our
Restore utility, but the same error occurred.
My conclusion is that it looks as though the RESTORE statement in our
utility is not "robust" enough. So I am going to either have to modify our
utility, or just provide instructions to our clients of the steps needed to
perform the same operations using DbaMgr2K (I think I like the latter
solution .
Thanks for your help!!
Becky