Moving database from MSDE to SQL Server 1

[lennyh]

Assuming I have a db running on my local machine with MSDE, can you tell me how I move it to a remote SQL Server (7.0). For example, can I merely ftp the mdf and ldf files from MSDE to some directory at SQL Server, and if so, which directory. Or is there some specific initialization, etc. required at SQL Server to enable it to access the new db.

Please excuse me for this, perhaps, simple-minded question, but I'm just getting started with MSSQL.

TIA.

 

[tlbroadbent]

You can simply stop MSDE and copy or FTP the files to the new location. run sp_attach_db to attach the files in the new server. Bear in mind that logins and users will probably be broken. See the following article at SWYNK for information about fixing them.

Fixing broken logins and transferring passwords

http://www.swynk.com/friends/boyle/fixingbrokenlogins.asp

You could also backup the file in MSDE and restore in SQL Server but the same broken login scenario applies. You could use DTS to transfer the databases if connectivity between the PC with MSDE and the SQL Server exists. Using DTS may not create the same broken login problem. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[lennyh]

Terry, thank you for your helpful response.

I have a somewhat related follow-up: I'm wondering about the relative merits of two possible approaches for creating/testing a db on a local Access 2k system prior to moving it to a remote MSSQL. One approach is as already suggested -- building an Access Project and MSDE. I guess another is building a standard Access db (mdb file) and then using the Upsizing Wizard to move/convert it to MSSQL. Do you have any recommendations concerning one approach versus the other?

(The db is tables only -- no forms/reports/etc. The tables will ultimately be accessed in a browser-based app via php or equivalent.)

Also, recognizing that I do not have MSSQL installed on my local machine, is it the case that none of the administrative activity associated with the server can be done from the local machine?

Thanks for any additional info.

Lenny Harris

 

[tlbroadbent]

I prefer to develop databses in MSDE. However, I also have all of the SQL Admin toools available so that makes it easy. I'm uncertain which I would prefer if that was not the case. I think it would depend on how easily I could maintain the SQL databases. I have almost no experience with Access projects. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.