How do I move a database to a standalone MSDE wks

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

How do I move a database to a standalone MSDE wks

Author

Topic

diode
Starting Member

2 Posts

Posted - 2004-01-26 : 14:05:41

I have a different request. We have a database on a SQL Server 7 which we want to move to someone's desktop. The authors of the database are confident with the database and want just a single user read-only version. We were thinking of using MSDE 2000.

When I try to use DTS to move the data over, I get the following error:

The license for the installation of SQL Server on your source and destination connections does not permit the use of DTS to transform data. See your license adreement for more information.

Is there something I am overlooking? Is there a script or a whitepaper which will give me the steps in transfering the database to MSDE? Is this even possible? It would have to transfer all the users and roles over.

Any help would be greatly appreciated.

Eric

We have volumn licensing with Microsoft, so I don't understand why we can't do this database move.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-26 : 14:08:41

Move your database using BACKUP/RESTORE or sp_detach_db/sp_attach_db. Do not use DTS.

Tara

diode
Starting Member

2 Posts

Posted - 2004-01-26 : 14:15:57

Could there be any compatibility issues with going into MSDE? I don't think there are any stored procedures. Could you point me to instructions for backup/restore and sp_detatch_db and sp_attach_db?

I'm relatively new to SQL Server, as I have been focusing on mainly Oracle.
Thanks,
Eric

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-26 : 14:18:02

No compatibility issues with moving a database. SQL Server Books Online is the manual for SQL Server.

But here is a sample backup and restore command:

BACKUP DATABASE DBName
TO DISK = 'F:\MSSQL\Backup\DBName.BAK'
WITH INIT

Then copy the file to the MSDE server:

RESTORE DATABASE DBName
FROM DISK = 'F:\MSSQL\Backup\DBName.BAK'
WITH REPLACE

If the MDF and LDF files will be located in a different path than the original box, then you'll need WITH MOVE command on the MSDE server. BOL has the details on this.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources